DBI: Missing objects in Database

[Ryan Frantz]

Perlers,

 

ActiveState Perl 5.8.2

MS SQL Server 2000 SP3

 

I'm working on a script that finds tables in a database and then prints
out the results of a stored procedure run against each of them.  There
are some tables that the DBD::ODBC driver complains don't exist.  They
do, but they're temporary and inconsequential.  I need to be able to
skip past them when I encounter the error but I'm not sure how to do
that.  Here's my code:

 

use warnings;

use strict;

 

use DBI;

# We need the DBD::ODBC driver...

 

# specify my data source; we're dependent on a user- or system-DSN, for
now...

my $dataSource = "WH_PROD";

 

my $dbh = DBI->connect("DBI:ODBC:$dataSource");

die "Unable to connect to $dataSource: $DBI::errstr\n" unless (defined
$dbh);

 

# find tables

my $sth_tables = $dbh->table_info( '', '', '%', 'TABLE' )

  or die "Unable to prepare statement: $dbh->errstr\n";

 

my @tables;

while ( my @row = $sth_tables->fetchrow_array ) {

  my ( $database, $owner, $tableName, $tableType ) = @row;

  #print "$tableName\n";

  push @tables, $tableName;

}

 

# find the amount of space used by each table

foreach ( @tables ) {

  my $sth_space = $dbh->prepare("exec sp_spaceused $_")

    or die "Unable to prepare statement: $dbh->errstr\n";

  print "\n";

  $sth_space->execute;

 

  while ( my @row = $sth_space->fetchrow_array ) {

    my ( $tableName, $numRows, $reservedSpace, $dataSize, $indexSize,
$unusedSpace ) = @row;

    print "Table:\t\t$tableName\n";

    print "Number of rows: $numRows\n";

    print "Reserved Space: $reservedSpace\n";

    print "Data Size:\t$dataSize\n";

    print "Index Size:\t$indexSize\n";

    print "Unused Space:\t$unusedSpace\n";

  }

}

 

$dbh->disconnect;

 

I get the following example error on "missing" tables:

 

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]The object 'ExportTemp' does not exist in database 'WH_PROD'.
(SQL-42000)(DBD: st_execute/SQLExecute err=-1) at sp.pl line 32.

 

I tried the following:

 

my $good = ( $sth_space->execute );

if ( $good ) {

  ...

}

 

But Perl complained thusly:

 

Can't call method "fetchrow_array" without a package or object reference
at sp.pl line 35.

 

Since I'm still studying references, I'm not sure how to proceed.
What's the best way to check the return of that method?

 

ry