DBの文字コードUTF-8と仲良くする

• DBD::SQLiteの入出力をちゃんと考えてみる - 日曜プログラマのそゞろ事 写経！

# vim:set ts=4 ai:
use strict;
use warnings;
use utf8;
use DBI;
use Encode;
binmode(STDOUT, ":encoding(UTF-8)"); # perlcritic で utf8 よりこっちの方が良いとのこと
binmode(STDIN,  ":encoding(UTF-8)");

my $database = ':memory:';
my $dbh = DBI->connect("dbi:SQLite:dbname=$database");
print "ver" . $dbh->{sqlite_version} . "\n";

sql_do($dbh, "CREATE TABLE tbl (id integer primary key, name text);");
sql_do($dbh, "INSERT INTO tbl (id, name) values ('1','日本語');");
sql_do($dbh, "INSERT INTO tbl (id, name) values ('2','あいうえお');");
sql_do($dbh, "INSERT INTO tbl (id, name) values ('3','だだだ');");

my $sth = $dbh->prepare("SELECT id,name FROM tbl");
$sth->execute();
$sth->bind_columns(\my($id, $name));
while ($sth->fetchrow_arrayref) {
    print "$id" . decode_utf8($name) . "\n";
}

$sth->finish; $sth = undef;
$dbh->disconnect;

sub sql_do
{
    my ($dbh, $statement) = @_;
    $statement = encode_utf8($statement);
    $dbh->do($statement);
}
__END__
ver3.7.9
1日本語
2あいうえお
3だだだ