Re: perl DBI::Sybase - How to reconnect through err_handler

The error handler is called from within the server callback (set with 
ct_callback()). There are only a few CTlib calls that are available in 
that situation, and I'm pretty sure that reconnecting is not feasible. The 
alternative is the good old "set a flag in the handler, and check the flag 
in the main code" procedure. Annoying, I know, but I don't see any way to 
directly generate a reconnect from the error handler...

Michael




Internet
[email protected]@peppler.org - 08.11.2006 00:32
 
Sent by:        [email protected]
To:     sybperl-l
cc: 
Subject:        perl DBI::Sybase - How to reconnect through err_handler


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)


This message and any attachments (the "message") is
intended solely for the addressees and is confidential. 
If you receive this message in error, please delete it and 
immediately notify the sender. Any use not in accord with 
its purpose, any dissemination or disclosure, either whole 
or partial, is prohibited except formal approval. The internet
can not guarantee the integrity of this message. 
BNP PARIBAS (and its subsidiaries) shall (will) not 
therefore be liable for the message if modified. 

                ---------------------------------------------

Ce message et toutes les pieces jointes (ci-apres le 
"message") sont etablis a l'intention exclusive de ses 
destinataires et sont confidentiels. Si vous recevez ce 
message par erreur, merci de le detruire et d'en avertir 
immediatement l'expediteur. Toute utilisation de ce 
message non conforme a sa destination, toute diffusion 
ou toute publication, totale ou partielle, est interdite, sauf 
autorisation expresse. L'internet ne permettant pas 
d'assurer l'integrite de ce message, BNP PARIBAS (et ses
filiales) decline(nt) toute responsabilite au titre de ce 
message, dans l'hypothese ou il aurait ete modifie.