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?

  • $\begingroup$ SQLConnections[] // FullForm shows why the pattern matching fails. $\endgroup$ – 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[...], 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:

  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:


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

(* {SQLConnection["db", 3, "Open", "Catalog" -> "db", "ReadOnly" -> True]} *)
  • $\begingroup$ +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. $\endgroup$ – Leonid Shifrin Sep 15 '12 at 18:10
  • $\begingroup$ 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". $\endgroup$ – Gustavo Delfino Oct 3 '12 at 22:22
  • 3
    $\begingroup$ @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). $\endgroup$ – WReach Oct 3 '12 at 23:40
  • $\begingroup$ "SELECT 1" is a good idea. Thanks. $\endgroup$ – Gustavo Delfino Oct 4 '12 at 20:32

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):


(* 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:


(* 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], __]]
  • 2
    $\begingroup$ 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). $\endgroup$ – Leonid Shifrin Sep 15 '12 at 0:53
  • $\begingroup$ @LeonidShifrin thanks. I hadn't previously seen that discussion you have linked to. $\endgroup$ – Mike Honeychurch Sep 15 '12 at 1:03
  • $\begingroup$ 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. $\endgroup$ – 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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.