Description: |
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 connect
+ion 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: 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: $msg\n";
} else {
print "MSG: $msg\n";
}
}
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=1
+0;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();
|