How can I avoid exiting the program when the Perl DBI encounters an error while preparing a statement?
I am creating a script that goes through a table containing all the other table names in the database. When it parses each row, it checks if the table is not omitted with
select count(*) cnt from $table_name
Some tables no longer exist in the schema and if I do this
select count(*)
directly on the command line, it returns an error:
206: The specified table (adm_rpt_rec) is missing from the database.
When I run it from Perl, it adds it to the beginning:
DBD :: Informix :: db prepare failed: SQL: -
How can I avoid the program exiting when I try to prepare this SQL statement?
a source to share
Working code - if 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";
Derive from the above code.
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 an error ( PrintError=>1
) but continued. Change the value 1 to 0 and no error appears. The parentheses in the declarations $tabname
and $num
are critical - the array context and scalar context.
a source to share
Just place calls that might 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: system catalog tables. Informix stores 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";
}
It is faster and safer than count(*)
using a table. Complete documentation of the system catalog tables can be found in the IBM Informix Guide to SQL (warning PDF).
a source to share