Home » Databases » Sybase » ASE » How to reconnect a dead DBD::Sybase Connection

How to reconnect a dead DBD::Sybase Connection

It is important to know that this is just one method of reconnecting a dead connection.  Note that we are handling the errors manually for the individual query.  It wouldn’t take much to create a db_exec subroutine so we just call the db_exec() subroutine and just worry about the reconnect in one place.

Notice that because of DBD::Sybase bug # 616, the error 151 will be printed to STDERR.

./test_sybase
OpenClient message: LAYER = (1) ORIGIN = (1) SEVERITY = (1) NUMBER = (151)
Message String: ct_cancel(): user api layer: external error: A connection to the server must exist on the connection structure before this routine can be called.
ERROR: Connection to DBMS died
syb_db_disconnect(): ct_close() failed
MSG: ERROR: Reconnected

#!/usr/bin/perl

use strict;
use warnings;

use lib “/home/jfroebe/lib”;

use DBI;
use File::Basename;

our $SYBDBA1_login = ‘login’;
our $SYBDBA1_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 exit_on_error {
my $msg = shift;

report_err($msg, “error”);
return -1;
}

sub connect_dbms {
my $loc_dbh;
my $script = basename($0);

if ($loc_dbh = DBI->connect(“dbi:Sybase:server=SISDBA1;loginTimeout=10;timeout=30;scriptName=$script;encryptPassword=1;tdsLevel=CS_TDS_50;charset=iso_1”, $SYBDBA1_login, $SYBDBA1_password, { PrintError => 0, RaiseError => 1 } )) {
return $loc_dbh;
}

report_err(“unable to connect to SISDBA1”, “error”);
return;
}

sub syb_loop {
my $query = “exec sp_helpdb”;

RETRY: for (my $i = 0; $i < 10000; $i++) { my $array_ref; eval { $array_ref = $dbh->selectall_arrayref($query);
};

if ($@) {
if ($dbh->err == 151 || $dbh->err == 60) {
report_err(“Connection to DBMS died”, “error”);
undef($dbh);

for (my $i = 0; $i < 10; $i++) { if( $dbh = connect_dbms() ) { report_err("Reconnected", "msg"); redo RETRY; } sleep 3; } exit_on_error("Unable to reconnect after $i attempts!"); } } } } syb_loop(); [/perl]

Share Button

Leave a Reply

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

*
*

Facebook login by WP-FB-AutoConnect