> -----Original Message-----
> From: Bakken, Luke [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, October 05, 2005 4:03 PM
> To: Ryan Frantz
> Subject: RE: Missing objects in Database
>
> Ryan Frantz wrote:
> > 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:
> >
> >
>
> Try this and let me know if it traps your error OK:
>
> use warnings;
> use strict;
> use DBI;
>
> my $dataSource = "WH_PROD";
> my $dbh = DBI->connect("DBI:ODBC:$dataSource");
> die "Unable to connect to $dataSource: $DBI::errstr\n" unless (defined
> $dbh);
> 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;
> push @tables, $tableName;
> }
>
> for my $tbl ( @tables )
> {
> my $sth_space = $dbh->prepare("exec sp_spaceused $tbl")
> or die "Unable to prepare statement: $dbh->errstr\n";
> # trap the potential error
> eval {
> $sth_space->execute;
> };
Negative; this did not work. I've been reviewing the DBI documentation
and it states that execute() returns undef if an error occurs. So I
tried this:
...
my $rv = $sth_space->execute;
next unless $rv;
...
But that does not work. Then I saw that for non-SELECT statements
(which I assume an EXEC is not; I'm new to DBs), that it will return
"OEO" if nothing was affected. I think, though, that this refers more
to INSERT and its ilk.
Should I set RaiseError to 1?
$dbh->{RaiseError} = 1;
Will that help me skip over errors? Or just give me better output when
I do encounter one? I'm going to monkey around with it.
> if ($@)
> {
> # print error
> print STDERR "[EMAIL PROTECTED]";
> }
> else
> {
> while ( my $ra_row = $sth_space->fetchrow_arrayref )
> {
> my ( $tableName, $numRows, $reservedSpace,
> $dataSize, $indexSize, $unusedSpace ) = @$ra_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;
--
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
<http://learn.perl.org/> <http://learn.perl.org/first-response>
