Perl DBI and Informix BYTE fields
Has anyone worked with Informix (IDS 7.31.4, if that makes any
difference) BYTE fields (which can store binary data) and Perl DBI?
I've set up a test table, in which I'm trying to store PDFs. It seems
to load into the database okay, but when I pull it out of the
database, I get something that appears to be a pdf (i.e., it begins
with the magic number %PDF-1.3), but it (a) is larger than the pdf
that was uploaded, and (b) cannot be read by Acrobat Reader, which
complains that "The file is damaged and could not be repaired."
I defined the table as follows:
create table pdfs (
id serial primary key,
document byte
);
Here's the code I used to insert the data.
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
$ENV{INFORMIXDIR} = '/opt/informix';
$ENV{INFORMIXSERVER} = 'tnwd_ecf';
my $dbh = DBI->connect('DBI:Informix:test', 'username', 'password',
{ PrintError => 0, RaiseError => 1 });
my $sql = 'insert into pdfs (document) values (?)';
my $sth = $dbh->prepare($sql);
open(DOCUMENT, "<test.pdf") or die $!;
undef $/; # slurp mode
my $document = <DOCUMENT>;
close DOCUMENT or die $!;
$sth->execute($document);
$dbh->disconnect;
__END__
To retrieve the document, I used the following code:
#!/usr/bin/perl
use strict;
use warnings;
use DBI;
$ENV{INFORMIXDIR} = '/opt/informix';
$ENV{INFORMIXSERVER} = 'tnwd_ecf';
my $id = 1;
my $dbh = DBI->connect('DBI:Informix:test', 'username', 'password',
{ PrintError => 0, RaiseError => 1 });
my $sql = q{select document from pdfs where id = ?};
my $sth = $dbh->prepare($sql);
$sth->execute($id);
my ($document) = $sth->fetchrow;
open(DOCUMENT, ">out.pdf") or die $!;
print DOCUMENT $document;
close DOCUMENT or die $!;
$dbh->disconnect;
__END__
And here's the result, where test.pdf is the file that was uploaded,
and out.pdf is what I then downloaded from the database.
[brock@brandeis tmp]$ ls -l *.pdf
-rw-r--r-- 1 brock brock 7960 Oct 3 09:16 out.pdf
-rw-r--r-- 1 brock brock 6508 Oct 3 08:05 test.pdf
When I try this with text-only data, e.g. a copy of /etc/hosts instead
of a pdf, the file I download is identical to the file I upload. But
not with pdfs.
In case it makes any difference, I'm using the 2003.04 DBD::Informix
driver, although I got the same result with 1.04.PC1.
Any thoughts?
--
Brock
philarete@mindspring.com