How to check if a database connection is open?

In[32]:= SQLConnections[]

Out[32]= {SQLConnection["db", 1, "Closed", "<>"],
SQLConnection["db", 2, "Closed", "<>"],
SQLConnection["db", 3, "Open", "Catalog" -> "db",

In[33]:= Cases[SQLConnections[], SQLConnection[__, "Open", __]]

Out[33]= {}


doesn't work. Why it doesn't match the one open connection expression?

-
SQLConnections[] // FullForm shows why the pattern matching fails. –  Mike Honeychurch Sep 15 '12 at 0:34

As @MikeHoneychurch observes, the formatted form of an SQLConnection expression:

SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True]


differs from its FullForm:

SQLConnection[JDBC[...], JLinkObjectsvm1JavaObject18126325894086657, 1, ...]


Pattern matching uses the FullForm.

One way to work around this is to convert the expression into its formatted form prior to pattern-matching:

Cases[
Format[#] /. FormatValues[SQLConnection] & /@ SQLConnections[]
, SQLConnection[__, "Open", __]
]

(* {SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True]} *)


If one has no objection to exploiting knowledge of the internal representation of an SQLConnection, then another option is to test the JDBC connection object directly:

Needs["JLink"]

Cases[
SQLConnections[]
, SQLConnection[_, c_, ___] /; JavaObjectQ[c] && c =!= Null && !c@isClosed[]
]

(* {SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True]} *)

-
+1, converting to formatted form is clever. Also, testing for being a Java object reference is clearly better than testing for a symbol being removed, since the latter is a deeper level implementation detail than the former. –  Leonid Shifrin Sep 15 '12 at 18:10
Great, but.... If I kill Mathematica's database connection in the database server and apply the JLink answer, it is still reported as open even though it is broken. If I try to use it I get: "com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure". –  Gustavo Delfino Oct 3 '12 at 22:22
@GustavoDelfino I'm afraid DatabaseLink is no different from most other database client protocols: an "open" connection does not imply a "usable" connection. The only way to know whether a database connection is still alive (as opposed to merely open) is to attempt to perform an operation on it. The common practice is to attempt a trivial query like SELECT 1 (or SELECT 1 FROM DUAL in Oraclespeak). –  WReach Oct 3 '12 at 23:40
"SELECT 1" is a good idea. Thanks. –  Gustavo Delfino Oct 4 '12 at 20:32
Needs["DatabaseLink"]

DatabaseLinkJDBC[ "MySQL(Connector/J)",

(* SQLConnection[1, "Open", "Catalog" -> "railfreight",


But when you check out the FullForm (removed password info):

FullForm[conn]

(* SQLConnection[JDBC["MySQL(Connector/J)","localhost:3306/railfreight"],JLinkObjectsvm1JavaObject121074792529921,2,Rule["Catalog",Automatic],Rule["Description",None],Rule["Location",None],Rule["Name",None],Rule["Password",""],Rule["Properties",List[]],Rule["ReadOnly",Automatic],Rule["RelativePath",False],Rule["TransactionIsolationLevel",Automatic],Rule["UseConnectionPool",Automatic],Rule["Username",""],Rule["Version",None]] *)


Similarly when you close the connection:

CloseSQLConnection[conn]
FullForm[conn]



So based on that I think this is worth a try:

Cases[SQLConnections[], SQLConnection[_, Except[_Removed], __]]

-
The problem is that the head Removed is not really a normal head, see e.g. here. In particular, direct pattern-matching attempts with Removed don't work. So, good idea, but I guess the realization should be more tricky (no time now to try). –  Leonid Shifrin Sep 15 '12 at 0:53
@LeonidShifrin thanks. I hadn't previously seen that discussion you have linked to. –  Mike Honeychurch Sep 15 '12 at 1:03
Actually, one can use functions similar to removedNames of that linked answer, to check whether or not the symbols has been removed. Something like SetAttributes[removedQ, HoldAll];removedQ[s_Symbol] := StringMatchQ[ToString[Unevaluated@s], "Removed[" ~~ __ ~~ "]"], and then test as Cases[SQLConnections[], SQLConnection[_, Except[_?removedQ], __]]. I think this should work. In any case, +1 for finding the right direction. –  Leonid Shifrin Sep 15 '12 at 1:05

I just checked the documentation in V10 and accidentally stumbled upon a built in command: SQLConnectionOpenQ[conn] This seems to do the trick.

-