CODEWITHSUNDEEP
sqlperlinformixdbi
CheeseConQueso
CheeseConQueso

Reputation: 6041

How can I avoid the program quitting when Perl's DBI encounters an error preparing a statement?

I'm making a script that goes through a table that contains all the other table names on the database. As it parses each row, it checks to see if the table is empty by 

select count(*) cnt from $table_name

Some tables don't exist in the schema anymore and if I do that 

select count(*)

directly into the command prompt, it returns the error:

206: The specified table (adm_rpt_rec) is not in the database.

When I run it from inside Perl, it appends this to the beginning:

DBD::Informix::db prepare failed: SQL: -

How can I avoid the program quitting when it tries to prepare this SQL statement?

Upvotes: 1

Views: 2975

Answers (3)

Jonathan Leffler
Jonathan Leffler

Reputation: 753455

Working code - assuming you have a 'stores' database.

#!/bin/perl -w
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Informix:stores','','',
                       {RaiseError=>0,PrintError=>1}) or die;
$dbh->do("create temp table tlist(tname varchar(128) not null) with no log");
$dbh->do("insert into tlist values('systables')");
$dbh->do("insert into tlist values('syzygy')");

my $sth = $dbh->prepare("select tname from tlist");
$sth->execute;
while (my($tabname) =  $sth->fetchrow_array)
{
    my $sql = "select count(*) cnt from $tabname";
    my $st2 = $dbh->prepare($sql);
    if ($st2)
    {
        $st2->execute;
        if (my($num) = $st2->fetchrow_array)
        {
            print "$tabname: $num\n";
        }
        else
        {
            print "$tabname: error - missing?\n";
        }
    }
}
$sth->finish;
$dbh->disconnect;
print "Done - finished under control.\n";

Output from running the code above.

systables: 72
DBD::Informix::db prepare failed: SQL: -206: The specified table (syzygy) is not in the database.
ISAM: -111: ISAM error:  no record found. at xx.pl line 14.
Done - finished under control.

This printed the error (PrintError=>1), but continued. Change the 1 to 0 and no error appears. The parentheses in the declarations of $tabname and $num are crucial - array context vs scalar context.

Upvotes: 2

Chas. Owens
Chas. Owens

Reputation: 64909

Just put the calls that may fail in an eval block like this:

for my $table (@tables) {
    my $count;
    eval {
        ($count) = $dbi->selectrow_array("select count(*) from $table");
        1; #this is here so the block returns true if it succeeds
    } or do {
        warn $@;
        next;
    }
    print "$table has $count rows\n";
}

Although, in this case, since you are using Informix, you have a much better option: the system catalog tables. Informix keeps metadata like this in a set of system catalog tables. In this case you want systables:

my $sth = $dbh->prepare("select nrows from systables where tabname = ?");
for my $table (@tables) {
    $sth->execute($table);
    my ($count) = $sth->fetchrow_array;
    $sth->finish;
    unless (defined $count) {
        print "$table does not exist\n";
        next;
    }
    print "$table has $count rows\n";
}

This is faster and safer than count(*) against the table. Full documentation of the system catalog tables can be found in IBM Informix Guide to SQL (warning this is a PDF).

Upvotes: 3

Sinan Ünür
Sinan Ünür

Reputation: 118118

One option is not to use RaiseError => 1 when constructing $dbh. The other is to wrap the prepare in an eval block.

Upvotes: 3

Related Questions