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.

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]
      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.

Back to Seekers of Perl Wisdom