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

[Martin J. Evans]

On 06/09/12 18:32, Keith Carangelo wrote:

Hello,

We connect to an iSeries AS400 using DBI and DBD::ODBC on a RHEL6 64-bit
web server. Since upgrading to a 64-bit server, we've had trouble with
outer joins that return NULLs (when there is no corresponding record on the
joined table).

select a.PRSK01, b.PSTA15
     from TESTHA.POLMAST a
     left join TESTHA.P15POLDP b
     on a.POLC01 = b.PPOL16
where POLC01 = 3950136

This statement generates a "long truncated DBI attribute LongTruncOk not
set and/or LongReadLen too small)" error. A DBI trace shows that the
datalen of column #2 is being set to 429467295 (0xffffffff) instead of -1:

     bind_columns fbh=ba55a0 fields=2
     Bind 1: type = CHAR(1), buf=c35940, buflen=4
     Bind 2: type = CHAR(1), buf=c35944, buflen=3
     bind_columns=0
     SQLFetch=0
     fetch num_fields=2
     fetch col#1 PRSK01 datalen=1 displ=4
     '2'(1)
     fetch col#2 PSTA15 datalen=4294967295 displ=3
     !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated DBI
attribute LongTruncOk not set and/or LongReadLen too small),
handles=(bdfef0,be0600,c4e040)
     !SQLError(bdfef0,be0600,c4e040) = (HY000, 1, st_fetch/SQLFetch (long
truncated DBI attribute LongTruncOk not set and/or LongReadLen too small))

I can work around the error in ODBC::DBD by setting the datalen to -1 in
dbdimp.c, but I'm hoping there is a better solution.

         if (DBIc_TRACE(imp_sth, DBD_TRACING, 0, 4))
             PerlIO_printf(
                 DBIc_LOGPIO(imp_dbh),
                 "    fetch col#%d %s datalen=%ld displ=%lu\n",
                 i+1, fbh->ColName, (long)fbh->datalen,
                 (unsigned long)fbh->ColDisplaySize);

if (fbh->datalen == 0xffffffff) {
   fbh->datalen = -1;
  }

         if (fbh->datalen == SQL_NULL_DATA) {    /* NULL value        */
             SvOK_off(sv);
             continue;
         }

This error does not occur using isql, which is part of unixOBDC.

Any hints to how I can better fix this issue?

Thanks,
Keith Carangelo

How recent are your DBI and DBD::ODBC? If you can get them up to date with the 
recent releases could then send me a trace of a simple script illustrating your 
problem. You'd do that like this:

export DBI_TRACE=DBD=x.log
perl myscript.pl

and if you are using bash you can do that all in one go.

It might also be worth rt'ing it at rt.cpan.org. I'm not sure it is a bug in 
DBD::ODBC as yet but I would not rule it out.

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