| Subject: | utf8 Flag on For BLOB Data |
| Date: | Wed, 24 May 2006 16:29:56 -0700 |
| To: | bug-dbd-sqlite [...] rt.cpan.org |
| From: | David Wheeler <david [...] kineticode.com> |
Matt,
In testing BLOB support in DBD::SQLite, I took note of these comments
in the documentation:
Show quoted text
> Also note that due to some bizareness 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->{unicode} =
> 1" (say,
> to store images in the database), you have to state so
> explicitely
> using the 3-argument form of "bind_param" in DBI when doing
> updates:
>
> use DBI qw(:sql_types);
> $dbh->{unicode} = 1;
> my $sth = $dbh->prepare
> ("INSERT INTO mytable (blobcolumn) VALUES (?)");
> $sth->bind_param(1, $binary_data, SQL_BLOB); #
> binary_data will
> # be stored as-is.
>
> Defining the column type as BLOB in the DDL is not
> sufficient.
That's great for getting data into the database, but not for getting
it out. This script demonstrates the problem: When unicode => 1 is
enabled (and shouldn't that be sqlite_unicode to be compatible with
DBI's naming conventions?), BLOB data has the utf8 flag switched on,
even if I tell SQLite that the data is binary. This script
demonstrates the issue: It should output "utf8: no" but outputs
"utf8: yes".
#!/usr/local/bin/perl -w
use strict;
use DBI qw(:sql_types);
use File::Basename;
use Encode qw(is_utf8);
my $filename = shift or die "Usage: $0 filename\n";
my $dbh = DBI->connect(
'dbi:SQLite:dbname=try.db', '', '',
{ RaiseError => 1, PrintError => 0, unicode => 1 },
);
END {
$dbh->disconnect;
unlink 'try.db';
}
$dbh->do(q{
CREATE TABLE bin (
id integer primary key autoincrement,
content blob,
filename text
)
});
my $ins = $dbh->prepare(q{
INSERT INTO bin (content, filename)
values (?, ?)
});
open my $fh, '<:bytes', $filename
or die "Cannot open '$filename': $!\n";
my $content = do { local $/; <$fh> };
close $fh or die "Cannot close '$filename': $!\n";
$filename = basename($filename);
$ins->bind_param(1, $content, { TYPE => SQL_BLOB });
$ins->execute($content, $filename);
my $sel = $dbh->prepare(q{
SELECT content, filename
FROM bin
});
$sel->execute;
my ($cont, $fn);
$sel->bind_col(1, \$cont, { TYPE => SQL_BLOB });
$sel->bind_col(2, \$fn);
#$sel->bind_columns(\($cont, $fn));
while ($sel->fetch) {
print 'utf8: ', is_utf8($cont) ? "yes\n" : "no\n";
# open my $out, '>:bytes', $fn
# or die "Cannot open '$fn': $!\n";
# print $out $cont;
# close $out or die "Cannot close '$fn': $!\n";
}