On Sat, 18 Jan 2003 05:17:55 +1100, Laird Shaw wrote: Hi Laird
Great experiment! Thanx. Result: o The mysqlPP driver is corrupt, in - I suspect - more ways than one o Stop using it. o Report problem to maintainer via http://rt.cpan.org o Instead, use straight mysql version, eg via Randy Kobes' repository: http://theoryx5.uwinnipeg.ca/ppmpackages/ (if it matches your version of Perl, which I believe it does) o Tested code and output below o Warning: Watch line wrap -----><8----- #!/usr/bin/perl # # Name: # template.pl. # # Purpose: # Test MySQL & blobs etc. # # Note: # tab = 4 spaces || die. # # Author: # Ron Savage <[EMAIL PROTECTED]> # http://savage.net.au/index.html use strict; use warnings; use DBI; use Error qw/:try/; # ----------------------------------------------- sub test { my($driver, $dbh, $input_file_name, $column_type, $column_size) = @_; print "Start: ", scalar localtime(), ". \n"; print "Driver: $driver. \n"; # The evals protect against non-standard SQL # and against a non-existant table. eval{$dbh -> do('drop table if exists b_table')}; eval{$dbh -> do('drop table b_table')}; my($sql) = "create table b_table (b_value $column_type)"; print "SQL: $sql. \n"; $dbh -> do($sql); $sql = 'insert into b_table (b_value) values (?)'; my($sth) = $dbh -> prepare($sql); # Yes, we call die in the next statement, and the catch is triggered. # That is, we don't need to say: || throw Error::Simple(...). open(INX, $input_file_name) || die("Can't open($input_file_name): $!"); binmode INX; print "File name: $input_file_name. \n"; print "Blob file size: ", -s INX, " bytes. Expect: $column_size. \n"; my($blob); { local $/ = undef; $blob = <INX>; close INX; } print "Blob ram size: ", length($blob), " bytes. Expect: $column_size. \n"; $sth -> execute($blob); $sth -> finish(); $sql = 'select b_value from b_table'; $sth = $dbh -> prepare($sql); $sth -> execute(); $blob = $sth -> fetch(); $blob = $$blob[0]; $sth -> finish(); print "Blob db size: ", length($blob), " bytes. Expect: $column_size. \n"; print "End: ", scalar localtime(), ". \n"; print "\n"; } # End of test. # ----------------------------------------------- try { my(%data) = ( '/temp/configure-apache.zip' => 16249, '/temp/cookbook-code.zip' => 134552, ); for my $driver ('mysql', 'mysqlPP') { my($dbh) = DBI -> connect ( "DBI:$driver:test:127.0.0.1", 'root', 'toor', { AutoCommit => 1, HandleError => sub {Error::Simple -> record($_[0]); 0}, LongReadLen => 150_000, LongTruncOk => 0, PrintError => 0, RaiseError => 1, ShowErrorStatement => 1, } ); for my $input_file_name (keys %data) { for my $column_type ('blob', 'longblob', 'text') { test($driver, $dbh, $input_file_name, $column_type, $data{$input_file_name}); } } } } catch Error::Simple with { my($error) = 'Error::Simple: ' . $_[0] -> text(); chomp($error); print $error; }; -----><8----- Output: -----><8----- Start: Sat Jan 18 00:48:19 2003. Driver: mysql. SQL: create table b_table (b_value blob). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16249 bytes. Expect: 16249. End: Sat Jan 18 00:48:19 2003. Start: Sat Jan 18 00:48:19 2003. Driver: mysql. SQL: create table b_table (b_value longblob). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16249 bytes. Expect: 16249. End: Sat Jan 18 00:48:19 2003. Start: Sat Jan 18 00:48:19 2003. Driver: mysql. SQL: create table b_table (b_value text). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16249 bytes. Expect: 16249. End: Sat Jan 18 00:48:19 2003. Start: Sat Jan 18 00:48:19 2003. Driver: mysql. SQL: create table b_table (b_value blob). File name: /temp/cookbook-code.zip. Blob file size: 134552 bytes. Expect: 134552. Blob ram size: 134552 bytes. Expect: 134552. Blob db size: 65535 bytes. Expect: 134552. End: Sat Jan 18 00:48:19 2003. Start: Sat Jan 18 00:48:19 2003. Driver: mysql. SQL: create table b_table (b_value longblob). File name: /temp/cookbook-code.zip. Blob file size: 134552 bytes. Expect: 134552. Blob ram size: 134552 bytes. Expect: 134552. Blob db size: 134552 bytes. Expect: 134552. End: Sat Jan 18 00:48:20 2003. Start: Sat Jan 18 00:48:20 2003. Driver: mysql. SQL: create table b_table (b_value text). File name: /temp/cookbook-code.zip. Blob file size: 134552 bytes. Expect: 134552. Blob ram size: 134552 bytes. Expect: 134552. Blob db size: 65535 bytes. Expect: 134552. End: Sat Jan 18 00:48:20 2003. Start: Sat Jan 18 00:48:20 2003. Driver: mysqlPP. SQL: create table b_table (b_value blob). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16197 bytes. Expect: 16249. End: Sat Jan 18 00:48:20 2003. Start: Sat Jan 18 00:48:20 2003. Driver: mysqlPP. SQL: create table b_table (b_value longblob). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16197 bytes. Expect: 16249. End: Sat Jan 18 00:48:20 2003. Start: Sat Jan 18 00:48:20 2003. Driver: mysqlPP. SQL: create table b_table (b_value text). File name: /temp/configure-apache.zip. Blob file size: 16249 bytes. Expect: 16249. Blob ram size: 16249 bytes. Expect: 16249. Blob db size: 16197 bytes. Expect: 16249. End: Sat Jan 18 00:48:20 2003. Start: Sat Jan 18 00:48:20 2003. Driver: mysqlPP. SQL: create table b_table (b_value blob). File name: /temp/cookbook-code.zip. Blob file size: 134552 bytes. Expect: 134552. Blob ram size: 134552 bytes. Expect: 134552. Error::Simple: DBD::mysqlPP::st execute failed: You have an error in your SQL syntax near '���?���̸ `�����`���3憀���xf�E!��}�5��C���&G�;t�X�~W(� p G:e~O!� �' at line 1 [for statement ``insert into b_table (b_value) values (?)'']) -----><8----- Notes: o Like hell I have an error... :-) o Under some conditions, DBI reports the wrong SQL, so don't take the above error SQL literally. I posted a demo of _this_ problem on 7th Jan (to this list) -- Cheers Ron Savage, [EMAIL PROTECTED] on 18/01/2003 http://savage.net.au/index.html
