Beefy Boxes and Bandwidth Generously Provided by pair Networks
The stupid question is the question not asked
 
PerlMonks  

How to reconnect a dead DBD::Sybase Connection

by jfroebe (Parson)
on Nov 08, 2006 at 20:11 UTC ( #582964=sourcecode: print w/ replies, xml ) Need Help??

Category: DBI Related
Author/Contact Info Jason L. Froebe
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();

Comment on How to reconnect a dead DBD::Sybase Connection
Download Code

Back to Code Catacombs

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: sourcecode [id://582964]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others surveying the Monastery: (6)
As of 2015-07-03 16:31 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (54 votes), past polls