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, "<BR>\n", $DBI::errstr;

my $data = $dbh->selectall_arrayref( $sth, {Columns=>{}}, @sql_data )
                    or die $sql_query, "<BR>\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.