[DBD-SQLite] Could someone explain the pod re sqlite_unicode and blobs?

[Kenichi Ishigaki]

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.evans at 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 at lists.scsys.co.uk
>http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite