How to check the $dbh's status

pysome has asked for the wisdom of the Perl Monks concerning the following question:

Hi
I am coding a perl program to become a system daemon. The code just like :

$dbh=DBI->connect() ....
while(1) {
   do_something();
}
[download]

In the sub do_stomthing(),i execute some SQL operation.But how to check the $dbh's status in do_something()?
I means i will re-connect the Database if the database were down.My idea is to to something in do_something() like this :

sub do_something() {
  if($dbh's status is false) {
    $dbh=DBI->connect()....
  }
}
[download]

My aim is only to reduce the spending of DB connection.

Any help will appreciated!
Thanks

Comment on How to check the $dbh's status Select or Download Code

Replies are listed 'Best First'.
Re: How to check the $dbh's status by bart (Canon) on Jul 18, 2007 at 10:27 UTC
To check a connection, take a look at the method ping. I think that is what you're after. In case that doesn't work for your database, for example because the driver doesn't actually implements that method, you can always fall back on trying to execute your query and see if it bombs out, catch it with eval if necessary, and if it fails, reconnect and try again.	[reply]
Re: How to check the $dbh's status by perrin (Chancellor) on Jul 18, 2007 at 15:10 UTC
Just call connect_cached every time. It will take care of reconnecting if the connection has gone bad.	[reply]
Re^2: How to check the $dbh's status by pysome (Scribe) on Jul 19, 2007 at 05:26 UTC
can i use $dbh->{Active} to catch the connection status?	[reply]
Re^3: How to check the $dbh's status by perrin (Chancellor) on Jul 19, 2007 at 14:39 UTC
Well, connect_cached does more than that. It pings the connection as well. Don't waste your time rewriting connect_cached -- just call it.	[reply]
Re^4: How to check the $dbh's status by pysome (Scribe) on Jul 20, 2007 at 02:09 UTC
Re: How to check the $dbh's status by Akoya (Scribe) on Jul 18, 2007 at 14:32 UTC
If the ping method is not available, you could try something like this: `my $sth = $dbh->prepare('SELECT * FROM dual'); my $rows = $sth->execute(); if ($rows != 1) { # try to reconnect }` [download] Selecting from dual always retrieves a single record. Caveat: I use Oracle. I'm not sure if dual is implemented on other database engines. --Akoya	[reply] [d/l]
Re: How to check the $dbh's status by sago (Scribe) on Jul 19, 2007 at 10:08 UTC
#Connection to the database my $user="test_user"; my $pw="pass123"; print "starting the process"; $dbh = DBI->connect('DBI:ODBC:TESTSERVER',$user,$pw); if(!$dbh){ print "Unable to connect to the database testserver\n"; exit(0); } else{ print "connected \n"; } #Interacting with the database $sth=$dbh->prepare("select * from table1") \|\| die "prepare failed"; $sth->execute() \|\| die "execute failed"; while (my @rows= $sth->fetchrow_array) { print "@rows"; } #Disconnecting from the database $dbh->disconnect;	[reply]