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.ev...@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@lists.scsys.co.uk >http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite _______________________________________________ DBD-SQLite mailing list DBD-SQLite@lists.scsys.co.uk http://lists.scsys.co.uk/cgi-bin/mailman/listinfo/dbd-sqlite