perldoc DBD::SQLite

DRIVER PRIVATE ATTRIBUTES ^
Database Handle Attributes

...

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. 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 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:

Code: (dl )

1
2
3
4
5
6

        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.