CODEWITHSUNDEEP
sqlperlinformix
swornabsent
swornabsent

Reputation: 994

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 lastseen column (of whatever data type):

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

then 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 NULLS OK 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);
    $sth->execute;
    while(defined(my $row = $sth->fetchrow_arrayref)) {
        # do ETL stuff with $row
    }
}

Upvotes: 2

Views: 778

Answers (2)

swornabsent
swornabsent

Reputation: 994

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.

Upvotes: 0

Maciej Piaseczny
Maciej Piaseczny

Reputation: 59

Check this - section NULL in Perl. It seems that with this driver there is no simple way to handle this problem.

Upvotes: -1

Related Questions