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

2016-01-28 Thread Martin J. Evans 
 --- Begin Message ---

On 28/01/16 15:08, Jerrad Pierce wrote:

Hello Martin,

$DBI::VERSION = 1.634
$DBD::ODBC::VERSION = 1.52


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

I'm updating a LONGBINARY column in a Jet 4 (MDB) database.

Thanks

Thank you



I forgot, MS Access does not support the ODBC API SQLDescribeParam which a) it 
should and b) is a PITA when a driver does not. Because DBD::ODBC does not know 
the column type it binds it as a VARCHAR. Access will convert that varchar to a 
binary using whatever conversion algorithm it uses - obviously not one that 
works for you.

I think you will have to resort to specifying the bind type when you call 
bind_param. If you are not calling bind_param right now you'll have to start 
calling it and specify as the type SQL_BINARY.

As bind_param types are sticky you can probably call bind_param like so:

$st->bind_param(1, undef, {TYPE => SQL_BINARY});

and still continue to call execute as before:

$st->execute($parameter_1_value);

or you can just call bind_param with the real value and execute with no values.

Let me know if you need an example or if this does not sort it out for you.

I'm afraid the MS Access ODBC driver is a poor example of ODBC.

http://search.cpan.org/~mjevans/DBD-ODBC-1.52/FAQ#Why_am_I_getting_errors_with_bound_parameters?
 might provide some background.

Martin

--- End Message ---

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

2016-01-28 Thread Jerrad Pierce 

Martin has informed me that Microsoft doesn't support SQLDescribeParam and
in the absence of an explicit bind_param, miscasts the data as VARCHAR.

   $sth->bind_param(1, undef, SQL_LONGVARBINARY);

Solved the problem.

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

2016-01-28 Thread Jerrad Pierce 

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

DBI::db=HASH(0x25f2608) trace level set to 0x6000100/2 (DBI @ 
0x0/0) in DBI1.634-ithread (pid 1844)
-> prepare for DBD::ODBC::db (DBI::db=HASH(0x25f26e0)~0x25f2608 
'Update Room

 Set FloorUValue=? WHERE Number=?') thr#679fb8
SQLPrepare Update Room Set FloorUValue=? WHERE Number=?
  Processing sql in non-unicode mode for SQLPrepare
<- prepare= ( DBI::st=HASH(0x25f6678) ) [1 items] at db_ODBC.pl line 89
-> execute for DBD::ODBC::st (DBI::st=HASH(0x25f6678)~0x25f3058 
'. α@..PA..(B..pB' 1) thr#679fb8

<- execute= ( 1 ) [1 items] at db_ODBC.pl line 90
-> DESTROY for DBD::ODBC::st (DBI::st=HASH(0x25f3058)~INNER) thr#679fb8
<- DESTROY= ( undef ) [1 items] at db_ODBC.pl line 69
3000400210004e04051482240724! -> 
DESTROY for DBD::ODBC::db (DBI::db=HASH(0x25f2608)~INNER) 
thr#679fb8SQLDisconnect=0

!   <- DESTROY= ( undef ) [1 items] during global destruction

--
Jerrad Pierce, Research Assistant 2, NMR Group
617-284-6230x21

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

2016-01-28 Thread Jerrad Pierce 
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:
0x3000400210004e04051482240724

Ends up end the database like this: 
0x0300040021000400E0004000500041002800420070004200 



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 
3000400210004e04051482240724! -> 
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