check if DBI is still connected

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

Replies are listed 'Best First'.
Re: check if DBI is still connected by Zaxo (Archbishop) on Oct 06, 2005 at 08:39 UTC
You want the `ping` method of the connection handle, `if ( not $dbh->ping ) { $dbh = my_reconnect() }` [download] If you would like to make this automatic, with no connection made until the handle is used, and no other alteration to existing code, see my Lazy DBI Connection Handles. After Compline, Zaxo	[reply] [d/l]
Re^2: check if DBI is still connected by jamroll (Beadle) on Jun 23, 2017 at 18:17 UTC
yes...yes...but, what if i want to: `my $dbh = "123456"; # just as a test, instead of DBI->connect(....); if ($dbh != valid_DBI_handle()) { print "please supply a PROPER reference to a DBI object, or whatever + it's called\n"; } else { # okay, dbh actually points to a real db connection print "dbh is truly valid"; }` [download] because most posts about this say to simply `if (!$dbh) { die "no db connection!"; } else { $dbh->run_some_command(); }` [download] well, `if $dbh eq 1`, then we try to `run_some_command` will fail, and cause a bad user experience, to the point where the user's trust in the site dies along with the script!	[reply] [d/l] [select]
Re: check if DBI is still connected by EvanCarroll (Chaplain) on Oct 06, 2005 at 08:42 UTC
See options $dbi->{'RaiseError'} and $dbi->{'PrintError'}, querrying on a dead database will always throw an error or a warning respectively. If you set RaiseError to 1, and set PrintError to 0 (default), and capture the error message from $DBI::stderr after an eval, you can run a regex to see if it matches that of a failed connection, if it fails, reconnect and try again. `eval { $dbi = blah; $sth = blah }; if ($@ && $DBI::stderr =~ m/Bad Connection/) {reconnect and start again}` Evan Carroll www.EvanCarroll.com	[reply] [d/l]
Re: check if DBI is still connected by radiantmatrix (Parson) on Oct 06, 2005 at 16:02 UTC
Any of the above answers will suit you, but I found this solution to be useful for a similar task of mine, as it does most of the work for you: `our @connection = ($dsn, $user, $passwd, \%options); our $dbh = DBI->connect_cached( @connection ); sub do_query { my = shift; $dbh = DBI->connect_cached( @connection ); ## do query ## }` [download] `connect_cached()` connects if you aren't currently connected. Of course, you can do this yourself by using: `unless ($dbh->ping) { $dbh = DBI->connect(@connection); }` [download] But why? <-radiant.matrix-> A collection of thoughts and links from the minds of geeks The Code that can be seen is not the true Code "In any sufficiently large group of people, most are idiots" - Kaa's Law	[reply] [d/l] [select]
Re: check if DBI is still connected by critter (Acolyte) on Oct 06, 2005 at 09:37 UTC
You can check the state on the handle if you are using DBI. `$sth->state` [download] Grtz, Critter: A domestic animal or a non-predatory wild animal.	[reply] [d/l]
Re^2: check if DBI is still connected by gepebril69 (Scribe) on Apr 05, 2012 at 12:32 UTC
This option $sth->state don't work here. It always returns empty strings, once the variable has been used. using: strawberry 5.12.3.0 DBI 1.618	[reply]
Re^3: check if DBI is still connected by gepebril69 (Scribe) on Apr 05, 2012 at 12:46 UTC
`$dbh->ping` does work	[reply] [d/l]
Re: check if DBI is still connected by pajout (Curate) on Oct 06, 2005 at 08:57 UTC
You need not to develop own daemon, think about Apache::DBI, which _may_ help, or about SQL Relay, which is real connection pool daemon (but I have no practice with it :( )	[reply]
Re: check if DBI is still connected by marto (Cardinal) on Oct 06, 2005 at 08:43 UTC
Hi, Depending on your system setup you may want to look at Apache::DBI for persistent database connections. Have a read at the documentation, this module may be of use to you. Hope this helps. Martin	[reply]