| Subject: | iSeries AS400 Returning Null Columns on 64-bit Linux |
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 |
#!/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 |
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!