Beefy Boxes and Bandwidth Generously Provided by pair Networks
There's more than one way to do things

Mysterious null-padding

by belg4mit (Prior)
on Jan 27, 2016 at 21:48 UTC ( #1153796=perlquestion: print w/replies, xml ) Need Help??

belg4mit has asked for the wisdom of the Perl Monks concerning the following question:

Hello all, I'm updating a LONGBINARY column in a Jet 4 (MDB) database on Windows with DBD::ODBC and have encountered an odd issue. Data that should look like: 0x3000000040021000400000000000000000000e04000005140000822400000724 Ends up being inserted like this: 0x030000000000000004002000010000000400000000000000000000000000000000000000E0004000000000005000410000000000280042000000000070004200 i.e; there is a null byte inserted between each legitimate byte of data. This seems similar maybe to an issue discussed here When I enable some DBI tracing $DBH->trace($DBH->parse_trace_flags('SQL|odbcconnection|2|odbcunicode')); I get the following:

DBI::db=HASH(0x24d0eb8) trace level set to 0x6000100/2 (DBI @ 0x0/ +0) in DBI 1.634-ithread (pid 10652) -> prepare for DBD::ODBC::db (DBI::db=HASH(0x24d0f90)~0x24d0eb8 'U +pdate Room Set FloorUValue=? WHERE Number=?') thr#519fb8 SQLPrepare Update Room Set FloorUValue=? WHERE Number=? Processing non-utf8 sql in unicode mode <- prepare= ( DBI::st=HASH(0x24d35b8) ) [1 items] at li +ne 90 -> execute for DBD::ODBC::st (DBI::st=HASH(0x24d35b8)~0x24d45c8 '. +.... ............&#945;@..PA..(B..pB' 1) thr#519fb8 <- execute= ( 1 ) [1 items] at line 91 -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x24d45c8)~INNER) thr#5 +19fb8 <- DESTROY= ( undef ) [1 items] at line 70 3000000040021000400000000000000000000e04000005140000822400000724! -> + DESTROY for DBD::ODBC::db (DBI::db=HASH(0x24d0eb8)~INNER) thr#519fb8 + SQLDisconnect=0 ! <- DESTROY= ( undef ) [1 items] during global destruction
Most material out there on Unicode issues with databases seem to be with people having character data treated as bytes, whereas I seem to be in the opposite predicament. I've also tried utf8::downgrade and explicitly setting the encoding to UCS-2LE to no avail. DBI::data_string_desc returns "UTF8 off, non-ASCII" Any assistance would be much appreciated.

UPDATE: I have the problem whether I use the default DBD::ODBC from Strawberry Perl, or one built specifically with Unicode disabled.

In Bob We Trust, All Others Bring Data.

Replies are listed 'Best First'.
Re: Mysterious null-padding
by Corion (Pope) on Jan 28, 2016 at 08:50 UTC

    I have no deep insight on DBD::ODBC, but maybe you can use the ->bind_param method of DBI to tell DBI to keep the data as-is instead of trying to convert the data to some other encoding.

      We have a winner! As mje was able to confirm, MicroSoft is not <drumRoll/> fully standards compliant, and is massaging the data because it assumes it's a VARCHAR.

      In Bob We Trust, All Others Bring Data.

Re: Mysterious null-padding
by mje (Curate) on Jan 28, 2016 at 14:46 UTC

    I see you've also sent this to the dbi-users list and I've answered there. In case you see this first.

    To help identify the problem could you please provide:

    DBI version DBD::ODBC version

    Assuming you have recent DBI and DBD::ODBC could you set DBI_TRACE=DBD=x.log then rerun your code e.g.,

    set DBI_TRACE=DBD=x.log perl

    It should produce DBD::ODBC specific output in x.log that will help me identity the problem. There is no need to send the log to the list.

    If this produces nothing in x.log you've probably got an older DBI in which case use:

    set DBI_TRACE=15=x.log perl

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://1153796]
Approved by stevieb
Front-paged by Corion
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others contemplating the Monastery: (3)
As of 2019-05-26 05:50 GMT
Find Nodes?
    Voting Booth?
    Do you enjoy 3D movies?

    Results (153 votes). Check out past polls.

    • (Sep 10, 2018 at 22:53 UTC) Welcome new users!