DBD::Firebird encoding/decoding-CodePudding

In this example, Firebird returns the string undecoded. Have I not set up the database correctly or is this how Firebird works?

#!/usr/bin/env perl
use warnings;
use 5.10.0;
use utf8;
use open qw( :std :utf8 );
use Devel::Peek;
use DBI;

my ( $db, $dbh, $sth, @array );
my $table = 'test_encoding';
my $create = "CREATE TABLE $table ( name varchar(32) )";
my $insert = "INSERT INTO $table ( name ) VALUES ( ? )";
my $select = "SELECT * FROM $table";
my $value = 'ä';

$db = '/home/me/my_firebird_db';
$dbh = DBI->connect(
    "dbi:Firebird:db=$db", 'user', 'password',
    { PrintError => 0, RaiseError => 1, ib_charset => 'UTF-8' }
);
$sth = $dbh->do( "DROP TABLE $table" );
$sth = $dbh->do( $create );;
$sth = $dbh->prepare( $insert );
$sth->execute( $value );
@array = $dbh->selectrow_array( $select );
Dump $array[0];
say $array[0];
say "";

$db = '/home/me/my_sqlite_db';
$dbh = DBI->connect(
    "dbi:SQLite:db=$db", '', '',
    { PrintError => 0, RaiseError => 1, sqlite_string_mode => 5 }
);
$sth = $dbh->do( "DROP TABLE IF EXISTS $table" );
$sth = $dbh->do( $create );
$sth = $dbh->prepare( $insert );
$sth->execute( $value );
@array = $dbh->selectrow_array( $select );
Dump $array[0];
say $array[0];

Output:

SV = PV(0x2105360) at 0x22628a0
  REFCNT = 1
  FLAGS = (POK,pPOK)
  PV = 0x22a37e0 "\303\244"\0
  CUR = 2
  LEN = 10
Ã¤

SV = PV(0x2111470) at 0x2121220
  REFCNT = 1
  FLAGS = (POK,pPOK,UTF8)
  PV = 0x1f2fed0 "\303\244"\0 [UTF8 "\x{e4}"]
  CUR = 2
  LEN = 10
ä

CodePudding user response：

As indicated by the link to the documentation of DBD::Firebird supplied by clamp in the comments, you need to connect using:

$dbh = DBI->connect(
    "dbi:Firebird:db=$db;ib_charset=UTF8", 'user', 'password',
    { PrintError => 0, RaiseError => 1, ib_enable_utf8 => 1 }

That is, the ib_charset property must be in the connection string, not in the hash, and its value must be UTF8 (not UTF-8!), and you need to add ib_enable_utf8 with setting 1 to the hash.

Specifically, this part:

ib_enable_utf8 (driver-specific, boolean)

Setting this attribute to TRUE will cause any Perl Unicode strings supplied as statement parameters to be downgraded to octet sequences before passing them to Firebird.

Also, any character data retrieved from the database (CHAR, VARCHAR, BLOB sub_type TEXT) will be upgraded to Perl Unicode strings.

Caveat: Currently this is supported only if the ib_charset DSN parameter is UTF8. In the future, encoding and decoding to/from arbitrary character set may be implemented.

Example:
$dbh = DBI->connect( 'dbi:Firebird:db=database.fdb;ib_charset=UTF8',
    { ib_enable_utf8 => 1 } );