On Tuesday 12 September 2017 12:27:25 p...@cpan.org wrote:
> To prove fact that other DBI drivers (e.g. Pg or SQLite) had fixed
> similar/same UTF-8 issue as MySQL has and behave Perl-correctly, I
> would provide test cases so you would see difference between Pg,
> SQLite and mysql DBI drivers.
And here is promised script:
=======================================
use strict;
use warnings;
use utf8;
binmode \*STDOUT, ':utf8';
binmode \*STDERR, ':utf8';
use DBI;
sub ords { $_[0] . ' (' . (join ', ', map ord, split //, $_[0]) . ')' }
# DBD::Pg enables Unicode <--> UTF-8 automatically
my $pg_dbh = DBI->connect("dbi:Pg:dbname=postgres", "", "");
# DBD::mysql needs mysql_enable_utf8 and mysql_enable_utf8mb4 for Unicode <-->
UTF-8
my $mysql_dbh = DBI->connect("dbi:mysql:dbname=mysql", "root", "", {
mysql_enable_utf8 => 1, mysql_enable_utf8mb4 => 1 });
# MySQL server by default uses latin1
# Without switch to utf8mb4, Unicode code points above U+FF cannot be stored (á
is below U+FF, č not)
# Another option is to specify own charset in CREATE TABLE
$mysql_dbh->do("SET character_set_database='utf8mb4'");
$mysql_dbh->do("SET character_set_server='utf8mb4'");
# DBD::SQLite needs sqlite_unicode for Unicode <--> UTF-8
my $sqlite_dbh = DBI->connect("dbi:SQLite:dbname=:memory:", "", "", {
sqlite_unicode => 1 });
for my $dbh ($pg_dbh, $mysql_dbh, $sqlite_dbh) {
for my $ins ("\xC3\xA1", "\N{U+C3}\N{U+A1}", "á", "č", "\x{263A}",
"\N{U+263A}", "☺") {
{
$dbh->do("CREATE TEMPORARY TABLE t(s VARCHAR(10))");
my $sth = $dbh->prepare("INSERT INTO t(s) VALUES('$ins')");
$sth->execute();
my $fetch = $dbh->selectrow_array("SELECT s FROM t");
print $dbh->{Driver}->{Name} . ' without bind - insert: ' . ords($ins) .
'; fetch: ' . ords($fetch) . ' - ' . ($ins eq $fetch ? 'OK' : 'FAIL') . "\n";
$dbh->do("DROP TABLE t");
}
{
$dbh->do("CREATE TEMPORARY TABLE t(s VARCHAR(10))");
my $sth = $dbh->prepare("INSERT INTO t(s) VALUES(?)");
$sth->execute($ins);
my $fetch = $dbh->selectrow_array("SELECT s FROM t");
print $dbh->{Driver}->{Name} . ' with bind - insert: ' . ords($ins) . ';
fetch: ' . ords($fetch) . ' - ' . ($ins eq $fetch ? 'OK' : 'FAIL') . "\n";
$dbh->do("DROP TABLE t");
}
}
}
=======================================
It is simple "identity" script, which stores value into database,
fetches it back and then compare results.
Look on results with DBD::mysql 4.041, 4.042 and 4.043.
Are all users of DBD::mysql aware of such results?
And specially look at results with DBD::Pg prior to version 3.3.0 and up.
Also DBD::SQLite prior to version 1.43_04.
Basically DBD::Pg (pg_enable_utf8 = -1) and DBD::SQLite
(via sqlite_unicode=1) had exactly same bug as DBD::mysql.
