sybperl-l Archive

From: "Jason L dot Froebe" <jason at froebe dot net>
Subject: perl DBI::Sybase - How to reconnect through err_handler
Date: Nov 7 2006 11:32PM

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_pe
+rl/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-t
+hread-multi/DBD/Sybase.pm line 94.
ERROR: unable to connect to SISDBA

Has anyone done this?

===============
#!/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: $msg\n";
        } else {
                print "MSG: $msg\n";
        }
}

sub syb_err_handler {
    my($err, $sev, $state, $line, $server, $proc, $msg, $sql, $err_typ
+e) = @_;

    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 : $msg\n";
    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;ch
+arset=iso_1", $SYBDBA_login, $SYBDBA_password, { PrintError => 0, Rai
+seError => 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();


Jason L. Froebe

He was the sort of person who stood on mountaintops during thunderstorms in wet copper armour shouting "All the Gods are bastards." --- Terry Pratchett 

WebBlog http://jfroebe.livejournal.com
Tech log http://www.froebe.net/blog

TeamSybase (http://www.teamsybase.com)
ISUG member (http://www.isug.com)
Chicago Sybase Tools User Group (http://www.cpbug.com)