|
|
sybperl-l Archive
Up Prev Next
From: "Jason L dot Froebe" <jason at froebe dot net>
Subject: Re: perl DBI::Sybase - How to reconnect through err_handler
Date: Nov 8 2006 1:32PM
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
--- michael.peppler@bnpparibas.com 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
> jason@froebe.net@peppler.org - 08.11.2006 00:32
>
> Sent by: owner-sybperl-l@peppler.org
> 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)
|
