Re: DBI Issue With iSeries AS400 Returning Null Columns on 64-bit Linux

On 07/09/12 16:05, Keith Carangelo wrote:
> Martin,
>
> Your diagnosis is correct. Unfortunately, the ODBC driver I'm using is
> IBM's "iSeries Access for Linux 64-bit ODBC Driver". I'm sure I'll have a
> lot of luck getting them to change it.

I think you might be surprised if you have a license and you ask them. I'm pretty sure they will already know about this and might have already changed it.

> The only thing I don't understand is
> that everything works fine using unixODBC's isql command line tool.

I don't think isql binds columns, I think it uses SQLGetData. There are loads of other differences including the fact that isql probably doesn't even test for truncated data.

> I think my best workaround is modifying dbdimp.c to cast the datalen to an
> int before checking if it is equal to SQL_NULL_DATA. We don't have a lot of
> data with a length of over 4 billion!

It is certainly the easiest looking change but you'll find a few more places where SQLLEN/SQLULEN are used.

  
> Thanks,
> Keith

I'll write your rt off now unless you object.

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> On Fri, Sep 7, 2012 at 10:10 AM, Martin J. Evans
> <[email protected]>wrote:
>
>> On 07/09/12 13:49, Keith Carangelo wrote:
>>
>>> Hi Martin,
>>>
>>> Thank you so much for your response. I've attached the simplest sql query
>>> I could think of - it just returns NULL in a single row and column, and the
>>> log file. Here is the output:
>>>
>>> mater6:~/as400_dbd$ export DBI_TRACE=DBD=trace.log; perl test_as400.pl <
>>> http://test_as400.pl>
>>>
>>> DBI Version: 1.622
>>> DBD::ODBC Version: 1.39
>>> DBD::ODBC::db selectall_arrayref failed: st_fetch/SQLFetch (long
>>> truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)
>>> (SQL-HY000) at test_as400.pl <http://test_as400.pl> line 25.
>>>
>>>
>>> I think I'm on the latest versions of DBI and DBD::ODBC.
>>>
>>>
>> Keith,
>>
>> I've cc'ed my answer to dbi-users who did not see your log file and test
>> script.
>>
>> I got a quick 5 mins to look at it and the news is not good for you.
>>
>> You said you were on a 64 bit platform and I believe your perl is 64 bit.
>> You can look at perl -V to check this and run odbcinst -j to see what
>> unixODBC thinks the size of SQLLEN and SQLULEN are. I think you'll find
>> unixODBC says SQLLEN/SQLULEN are 8 bytes and DBD::ODBC will do whatever
>> unixODBC says. If you unixODBC and Perl are 64 bit your libcwbodbc.so (btw,
>> what is this ODBC driver) must be 64 bit too and hence should also be using
>> an 8 byte SQLLEN/SQLULEN. However, in your SQL:
>>
>> select NULLIF(0,0) from TESTHA.POLMAST fetch first 1 rows only
>>
>> I believe this returns a NULL and so when DBD::ODBC binds the column as an
>> SQL_C_LONG the driver sets the returned indicator to say SQL_NULL_DATA
>> which is -1. However, as your driver looks like it thinks SQLLEN/SQLULEN
>> are 4 byte quantities it writes 0xFFFFFFFF (-1 if a 4 byte integer) into a
>> 8 byte quantity which now looks like 4294967295 and DBD::ODBC thinks the
>> column has been truncated.
>>
>> So, basically, I think your ODBC driver is broken but you'd have to
>> confirm some of my guess work above. If I'm right you'll need to ask your
>> driver manufacturer to rebuild their 64 bit driver with SQLLEN/SQLULEN as 8
>> bytes on 64 bit platforms (perhaps it does not even know about
>> SQLLEN/SQLULEN and is still using SQLINTEGER for its indicators).
>>
>> You could try binding the column as an SQL_VARCHAR as a workaround but I'm
>> not 100% sure that will work.
>>
>> If you really cannot get your driver fixed you could try forcing unixODBC
>> to make SQLLEN/SQLULEN 4 bytes in its header files, rebuilding it then
>> rebuilding DBD::ODBC but that is too involved to describe here.
>>
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>
>
>