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

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