Subject: | DBD::Pg 3.x BYTEA issues with UTF-8 encoded database |
Date: | Wed, 30 Apr 2014 12:48:51 -0400 |
To: | bug-DBD-Pg [...] rt.cpan.org |
From: | Michael Graziano <michael.graziano [...] premierheart.com> |
We’ve encountered a regression in DBD::Pg 3.0 which I believe is related to the new unicode handling - bytea strings being passed to the database are being mangled when inserting into a utf-8 encoded database.
The affected perl code worked on both SQL_ASCII and utf-8 encoded databases under the 2.19.3 DBD::Pg driver.
Under the 3.x driver the bytea data is corrupted when we insert into a utf-8 encoded database (the inserted data is longer than the contents being inserted), but the code continues to work normally on a SQL_ASCII encoded database.
The affected code (extract) is:
####### BEGIN EXCERPT #######
# Generate File1
$file1_contents = pack ( "a13a4a2a17a14a13a17a16a2LSaaaaaaa20a12aaa94Sa11LS”,
$fn, $a, $s, $n, 0, $h, $cid,
"0000000", "N", $date, $hid,
"", "", "", "", "", "",
"", "", "", "", "NNNNNNNNNN", 0, "",
&UnixDate($db, “%s"), 0x6E
);
# Generate File2 from File1 and raw data
## raw_data is from sysread()
$file2_contents = pack( “a256a32776”, $file1_contents, $raw_data );
$stmt = $db->prepare(“INSERT INTO $table ( field1, field2) VALUES ( ?, ? ) “ );
if (! defined($stmt) ) {
show_error( "Unable to prepare INSERT $table : $DBI::errstr" );
$db->disconnect();
exit -3;
}
$stmt->bind_param( 1, $file1_contents, { pg_type => DBD::Pg::PG_BYTEA } );
$stmt->bind_param( 2, $file2_contents, { pg_type => DBD::Pg::PG_BYTEA } );
if (! $stmt->execute() ) {
show_error( "Unable to INSERT $table : $DBI::errstr" );
$stmt->finish;
$db->disconnect();
exit -4;
}
$stmt->finish;
######## END EXCERPT ########
It appears this is still the correct way to handle bytea inserts and should “Just Work”, so I’m raising this as a bug - if we’re screwing up something obvious please let me know. (Unfortunately recoding to SQL_ASCII is absolutely not an option for us - in-database encoding enforcement is needed.)
OS Information:
FreeBSD dbi01.dev.premierheart.com 9.1-RELEASE-p11 FreeBSD 9.1-RELEASE-p11 #2 r264302: Wed Apr 9 16:36:12 EDT 2014 root@SYSBUILDER.dev.premierheart.com:/usr/obj/usr/src/sys/ph-amd64 amd64
Database Information:
PostgreSQL 9.3.1
Perl Information:
This is perl 5, version 18, subversion 2 (v5.18.2) built for amd64-freebsd-thread-multi
Tested broken on:
DBD::Pg 3.0.0 ; DBD::Pg 3.1.1
Using DBI 1.631
Message body not shown because it is not plain text.