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

My simple SQL (Sybase DB) 
$qstr = "EXEC sp_rename '$sybTable_tmp', '$sybTable'";
$syb_sth = $syb_dbh->do($qstr) or die $syb_dbh->errstr()";

[download]
upon successful completion, generates this to STDERR 
Object name has been changed.

[download]
Any ideas on how to stop this would be greatly appreciated.

Replies are listed 'Best First'.
Re: DBI, sp_rename, and STDERR
by jfroebe (Parson) on Jul 20, 2005 at 14:48 UTC

    Hi,

    Easily done but maybe not the most intuitive. You need to use an error handler for Sybase such as:

    error handler

    UPDATE:
    Derby caught an omission in my error handler. Namely, I missed copying the part which handles print statements. When a print statement occurs, the syb_err_handler() is called with $err being null. Thanks Derby for catching that!!

    ################################
##  Error handler function Called by DBD::Sybase if there is an error 
+or print statement  
################################
sub syb_err_handler {
    my ($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_ty
+pe) = @_;                                                            
+                                                       
    my $ERROR = 1;  
    my $WARNING = 0;
    my $return_code = $ERROR;
 if ( ($err == 131) && ($sev == 5) ) {
        ## Connection attempt failed
        return $ERROR;
    } elsif ( ($err == 0) && ($sev == 10) && ($state == 1) ) {
        if ( $msg =~ m/Active traceflags:/i ) {
            process_dbcc_list($msg);
            $return_code = $WARNING;
        } else {
            unless ( $msg =~ m/^\s+$/ ) {
                chomp $msg;
                print $msg . "\n";
            }
        }
        $return_code = $WARNING;
    } elsif ( ($err == 3) && ($sev == 5) ) {
        print "err: $msg\n";
        $return_code = $ERROR;
    } elsif ( !err ) {
        print "$msg\n";
        return $WARNING;
    }
    return ($return_code);
}

    [download]
    set error handler
    my $dbh = DBI->connect("dbi:Sybase:server=$SYB_SERVER;database=$SYB_DB
+", $SYB_UID, $SYB_PWD,
        { PrintError => 0, syb_err_handler => \&syb_err_handler } );

    [download]

    Jason L. Froebe

    Team Sybase member

    No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

[reply]
[d/l]
[select]

      Jason,
      Thanks for setting me straight. Just one other typo in your example: 

      syb_err_handler => \&syb_err_handler

      [download]

      Thanks!

      -derby
[reply]
[d/l]
        Thanks! :)

        Jason L. Froebe

        Team Sybase member

        No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

[reply]
Re: DBI, sp_rename, and STDERR
by mda2 (Hermit) on Jul 20, 2005 at 14:42 UTC
    Check your connect.

    PrintError parameter is default on DBI.

     $dbh = DBI->connect($DSN, $USER, $PASS, { PrintError => 0 } );

    [download]

    --
    Marco Antonio
    Rio-PM

[reply]
[d/l]

      That's not going to work. Setting PrintError to 0 just prevents perl from calling warn when errors are encountered. This isn't an error but a print statement in the sp_rename proc. Normally I handle this by nicely asking the stored proc dev to remove the offending print statement - something your DBA might frown upon when dealing with a system proc!

      Update: Kudos to Jason for the correct answer.

      -derby
[reply]

        This is a perfectly acceptable method unless we call system stored procedures (i.e. sp_rename) or where the source of the stored procedure is not available (3rd party). In truth, only using an error handler will 'catch' the print statement output from being sent to STDERR.

        Jason L. Froebe

        Team Sybase member

        No one has seen what you have seen, and until that happens, we're all going to think that you're nuts. - Jack O'Neil, Stargate SG-1

[reply]

Back to Seekers of Perl Wisdom