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

Martin J. Evans martin.evans at easysoft.com
Wed Sep 28 07:45:16 GMT 2011


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?

Martin

> 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



More information about the DBD-SQLite mailing list