Home » Databases » Sybase » ASE » DBD::Sybase – reconnect through err_handler (feature request)

DBD::Sybase – reconnect through err_handler (feature request)

I received an email from Michael Peppler, the creator/maintainer of DBD::Sybase. In it, he says that this is a limitation of Sybase’s OpenClient CT-Library not DBD::Sybase.

The error handler is called by Sybase’s ct_callback() function. Sybase has restricted what CT-Library calls that can be made. Apparently while ct-connect() can be called, memory allocation of the connection will be disallowed.

I’m opening a number of feature requests with Sybase to make reconnecting, when the connection dies, easier to do.

I’m writing an application with a business requirement of reconnecting a dead db connection to Sybase through the syb_err_handler() error handler routine.

In the past, I would just run $dbh->DBDEAD() just prior to running the queries. If the connection was dead, I would just reconnect and run the query (plus any initialization db routines).

The syb_err_hander() can easily catch a dead connection but calling $dbh->connect() in the error handler fails

./test_sybase 
ERROR: Connection dropped ct_config(CS_SET, CS_LOGIN_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_config(CS_SET, CS_TIMEOUT) failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ct_con_alloc failed at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/Sybase.pm line 94. 
ERROR: unable to connect to SISDBA 

Has anyone done this? I’m starting to think that this can’t be done through the error handler of DBD::Sybase

#!/usr/bin/perl
use strict;
use warnings;
use lib "/home/jfroebe/lib";
use DBI;
use File::Basename;
our $SYBDBA_login = ‘login’;
our $SYBDBA_password = ‘password’;
our $dbh = connect_dbms();
sub report_err {
        my $msg = shift;
        my $type = shift;
        if ($type eq ‘error’) {
          print "ERROR: $msgn";
        } else {
          print "MSG: $msgn";
        }
}
sub syb_err_handler {
    my($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_type) = @_;
    my %ignore_errors = map { $_, 1 } (6, 183, 208, 2056, 2057, 14024, 17001);
    if ($err == 151 || $err == 50 || $err == 60) {
       report_err("Connection dropped", "error");
       for (my $attempt = 0; $attempt < 10; $attempt++) {
          if ($dbh = connect_dbms()) {
             report_err("Reconnected", "error");
             return 0;
          }
          sleep 2;
       }
       return 1;
    } elsif ($err == 63 || ($err == 4 && $sev == 5) ) {
        report_err("Connection timed out", "error");
        return 1;
    } elsif ($ignore_errors{$err}) {
        # ignore
        return 0;
    }
    print "error: $err, $sev, $state : $msgn";
    return 1;
}
sub connect_dbms {
    my $script = basename($0);
    if ($dbh = DBI->connect("dbi:Sybase:server=SISDBA;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1", $SYBDBA_login, $SYBDBA_password, { PrintError => 0, RaiseError => 0, syb_err_handler => &syb_err_handler } )) {        return $dbh;    }
    report_err("unable to connect to SISDBA", "error");
    return;
}
sub syb_loop {
        my $query = "exec sp_helpdb";
        for (my $i = 0; $i < 10000; $i++) {
                my $array_ref = $dbh->selectall_arrayref($query);
        }
}
syb_loop();
Share Button

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect