And this bind_col() issue is a bug in DBD::SQLite. I'll write a fix and a test for it later.
Thanks, Kenichi On Tue, 27 Sep 2011 22:57:34 +0100, "Martin J. Evans" <martin.ev...@easysoft.com> wrote: >On 25/09/2011 13:12, Martin J. Evans wrote: >> Hi, >> >> From >> http://search.cpan.org/~adamk/DBD-SQLite-1.33/lib/DBD/SQLite.pm#Database_Handle_Attributes >> >> ===== >> sqlite_unicode >> >> If set to a true value, DBD::SQLite will turn the UTF-8 flag on >> for all text strings coming out of the database (this feature is >> currently disabled for perl < 5.8.5). For more details on the UTF-8 >> flag see perlunicode. The default is for the UTF-8 flag to be turned off. >> >> Also note that due to some bizarreness in SQLite's type system >> (see http://www.sqlite.org/datatype3.html), if you want to retain >> blob-style behavior for some columns under $dbh->{sqlite_unicode} = 1 >> (say, to store images in the database), you have to state so >> explicitly using the 3-argument form of "bind_param" in DBI when doing >> updates: >> >> use DBI qw(:sql_types); >> $dbh->{sqlite_unicode} = 1; >> my $sth = $dbh->prepare("INSERT INTO mytable (blobcolumn) VALUES >> (?)"); >> >> # Binary_data will be stored as is. >> $sth->bind_param(1, $binary_data, SQL_BLOB); >> >> Defining the column type as BLOB in the DDL is not sufficient. >> ===== >> >> I don't understand this and wondered if someone could explain it >> better to me. I don't get a) the bolded "some" in "for some columns" >> and when it says "in DBI when doing updates:" it is followed with an >> example which does an insert. >> >> What I'm struggling to understand is what are the precise cases when >> you need to bind a blob as a SQL_BLOB when sqlite_unicode is on? What >> I've found if that if you do an insert or update of a png file into a >> blob field and select it back the UTF8 flag is set on but when the >> blob is inserted with TYPE => SQL_BLOB it is not - is this the problem? >> >> Here is an example: >> >> 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 of 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 uncomment the following 3 lines and comment the 4th >> # then blob data does not come back with UTF8 flag on >> #$s->bind_param(1, $euro); >> #$s->bind_param(2, $png, {TYPE => SQL_BLOB}); >> #$s->execute; >> $s->execute($euro, $png); >> >> my $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"; >> >> $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 $row->[1]; >> close $ofh; >> >> which produces: >> >> UTF8 flag 1 >> UTF8 flag 1 >> UTF8 flag 1 >> UTF8 flag 1 >> >> but when you make the change (see comments) you get: >> >> UTF8 flag 1 >> UTF8 flag >> UTF8 flag 1 >> UTF8 flag >> >> Thanks >> >> Martin >> > >I'd appreciate some feedback as I'm attempting to collate information on >unicode support in different DBDs - how it is implemented and what are >the issues with it so I can present it on dbi-dev to attempt to >standardise unicode support in DBI. > >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 > > >_______________________________________________ >DBD-SQLite mailing list >DBD-SQLite@lists.scsys.co.uk >http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite _______________________________________________ DBD-SQLite mailing list DBD-SQLite@lists.scsys.co.uk http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite