swornabsent swornabsent - 1 month ago 6
SQL Question

Selecting all rows from Informix table containing some null columns

I am using Perl DBD::ODBC to connect to an Informix database which I was previously blind to the schema of. I have successfully discovered the schema via querying tabname and colname tables. I am now iterating over each of those tables extracting everything in them to load into another model. What I am finding is that null columns bail out of the select query. E.g. if a table looked like this, with an optionally null

column (of whatever data type):

ID username lastseen
-- -------- --------
1 joe 1234567890
2 bob 1098765432
3 mary
4 jane 1246803579

select * from mytable
(or specifying all column names indiidually) stops at the mary row.

I do have this working by using NVL as follows:

select nvl(id, ''), nvl(username, ''), nvl(lastseen, '') from mytable

And that's okay, but my question is: Is there a simpler Informix syntax to allow nulls to come into my result set, something as simple as
or something that I am missing? Alternatively, some database handle option to allow the same?

Here is an example of my Perl with the nvl() hack, in case it's relevant:

my %tables = (
users => [
qw(id username lastseen)

foreach my $tbl (sort keys %tables) {
my $sql = 'select ' . join(',', map { "nvl($_, '')" } @{$tables{$tbl}}) . " from $tbl";
# sql like: select nvl(a, ''), nvl(b, ''), ...
my $sth = $dbh->prepare($sql);
while(defined(my $row = $sth->fetchrow_arrayref)) {
# do ETL stuff with $row


After a balked attempt at installing DBD::Informix I came back around to this and found that for some reason enabling LongTruncOk on the database handle did allow all rows including those with null columns to be selected. I don't imagine this is the root of the issue but it worked here.

However, this solution seems to have collided with an unrelated tweak to locales to support non-ascii characters. I added DB_LOCALE=en_us.utf8 and CLIENT_LOCALE=en_us.utf8 to my connection string to prevent selects from similarly breaking when encountering non-ascii characters (i.e., in a result set of say 500 where the 300th row had a non-ascii character the trailing 200 rows would not be returned). With locales set this way as well as LongTruncOk enabled on the dbh all rows are being returned (without the NVL hack), but null columns have bytes added to them from previous rows, and not in any pattern that is obvious to me. When I leave the locale settings off of the connection string and set LongTruncOk, rows with null columns are selected correctly but rows with utf characters break.

So if you don't have a charset issue perhaps just LongTruncOk would work for you. For my purposes I have had to continue using the NVL workaround for nulls and specify the locales for characters.