Perl DBI Issue With iSeries AS400 Returning Null Columns on 64-bit Linux
We’re having an issue with database queries to the iSeries AS400 through Perl using DBI and DBD::ODBC. The issue occurs when the SQL statement contains an outer join and the query returns rows with NULL columns:
my $sql_query = qq|select PRSK01, PSTA15 from TESTHA.POLMAST left join TESTHA.P15POLDP on POLC01 = PPOL15 where POLC01 = 003950136|; # Execute the sql query my $sth = $dbh->prepare( $sql_query ) or die $sql_query, "
\n", $DBI::errstr; my $data = $dbh->selectall_arrayref( $sth, {Columns=>{}}, @sql_data ) or die $sql_query, "
\n", $sth->errstr;
The error returned is:
DBD::ODBC::db selectall_arrayref failed: st_fetch/SQLFetch (long truncated DBI attribute LongTruncOk not set and/or LongReadLen too small) (SQL-HY000)
The error message lead me to believe that we could fix it by setting
$dbh->{ LongTruncOK } = 1
before making the query. But this
actually gave us
some serious side effects. Instead of a null or an empty string, DB
returned two (or more) ‘\0’ characters. Here is an example from Data::Dumper.
The ‘^@’ characters are zero byte (Hex 00) characters.
$VAR1 = [ { 'ADJNAM' => 'A. Jonathan - Arb', 'CNPD75' => '^@^@', 'CADN75' => '^@^@^@^@^@^@^@^@^@^@^@^@^@^@', 'CIDO75' => '^@^@', 'CADC75' => '^@^@^@', 'ADJCOD' => 'U2' },
Fortunately, there is a better work-around. In the SQL, return an empty string if the column is null, otherwise return the column:
my $sql_query = qq|select PRSK01, case when PSTA15 is null then '' else PSTA15 end as PSTA15 from TESTHA.POLMAST left join TESTHA.P15POLDP on POLC01 = PPOL15 where POLC01 = 003950136|;
Note that this occurs both on RHEL6 and Linux Mint 12, on both perl v5.10.1 and v 5.12.4, with DBI version 1.622 and DBD::ODBC version 1.39. It does not happen using the command line isql program to run the query.
Last Updated: Wed, 08 Aug 2012
Feedback