hiyall has asked for the wisdom of the Perl Monks concerning the following question:

I expected to see: msg 3631 (from 12.5.1) Total actual I/O cost for this command: %ld. output somewhere from following code - but it wasn't Any hint on how to get it out?
#!/usr/bin/perl use Sybase::DBlib; dbmsghandle(\&showplan_handler); # $dbh = DBI->connect(DBI:Sybase:v6_dba_test_nj2x, 'user', 'pw'); $dbh = new Sybase::DBlib 'user','password'; $dbh->dbsetopt(DBSHOWPLAN); $dbh->dbsetopt(DBSTAT, "IO"); $dbh->dbsetopt(DBSTAT, "TIME"); $dbh->dbcmd('use myDB'); $dbh->dbsqlexec; $dbh->dbcmd('set statistics io,time,subquerycache on'); $dbh->dbsqlexec; $dbh->dbcmd('sp_who sa'); $dbh->dbsqlexec; while($dbh->dbresults != NO_MORE_RESULTS) { while(@dat = $dbh->dbnextrow) { print "@dat\n"; } } # Message number 3612-3631 are statistics time / statistics io # message. Showplan messages are numbered 6201-6225. # (I hope I haven't forgotten any...) @sh_msgs = (3612 .. 4043, 6201 .. 6225); @showplan_msg{@sh_msgs} = (1) x scalar(@sh_msgs); sub showplan_handler { my ($db, $message, $state, $severity, $text, $server, $procedure, $line) = @_; # Don't display 'informational' messages: if ($severity > 0) { print STDERR ("Sybase message ", $message, ", Severity ", $severity, ", state ", $state); print STDERR ("\nServer `", $server, "'") if defined ($ser +ver); print STDERR ("\nProcedure `", $procedure, "'") if defined ($procedure); print STDERR ("\nLine ", $line) if defined ($line); print STDERR ("\n ", $text, "\n\n"); } elsif($showplan_msg{$message}) { # This is a SHOWPLAN or STATISTICS message, so print it out: print STDERR ($text, "\n"); } elsif ($message == 0) { print STDERR ($text, "\n"); } 0; }

Replies are listed 'Best First'.
Re: DBI Issue with Sybase 15.5
by JavaFan (Canon) on Feb 13, 2012 at 21:15 UTC
    It's more than a dozen years ago since I last used Sybase, so my remark may be way off, but why are you expecting anything from your showplan_handler? You aren't doing any query, so there's no query plan to be made.

    What happens if you add a

    $dbh->dbcmd("SELECT * FROM SOME_TABLE"); $dbh->dbsqlexec;
    to your program?
      The showplan handler also handles the statistics msgs re: msg 3631 which is the line reporting actual i/o. However, the other statistics msgs are output, just not the one that I want msg 3631. I do execute sql via the program Note the statement: $dbh->dbcmd('sp_who sa'); This is a stored procedure call which contains multiple sql statements. Each sql within the stored procedure will result in a statistics report output due to the "set statistics ..." statement executed earlier in the program. I have tried a different approach which seems to work now ... after I posted this. I will post the rewritten code as a reply. I am still curious why this approach fails to output the msg 3631 text and values.
        Are you sure it's a Perl or DBI issue? If you write the equivalent program in C, do you then get the expected callback?