How to check if a database connection is open?

Question

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?

Answer 1

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]} *)

Answer 2

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], __]]

Answer 3

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