How to determine connection state of Perl DBI database handler

Question

How to determine connection state of Perl DBI database handler(is connection opend)? Something like .NET SqlConnection.State == Open. May be something like

defined($dbh->do("some nop sql"))

but can't find sql nop statement to use.

Answer 1

The ping method - though what it does is going to be database driver dependent.

Answer 2

You can ask you database handle if it is connected by calling

$dbh->ping();

Some DB Drivers don't implement ping but DBD::mysql does. An alternative is to run an empty select like select 1 for MySQL. I'm assuming MySQL since that is how your question is tagged. Other databases will have slightly different answers.

Answer 3

There are two parts to this answer.

The first answer is the {Active} field. perldoc DBI says:

ATTRIBUTES COMMON TO ALL HANDLES

These attributes are common to all types of DBI handles. [...]

"Active" (boolean, read‐only)

The "Active" attribute is true if the handle object is "active". This is rarely used in applications. The exact meaning of active is somewhat vague at the moment. For a database handle it typically means that the handle is connected to a database ("$dbh−>disconnect" sets "Active" off).

That's probably what you want to check.

The second answer is that, while you can call ping(), or check the result of SELECT 1, there's not much point. That will indeed tell you if the database handle is connected at the time of that check. But what you really want to know is whether the database handle is connected when you do what you're about to do next, right? And there's always a chance that the connection will fail between your check and whatever it is you actually want to do. So a true result from either of those isn't a guarantee of anything.

If you're doing status monitoring, then a ping() or SELECT 1 will do fine. In an application, though, don't check a dbh's validity before doing something. Just connect, and use the dbh you get back, and do proper error-checking at every step. There's no substitute for correctly checking for errors.