How to check if a database connection is open?

10

6

In[32]:= SQLConnections[]

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

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

Out[33]= {}

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

qazwsx

Posted 2012-09-14T23:53:51.980

Reputation: 8 134

SQLConnections[] // FullForm shows why the pattern matching fails. – Mike Honeychurch – 2012-09-15T00:34:20.603

Answers

13

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

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

differs from its FullForm:

SQLConnection[JDBC[...], JLink`Objects`vm1`JavaObject18126325894086657, 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]} *)

WReach

Posted 2012-09-14T23:53:51.980

Reputation: 62 787

+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 – 2012-09-15T18:10:11.293

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 – 2012-10-03T22:22:02.247

3@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 – 2012-10-03T23:40:51.927

"SELECT 1" is a good idea. Thanks. – Gustavo Delfino – 2012-10-04T20:32:01.770

7

Needs["DatabaseLink`"]

conn = DatabaseLink`OpenSQLConnection[ 
   DatabaseLink`JDBC[ "MySQL(Connector/J)", 
    "localhost:3306/railfreight"], "Username" -> "", 
   "Password" -> ""]

(* SQLConnection[1, "Open", "Catalog" -> "railfreight", 
 "TransactionIsolationLevel" -> "RepeatableRead"]*)

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

FullForm[conn]

(* SQLConnection[JDBC["MySQL(Connector/J)","localhost:3306/railfreight"],JLink`Objects`vm1`JavaObject121074792529921,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]

(* SQLConnection[JDBC["MySQL(Connector/J)","localhost:3306/railfreight"],Removed["JavaObject341542795476993"],1,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]] *)

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

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

Mike Honeychurch

Posted 2012-09-14T23:53:51.980

Reputation: 36 211

2

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 – 2012-09-15T00:53:07.987

@LeonidShifrin thanks. I hadn't previously seen that discussion you have linked to. – Mike Honeychurch – 2012-09-15T01:03:15.847

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 – 2012-09-15T01:05:20.060

3

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

BlueMac

Posted 2012-09-14T23:53:51.980

Reputation: 701