Putting Blobs in the Database via DBD::Informix

=o= Does anyone have a working example of what I'm looking for in that subject line? =o= Even better, does anyone know how to make it work with a stored procedure to pass it as a "REFERENCES BYTE" datatype? =o= Everywhere I turn, people mention various bugs: it must be in memory, not in a file; it must be done as an insert, not an update; etc. etc. etc. =o= What actually works? <_Jym_>

> =o= Does anyone have a working example of what I'm looking for > in that subject line? =o= If anyone cares, I figured it out. It's something along these lines: my $query = "insert into whatever(foo, bar, some_blob)" . " values($foo, $bar, ?);"; my @params = ($blob); $dbh->do($query, undef, @params); The blob has to be bound to a variable separately, though the other things ($foo and $bar) needn't be, so long as they're relatively normal datatypes. =o= Only an "INSERT ... VALUES(...)" query will work, because that's the only way to get Informix to communicated the datatype to DBD::Informix. Arrgh!!! =o= Also, DBD::Informix doesn't implement a bind_param() method so that one could specify the datatype. <_Jym_>

In article <Jym.ybn3dwhupa8.fsf@igc.org>, Jym Dyer <jym@igc.org> wrote: >=o= Does anyone have a working example of what I'm looking for >in that subject line? I have no problems with insertions, only selects. I can insert BLOB columns, no problem, using FILETOBLOB e.g. insert into my_table (blobcol) values (FILETOBLOB('blobfile', 'client')); however, if I do this: select LOTOFILE(blobcol, 'file', 'client') from image my_table; I get this: DBD::Informix::dbd_ix_st_fetch - Unknown type code: 43 (no IUS support) Database handle destroyed without explicit disconnect. I have come up with a workaround for this - I embed the select statement above into an insert statement: insert into my_table2 (filename) select LOTOFILE(blobcol, 'file', 'client') from image my_table; Then I can just select the filename from my_table2 and read the blob data from there. The problem with this is that my_table2 can get locked if there are lots of transactions reading in image data. Is there a better way of doing this? > >=o= Even better, does anyone know how to make it work with a >stored procedure to pass it as a "REFERENCES BYTE" datatype? > >=o= Everywhere I turn, people mention various bugs: it must >be in memory, not in a file; it must be done as an insert, not >an update; etc. etc. etc. > >=o= What actually works? > <_Jym_>

Jym Dyer wrote: > =o= Does anyone have a working example of what I'm looking for > in that subject line? Do you have the source for DBD::Informix? If so, you have examples -- look at the test suite in the t directory. Also look at the documentation. > =o= Even better, does anyone know how to make it work with a > stored procedure to pass it as a "REFERENCES BYTE" datatype? At the moment, that cannot be done. You'd have to know that the procedure took a REFERENCES BYTE parameter, and DBD::Informix would have to accept a type attribute when binding parameters. > =o= Everywhere I turn, people mention various bugs: it must > be in memory, not in a file; it must be done as an insert, not > an update; etc. etc. etc. There are irritating limitations in the Informix product -- see the Notes/updating.blobs diatribe in the 0.62 DBD::Informix, released yesterday. > =o= What actually works? For inserts, INSERT INTO SomeTable VALUES(?,?,?,?); See for example, the blob tests. Why does everybody overlook the tests? And why do people insist on asking questions in c.d.i when the documentation explicitly says where to send questions. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.62 -- see http://www.perl.com/CPAN #include <disclaimer.h>