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

[Martin J. Evans]

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