PostgreSQL and binary data

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Does anyone know if PostgreSQL is capable of storing binary data? I'm using DBI/DBD::ODBC and I need to save JPEG/GIF/PNG images in a table.
Here is what I tried:

use DBI;
my ($dbh, $data) = (undef, '');
$data .= chr(int rand 256) for 1..400;
eval{
 $dbh = DBI->connect('DBI:ODBC:testdb', '', '')
   or die $DBI::errstr;
 my $sth = $dbh->prepare('insert into btest(id, data) values (?,?);') 
+or die $dbh->errstr();
 $sth->execute(1, $data) or die $dbh->errstr();
 $dbh->disconnect();
};
print "Error : $@" if length $@;
[download]

Table BTEST is defined as follows

testdb=# \d btest
         Table "btest"
 Attribute |  Type   | Modifier
-----------+---------+----------
 id        | integer | not null
 data      | text    |
Index: btest_id_key
[download]

When I run the above code I get the following error:

DBD::ODBC::st execute failed: Error while executing the query;
ERROR:  Unterminated quoted string (SQL-S1000)
ERROR:  Unterminated quoted string (SQL-S1000)(DBD: st_execute/SQLExec
+ute err=-1
) at C:\Perl\dev\rdm\btest.pl line 12.
[download]

I tried doing two things

1) $data =~ s/'/''/g;
2) $data =~ s/(\W)/\\$1/g;

Neither one works.

Any input is appreciated.
Thanks.

Comment on PostgreSQL and binary data Select or Download Code

Replies are listed 'Best First'.
Re: PostgreSQL and binary data by grep (Monsignor) on Dec 25, 2001 at 00:44 UTC
Yes it can. I would avoid it like the plauge but, if you want to know. Define your row. `CREATE TABLE image { item_id INTEGER NOT NULL, picture OID, CONSTRAINT image_pk PRIMARY KEY(item_id), CONSTRAINT image_item_id_fk FOREIGN KEY(item_id) REFERENCES item(i +tem_id) };` [download] Then do your inserts, selects and deletes `INSERT INTO image VALUES (3, lo_import('/tmp/image_name.jpg')); SELECT lo_export(picture, '/tmp/image_name.jpg') FROM image WHERE item +_id = 3; to delete: SELECT lo_unlink(picture) FROM image WHERE item_id = 3;` [download] WARNING: this code is untested, but double checked FYI: why I suggest avoiding BLOBs. Not portable to other RDBMSs Filesystems are much better at handling files Slows your DB down during BLOB access, usally only slightly a general big pain in the A** grep grep> cd pub grep> more beer	[reply] [d/l] [select]
Re: Re: PostgreSQL and binary data by rob_au (Abbot) on Dec 25, 2001 at 13:40 UTC
I would have to strongly concur with grep's suggestion to avoid the storage of binary information, in this case, image files, in PostgreSQL. The amount of work involved in storing and extracting this data and the storage capacity it requires, far outweighs the indexing advantages offered by the database. By far a better solution is to storage all the image files in a known directory or directory structure and simply store a reference to this file in the database within a standard `VARCHAR` field. If the storing and indexing of the image files is an issue within itself, you may want to consider looking at Cache::Cache, in particular Cache::FileCache, and link the index of this cache to the database (if indeed you still require the database). `perl -e 's&&rob@cowsnet.com.au&&&split/[@.]/&&s&.com.&_&&&print'`	[reply] [d/l]
A reply falls below the community's threshold of quality. You may see it by logging in.


good chemistry is complicated, and a little bit messy -LW
	PerlMonks