Hi,
I am running into a problem whilst testing the use of DBI and DBD::mysqlPP
to store and retrieve binary files to/from a mysql database.
My test is to insert a file into a blob column and then retrieve it via
separate scripts for insertion / retrieval. The test works with one file (a
32k text file) - using the dos command 'fc' verifies that the retrieved and
original file are identical (although a curious issue for me is that the
file sizes differ by a few bytes) - but with the second file (a 48k binary
file) the retrieved file is only 7k - no need to use 'fc' on that one to
know they're different...
I am confident that the insert is working correctly: the reason being that I
have an empty table to start with (the file testtable.MYD is 0kb), then
after my insert this file is approx 48k (the size of the file I inserted).
When I use 'fc' to compare this database file with the original file, there
are very few differences.
Anyone got any suggestions?
I am a beginner to Perl but experienced in Delphi and as well as moderately
skilled in C, VB, so any hints on Perl programming style, safety and best
practice would be much appreciated.
Setup:
OS: Windows 95
mysql version 3.23.54 (running on the same machine as the scripts)
Perl v5.8.0 from ActiveState
DBI version 1.30
DBD::mysqlPP version 0.03.
The relevant metadata for the table is:
create table testtable (PK1 int not null primary key, blobcol blob);
The documentation for mysql says that blob columns can store up to 65k, so
this 48k file shouldn't be a problem.
The scripts are:
---------TestInsertBlob.pl---------
use DBI;
use DBD::mysqlPP;
use strict;
my $dbh = DBI->connect('dbi:mysqlPP:database=test;host=127.0.0.1',
'myusername', 'mypassword', {RaiseError => 1, AutoCommit => 1});
my $infilestr;
open(INFILE, 'c:\origbinaryfile.bin') ;
while (<INFILE>) {
$infilestr = $infilestr.$_;
}
#The file created below has size reported by 'dir' of 49190
#(different from original file's size of 49152,
#however: 'fc c:\origbinaryfile.bin copiedbinaryfile.bin' reports:
#'no differences encountered')
open(OUTFILE, '>copiedbinaryfile.bin');
print OUTFILE $infilestr;
close(OUTFILE);
#This prints 49152 (the exact size of the file as reported by 'dir')
print length($infilestr);
$dbh->do('delete from testtable where PK1=3');
my $sth = $dbh->prepare('insert into testtable (PK1, blobcol) values (?,
?)');
$sth->execute(3, $infilestr);
$dbh->disconnect;
---------END TestInsertBlob.pl---------
---------TestSelectBlob.pl---------
use DBI;
use DBD::mysqlPP;
use strict;
my $dbh = DBI->connect('dbi:mysqlPP:database=test;host=127.0.0.1',
'myusername', 'mypassword', {RaiseError => 1, AutoCommit => 1});
my $blob;
#this is longer than the expected file size (48000)
$dbh->{LongReadLen} = 100000;
my $sth = $dbh->prepare('select blobcol from testtable where PK1 = 3');
$sth->execute();
$sth->bind_columns(\$blob);
$sth->fetch;
#this prints 7251
print length($blob);
open(OUTFILE, '>retrievedbinaryfile.bin') ;
print OUTFILE $blob;
close(OUTFILE);
$dbh->disconnect;
---------END TestSelectBlob.pl---------
_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*
http://join.msn.com/?page=features/virus
