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

[Kenichi Ishigaki]

Hi, Martin. I'm afraid your script has a bug: 

>$s = $h->prepare(q/update test1 set b = ? where a = ?/);
>$s->execute($euro, $png);

reverse the order of params, and you'll see the difference.

Regards,

Kenichi


On Sun, 25 Sep 2011 13:12:47 +0100, "Martin J. Evans" <martin.evans at easysoft.com> 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
>
>_______________________________________________
>DBD-SQLite mailing list
>DBD-SQLite at lists.scsys.co.uk
>http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite