| Subject: | binding output columns as SQL_BLOB returns nothing |
Here is the mail I posted to sqlite mailing list. It shows a problem as
the code stands in that binding a column as an SQL_BLOB returns nothing.
The test code expects to have an in.png.
Further to the above, I've been experimenting and I also looked at
https://rt.cpan.org/Ticket/Display.html?id=19471 which suggested that
either binding on input as a SQL_BLOB or binding on output as an
SQL_BLOB would not set the utd8 flag. I confirm the former but when
binding a column on a select as SQL_BLOB I get no data back at all:
Before this test in.png is a valid png file and out.png does not exist.
Output is:
C:>perl sqlite4.pl
UTF8 flag 1
UTF8 flag
Use of uninitialized value $col2 in print at sqlite4.pl line 65, <$ifh>
line 1.
C:>dir out.png
27/09/2011 22:44 0 out.png
1 File(s) 0 bytes
0 Dir(s) 15,804,985,344 bytes free
Altering the script to use the bind_col without specifying the type
produces:
C:>perl sqlite4.pl
UTF8 flag 1
UTF8 flag
C:>dir out.png
27/09/2011 22:46 12,987 out.png
1 File(s) 12,987 bytes
0 Dir(s) 15,804,968,960 bytes free
Also selectall_arrayref with out any bound columns works.
In other words, specifying a type on bind_col seems to result in
returning no data at all.
Some DBDs ignore the TYPE on bind_col (actually, most do) - DBD::SQLite
does not appear to be one of those though (based on the above results).
I was experimenting with the idea that specifying a type of SQL_BLOB on
insert in the params OR specifying a type of SQL_BLOB on select in bound
columns would return a blob without the utf8 flag on.
The script I was running is:
use strict;
use warnings;
use DBI qw(:sql_types);
use Data::Dumper;
use Encode;
my $euro = "\x{20ac}";
my $h = DBI->connect("dbi:SQLite:dbname=test.db", '', '',
{RaiseError => 1,
# enable the following or you won't get unicode back:
sqlite_unicode => 1});
eval {
$h->do(q/drop table test1/);
};
$h->do(q/create table test1 (a varchar(50), b blob)/);
my $s = $h->prepare(q/insert into test1 values(?, ?)/);
open(my $ifh, "<:raw", "in.png");
my $png;
{
local $/ = undef;
$png = <$ifh>;
}
# if you bind as a SQL_BLOB on insert then when you retrieve the blob
# it does not come back with utf8 flag on \o/
$s->bind_param(1, $euro);
$s->bind_param(2, $png,
{TYPE => SQL_BLOB}
);
$s->execute;
#$s->execute($euro, $png);
my ($col1, $col2);
$s = $h->prepare(q/select * from test1/);
$s->bind_col(1, \$col1);
# works:
#$s->bind_col(2, \$col2);
# returns nothing:
$s->bind_col(2, \$col2, SQL_BLOB);
# returns nothing:
#$s->bind_col(2, \$col2, {TYPE=>SQL_BLOB});
$s->execute;
$s->fetch;
#print "$col1\n";
# instead of binding if you do the following it works
#my $row = $h->selectrow_arrayref(q/select * from test1/);
#($col1, $col2) = @{$row->[0]};
##print Dumper($row);
#
print "UTF8 flag ", Encode::is_utf8($col1), "\n";
print "UTF8 flag ", Encode::is_utf8($col2), "\n";
#####$s = $h->prepare(q/update test1 set b = ? where a = ?/);
#####$s->execute($euro, $png);
#####
#####$row = $h->selectrow_arrayref(q/select * from test1/);
######print Dumper($row);
######
#####print "UTF8 flag ", Encode::is_utf8($row->[0]), "\n";
#####print "UTF8 flag ", Encode::is_utf8($row->[1]), "\n";
open(my $ofh, ">:raw", "out.png");
print $ofh $col2;
close $ofh;
Martin
--
Martin J. Evans
Wetherby, UK