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

Thanks Michael! :)

I'll open an Openclient feature request with Sybase to either:
1) allow reconnect from ct_callback()
2) add reconnect to ct_connect() with appropriate parameters (reconnect
delay, number of attempts, etc).

jason

--- [email protected] wrote:

> 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.
> 
> 


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)