[DBD-SQLite] Could someone explain the pod re sqlite_unicode
and blobs?
Martin J. Evans
martin.evans at easysoft.com
Wed Sep 28 14:51:44 GMT 2011
On 28/09/11 14:09, Kenichi Ishigaki wrote:
>
> On Wed, 28 Sep 2011 08:45:16 +0100, "Martin J. Evans"<martin.evans at 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
rt 71311
Martin
>
>
>
>> 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