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
