Re: Extra nulls inserted with binary data (DBD::ODBC on Win7)

On 27/01/16 22:10, Jerrad Pierce wrote:
> Hello all, I'm updating a LONGBINARY column in a Jet 4 (MDB) database on Windows 7 with
> DBD::ODBC and have encountered an odd issue. Rather than the common problem of Unicode
> data being treated as bytes, I have bytes that seem to be being treated as UCS-2LE.
>
> Data that should look like this in hex:
> 0x3000000040021000400000000000000000000e04000005140000822400000724
>
> Ends up end the database like this: 0x030000000000000004002000010000000400000000000000000000000000000000000000E0004000000000005000410000000000280042000000000070004200
>
> i.e; there is a null byte inserted between each legitimate byte of data.
> How can I ensure the integrity of my inserted data?
>
> DBI tracing with SQL|odbcconnection|2|odbcunicode emits the following for a column update:
>
> 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 'Update 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 db_ODBC.pl line 90 -> execute for DBD::ODBC::st (DBI::st=HASH(0x24d35b8)~0x24d45c8 '..... ............α@..PA..(B..pB' 1) thr#519fb8 <- execute= ( 1 ) [1 items] at db_ODBC.pl line 91 -> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x24d45c8)~INNER) thr#519fb8 <- DESTROY= ( undef ) [1 items] at db_ODBC.pl line 70 3000000040021000400000000000000000000e04000005140000822400000724! -> DESTROY for DBD::ODBC::db (DBI::db=HASH(0x24d0eb8)~INNER) thr#519fb8 SQLDisconnect=0 ! <- DESTROY= ( undef ) [1 items] during global destruction And DBI::data_string_desc returns "UTF8 off, non-ASCII". Any assistance would be much
> appreciated. (A similar inquiry has been posted at http://perlmonks.org/?node_id=1153796)
>
> --
> Jerrad Pierce, Research Assistant 2, NMR Group
> 617-284-6230x21
>

Hello Jerrad,

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 mycode.pl

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 mycode.pl

It would also be useful to know the column type in your access DB.

Thanks

Martin