10
$\begingroup$
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?

$\endgroup$
1
  • $\begingroup$ SQLConnections[] // FullForm shows why the pattern matching fails. $\endgroup$ Sep 15, 2012 at 0:34

3 Answers 3

13
$\begingroup$

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]} *)
$\endgroup$
4
  • $\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$ Sep 15, 2012 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$ Oct 3, 2012 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, 2012 at 23:40
  • $\begingroup$ "SELECT 1" is a good idea. Thanks. $\endgroup$ Oct 4, 2012 at 20:32
7
$\begingroup$
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], __]]
$\endgroup$
3
  • 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$ Sep 15, 2012 at 0:53
  • $\begingroup$ @LeonidShifrin thanks. I hadn't previously seen that discussion you have linked to. $\endgroup$ Sep 15, 2012 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$ Sep 15, 2012 at 1:05
3
$\begingroup$

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

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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