Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 79498
Status: rejected
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: mail [...] kcaran.com
Cc:
AdminCc:

Bug Information
Severity: Important
Broken in: 1.39
Fixed in: (no value)



Subject: iSeries AS400 Returning Null Columns on 64-bit Linux
Download (untitled) / with headers
text/plain 1.1k
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). This error does not occur using isql, which is part of unixOBDC. mater6:~/as400_dbd$ export DBI_TRACE=DBD=trace.log; perl 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 line 25. 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); /****** Cast fbh->datalen to int to avoid the overflow ******/ if ((int) fbh->datalen == (int) SQL_NULL_DATA) { /* NULL value */ SvOK_off(sv); continue; }
Subject: test_as400.pl
Download test_as400.pl
text/x-perl 672b
#!/usr/bin/perl use strict; use warnings; use DBI; use DBD::ODBC; print "DBI Version: $DBI::VERSION\n"; print "DBD::ODBC Version: $DBD::ODBC::VERSION\n"; my $dbh = DBI->connect( 'DBI:ODBC:SICASA2', 'webtest', 'xxxxxxxxx', {} ) or die "Connect faild $DBI::errstr"; # # This select statement returns a single null value # my $sql_query = qq|select NULLIF(0,0) from TESTHA.POLMAST fetch first 1 rows only|; my @sql_data; # Execute the sql query my $sth = $dbh->prepare( $sql_query ) or die $sql_query, "<BR>\n", $DBI::errstr; my $data = $dbh->selectall_arrayref( $sth, {Columns=>{}}, @sql_data ) or die $sql_query, "<BR>\n", $sth->errstr;
Subject: trace.log
Download trace.log
text/x-log 2.1k
non-Unicode login6_sv dbd_db_login6 SQLDriverConnect 'SICASA2', 'webtest', 'xxxx' SQLDriverConnect failed: SQLConnect 'SICASA2', 'webtest' Turning autocommit on DRIVER_ODBC_VER = 03.51 DRIVER_NAME = libcwbodbc.so, type=0 DRIVER_VERSION = 07.01.0003 MAX_COLUMN_NAME_LEN = 128 DBD::ODBC is unicode built : NO SQLMoreResults supported: 1 SQLDescribeParam supported: 1 !!DBD::ODBC unsupported attribute passed (PrintError) setting AutoCommit !!DBD::ODBC unsupported attribute passed (Username) !!DBD::ODBC unsupported attribute passed (dbi_connect_closure) initializing sth query timeout to -1 ignore named placeholders = 0 SQLPrepare select NULLIF(0,0) from TESTHA.POLMAST fetch first 1 rows only Processing sql in non-unicode mode for SQLPrepare SQLPrepare = 0 +dbd_st_execute(101b3b8) dbd_st_finish(101b3b8) outparams = 0 SQLExecute/SQLExecDirect(1128680)=0 SQLRowCount=0 (rows=-1) SQLNumResultCols=0 (flds=1) dbd_describe done_desc=0 dbd_describe SQLNumResultCols=0 (columns=1) DescribeCol column = 1, name = 00001, namelen = 5, type = INTEGER(4), precision/column size = 10, scale = 0, nullable = 1 SQL_COLUMN_DISPLAY_SIZE = 11 SQL_COLUMN_LENGTH = 10 now using col 1: type = INTEGER (4), len = 10, display size = 4, prec = 10, scale = 0 -dbd_describe done_bind=0 have 1 fields -dbd_st_execute(101b3b8)=-1 dbd_st_FETCH_attrib NAMES 1 00001 Colname 0 => 00001 dbd_st_FETCH_attrib NUM_OF_FIELDS 1 bind_col 1 requested type:0, flags:0 bind_columns fbh=115d940 fields=1 Bind 1: type = INTEGER(4), buf=115d7f0, buflen=4 bind_columns=0 SQLFetch=0 fetch num_fields=1 fetch col#1 00001 datalen=4294967295 displ=4 !!dbd_error2(err_rc=-999, what=st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small), handles=(1108050,1108640,1128680) !SQLError(1108050,1108640,1128680) = (HY000, 1, st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small)) dbd_st_finish(1178050) dbd_st_finish closed query: SQLFreeHandle(stmt)=0 SQLDisconnect=0 DBD::ODBC Disconnected!
Download (untitled) / with headers
text/plain 2.2k
On Fri Sep 07 09:05:11 2012, JoseRijo wrote: Show quoted text
> We connect to an iSeries AS400 using DBI and DBD::ODBC on a RHEL6 64-
bit Show quoted text
> 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 Show quoted text
> the joined table). > > This error does not occur using isql, which is part of unixOBDC.
<snipped> Attached are my comments when I answered this on dbi-users list (before it was reported here): 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 Wetherby, UK
Download (untitled) / with headers
text/plain 2.5k
On Fri Sep 07 10:59:28 2012, MJEVANS wrote: Show quoted text
> On Fri Sep 07 09:05:11 2012, JoseRijo wrote:
> > We connect to an iSeries AS400 using DBI and DBD::ODBC on a RHEL6
64- Show quoted text
> bit
> > web server. Since upgrading to a 64-bit server, we've had trouble
with Show quoted text
> > outer joins that return NULLs (when there is no corresponding record
> on
> > the joined table). > > > > This error does not occur using isql, which is part of unixOBDC.
> > <snipped> > > Attached are my comments when I answered this on dbi-users list
(before Show quoted text
> it was reported here): > > Keith, > > I've cc'ed my answer to dbi-users who did not see your log file and
test Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> 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 Show quoted text
> it then rebuilding DBD::ODBC but that is too involved to describe
here. Show quoted text
> > Martin
I see no way at this time of progressing this issue and I don't think it is a DBD::ODBC one anyway. I'll close it in a few days unless you have something else to add. Martin -- Martin J. Evans Wetherby, UK
Subject: Re: [rt.cpan.org #79498] iSeries AS400 Returning Null Columns on 64-bit Linux
Date: Fri, 30 Nov 2012 11:14:30 -0500
To: Martin J Evans via RT <bug-DBD-ODBC [...] rt.cpan.org>
From: "Keith A. Carangelo" <mail [...] kcaran.com>
Download (untitled) / with headers
text/plain 2.9k
Hi Martin, I'm sorry, I thought it was already closed. Thank you very much for your help! Keith Carangelo On Thu, Nov 29, 2012 at 04:55:02AM -0500, Martin J Evans via RT wrote: Show quoted text
> <URL: https://rt.cpan.org/Ticket/Display.html?id=79498 > > > On Fri Sep 07 10:59:28 2012, MJEVANS wrote:
> > On Fri Sep 07 09:05:11 2012, JoseRijo wrote:
> > > 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). > > > > > > This error does not occur using isql, which is part of unixOBDC.
> > > > <snipped> > > > > Attached are my comments when I answered this on dbi-users list
> (before
> > it was reported here): > > > > 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
> > I see no way at this time of progressing this issue and I don't think it > is a DBD::ODBC one anyway. I'll close it in a few days unless you have > something else to add. > > Martin > -- > Martin J. Evans > Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.