| Subject: | Best way to insert binary null columns? |
Using DBD::SQLite 1.03, what's the best way to insert null characters (\0)? Someone suggested I use the sqlite_handle_binary_nulls attribute, but I don't see that documented in this version, and it doesn't appear to work.
#!/usr/local/bin/perl
use strict;
use DBI;
use DBD::SQLite;
print "DBD::SQLite version: ", DBD::SQLite->VERSION, "\n";
my $dbh = DBI->connect('dbi:SQLite:dbname=testnull', '', '', { RaiseError => 1,
PrintError => 0, AutoCommit => 1, sqlite_handle_binary_nulls => 1 });
$dbh->{sqlite_handle_binary_nulls} = 1; # Just in case
$dbh->do(qq(CREATE TABLE test (test_blob BLOB)));
my $string = "foo\0bar\0foo";
$dbh->do(qq(INSERT INTO test (test_blob) VALUES (?)), undef, $string);
my ($length, $return) = $dbh->selectrow_array(
qq(SELECT LENGTH(test_blob), test_blob FROM test));
print "Return value: $return\n";
print "Length: $length\n";
__END__
DBD::SQLite version: 1.03
Return value: foo
Length: 3
I worked around by doing something like this:
# before insert
$string =~ s/\\/\\\\/g;
$string =~ s/\0/\\0/g;
# after select
$string =~ s/\\0/\0/g;
$string =~ s/\\\\/\\/g;
It would be nice if \0 were escaped automatically by using placeholders or quote(), especially for BLOB columns.
Thanks!