Topic: Postgres large object interface
author: Mattia Rossi
created: 11-02-2002 06:43:02 AM
|
Hi guys,
in my struggle to make my application portable to both Oracle and postgres I have added two procedures to the adodb-postgres driver:
- UpdateBlobFile
- BlobDecode
These two methods allow me to use the same code (almost) with both the oracle and the postgres driver.
I am using Large objects in postgres (instead of the more appropriate bytea type) because I want to store binary data in the db and i had a lot of problems using the byte escaping functions provided with the postgres driver (BTW, postgres documentation regarding blobs and binary fields really sucks).
The postgres functions are similar to the ingres functions; when I have to store a blob I call the UpdateBlobFile and when I have to get the blob content I call BlobDecode (as the select only returns the object identifier).
I hope it can be of some help for someone.
This is the code I added to the ADODB_postgres class:
/*
* Load a Large Object from a file
* - the procedure stores the object id in the table and imports the object using
* postgres proprietary blob handling routines
*/
function UpdateBlobFile($table,$column,$val,$where,$blobtype='BLOB')
{
pg_exec ($this->_connectionID, "begin");
$oid = pg_lo_import ($val);
pg_exec ($this->_connectionID, "commit");
$rs = ADOConnection::UpdateBlob($table,$column,$oid,$where,$blobtype);
$rez = !empty($rs);
return $rez;
}
/*
* Reads the real blob from the db using the oid supplied as a parameter
*/
function BlobDecode( $blob )
{
@pg_exec("begin");
$fd = @pg_lo_open($blob,"r");
$realblob = @pg_loreadall($fd);
@pg_loclose($fd);
@pg_exec("commit");
return( $realblob );
}
and here is an example of how I'm using it:
/*
Example of how to write a binary file into a postgres db using adodb and large objects
The script inserts a file called test.zip into the table test, the table must exist !!!!
create table test(filename varchar(20),bin_file oid, mime_type varchar(50));
*/
include('adodb/adodb.inc.php'); # load code common to ADODB
$conn = &ADONewConnection('postgres7'); # create a connection
$conn->PConnect('','user','pass','db'); # connect to the db
$file_path = './test.zip';
$mime_type = 'application/zip';
if (!$conn)
die('Couldn\'t connect to the db');
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
$conn->debug=true;
$str = "INSERT INTO test (filename,bin_file,mime_type)
VALUES (basename($file_path), NULL, $mime_type)";
$stmt_write = $conn->Execute($str);
$stmt_write->close;
$conn->UpdateBlobFile('test',
'bin_file',
$file_path,
"filename='".basename($file_path)."'",
'BLOB'
);
/*
End of the file update script
*/
The file can be retrieved with this other script:
/*
Example of how to read a binary file from a postgres db using adodb and large objects
The script retrieves a file called test.zip from the table test
create table test(filename varchar(20),bin_file oid, mime_type varchar(50));
*/
include('adodb/adodb.inc.php'); # load code common to ADODB
$conn = &ADONewConnection('postgres7'); # create a connection
$conn->PConnect('','user','pass','db'); # connect to the db
$ADODB_FETCH_MODE = ADODB_FETCH_ASSOC;
if(isSet($infile))
$filename = $infile;
else
$filename = 'test.zip';
$str="select filename,bin_file,mime_type,octet_length(bin_file) as imgsize from test where filename='".$filename."'";
$stmt_image = &$conn->Execute($str);
$size = $stmt_image->fields["imgsize"];
$content_type = $stmt_image->fields["mime_type"];
$file_name = $stmt_image->fields["file_name"];
if ($size != 0) {
header("Content-type: ".$content_type);
header("Content-Disposition: filename=".$file_name);
echo $conn->BlobDecode($stmt_image->fields["bin_file"]);
}
.. Mattia |
|
Topic: Re:Postgres large object interface
author: John Lim
created: 12-02-2002 03:25:05 AM
|
Thanks for the patch. Do you mind if I refer questions from other people on this issue to you? This is because I only use bytea and do not use the blob functions.
Regards, John |
|
Topic: Re:Postgres large object interface
author: Mattia Rossi
created: 12-02-2002 02:00:04 PM
|
Hi John,
feel free to redirect the questions to this email address, it's the minimum I can do to repay you for the time you saved me by coding adodb ...
Cheers
Mattia
PS: do you store binary files in bytea fields ? If yes, which function do you use to escape postgres special characters ? |
|
Topic: Re:Postgres large object interface
author: John Lim
created: 12-02-2002 10:22:14 PM
|
Thanks for volunteering to help. Much appreciated.
I normally use
$blob = fread(...);
$conn->qstr($blob);
$conn->Execute("INSERT INTO TABLE (id, blobdata) VALUES (1,$blob);
Just make sure that the magic_quotes stuff is turned off. That is possibly screwing up any quoting.
Regards, John |
|
Topic: Re:Postgres large object interface
author: Mattia Rossi
created: 14-02-2002 05:33:58 AM
|
Hi John,
I am already using this method for blobs containing text, but I cannot make it work (transparently) with binary files.
As far as I know the qstr function only escapes carets, but it doesn't handle the four special chars 0x0 0x10 0x39 and 0x92.
I found a function that escapes these four chars, so that I can successfully insert the escaped string in the db. The problem is that I have to unescape the data when I want it back and I couldn't find a transparent way to do it in AdoDb (my bad, I only started using it last week ....)
I am probably missing something important on how the adodb classes work. As soon as I find a clean solution to the problem I'll post it.
BTW, the function that escapes the binary data is (look here for a complete description of the problem:
http://groups.google.com/groups?q=sqlesc&hl=en&selm=027601c12c08%2454576a20%2448d210ac%40jecw2k1&rnum=2
)
function sqlesc($ct)
{
$buf = "";
for ($i = 0; $i < strlen($ct); $i++)
{
if (ord($ct[$i]) == 0)
$buf .= "\\\\000";
else if (ord($ct[$i]) == 10)
$buf .= "\\\\012";
else if (ord($ct[$i]) == 39)
$buf .= "\\\\047";
else if (ord($ct[$i]) == 92)
$buf .= "\\\\134";
else
$buf .= $ct[$i];
} return $buf;
}
I got to the point of writing the unescape function, and then thought that the oid solution was quicker .... In theory postgres 7.2 provides two functions to escape/unescape data to php (pg_escape_bytea, pg_escape_text), but they were added in postgres 7.2/php 4.2 (only on cvs) and they don't work (php segfaults when calling one of these two functions)
...
Please let me know if I'm missing something important (like: 'oh, but adodb handles transparently the binary escaping/unescaping'), otherwise I'll keep testing and trying new solutions ....
Mattia |
|
Topic: Re:Postgres large object interface
author: Bill Moran
created: 27-12-2002 09:43:55 AM
|
The PostgreSQL 7 driver can use the following two functions to manage bytea fields as blobs.
function BlobDecode($blob)
{
$blob = str_replace('"', '\"', $blob);
eval("\$temp = \"$blob\";");
return $temp;
}
function UpdateBlob($table, $column, $val, $where, $blobtype='BLOB')
{
$val = pg_escape_bytea($val);
return $this->Execute("UPDATE $table SET $column='$val' WHERE $where") != false;
}
We've been using this logic to store image data for a while now with no problems.
The pg_escape_bytea() function is apparently available as long as you have the PostgrSQL 7 client library on the machine running php.
Also, it would make sense (to me) if every driver had an UpdateBlob() and DecodeBlob() function. Even though most drivers don't require the DecodeBlob() function, it will make using the library easier and more consistent. The funciton would only need to be:
function DecodeBlob($blob)
{
return $blob;
}
when no decoding is needed. |
|
Topic: Re:Postgres large object interface
author: John Lim
created: 28-12-2002 12:29:49 PM
|
Hmm, seems like i am still learning more about postgresql everyday. Thx to you experts.
>>>>Also, it would make sense (to me) if every driver had an UpdateBlob() and DecodeBlob() function. Even though most drivers don't require the DecodeBlob() function, it will make using the library easier and more consistent. The funciton would only need to be
OK will add BlobDecode() and BlobEncode() in base ADOConnection class.
There are 2 problems with the function below:
function BlobDecode($blob)
{
$blob = str_replace('"', '\"', $blob);
eval("\$temp = \"$blob\";");
return $temp;
}
1. $blob = str_replace('$','\$',$blob) is required.
2. Will break backward compat, as the current version assumes OID is parameter. So as a compromise, we will change the API to:
function BlobDecode($blob,$isOID=true)
Set $isOID to false to pass in octal escaped bytea string. |
|
Topic: Re:Postgres large object interface
author: Bill Moran
created: 30-12-2002 10:28:53 AM
|
> 1. $blob = str_replace('$','\$',$blob) is required.
Oops ... that's quite a coincidence that we didn't find that one! Thanks!
> function BlobDecode($blob,$isOID=true)
>
> Set $isOID to false to pass in octal escaped bytea string.
In the interest of keeping the API consistent (across the boards) - could you test for PostgreSQL 6 within the function?
if ($ADODB_Database == 'postgres64') {
$isOID = true;
} else {
$isOID = false;
}
This automagically determines the proper way to use the function.
This will keep the calling process simple and should still work consistently. Just note in the docs that Postgres64 requires an OID, while other systems use the escaped data.
I've not enough experience with Postgres prior to 7 to know if this is viable or not. |
|
Topic: Re:Postgres large object interface
author: shiva
created: 22-12-2006 05:33:38 AM
|
|
|
|
