On Wed, 28 Sep 2011 08:45:16 +0100, "Martin J. Evans" <martin.ev...@easysoft.com> wrote:
>On 28/09/11 01:17, Kenichi Ishigaki wrote: >> And this bind_col() issue is a bug in DBD::SQLite. >> I'll write a fix and a test for it later. >> >> Thanks, >> >> Kenichi > >Do you want an rt? Yes, please. Thanks, Kenichi >Martin > >> 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