I'm using SQLConnection to database in repetitive ScheduledTask. To prevent creation of new connections, and to maintain binding of connection to same symbol I made the following:

If[MatchQ[conn, _SQLConnection],
  If[(! SQLConnectionUsableQ[conn]), CloseSQLConnection[conn]; 
   conn = OpenSQLConnection[conn];], conn = OpenSQLConnection[conn];],
  conn = OpenSQLConnection["demo"];]


How to make the same procedure, but with usage of conn symbol only on one place, NOT as above with conn on 9 places?

EDIT: additionally to this question and to the answer below given by the Kuba, emerged new problem and question posted here SQLConnection causes kernel hangs


1 Answer 1


Moving my comments into an answer:

If you create forceConnection[conn_]:=If[.. then you don't have to worry about replacing conn inside in future. You need to add HoldFirst attribute to forceConnection if you want to modify its value.

Additionally, I don't like nested Ifs:

forceConnection // Attributes = {HoldFirst};

forceConnection[conn_] := conn = Which[
  ! MatchQ[conn, _SQLConnection], OpenSQLConnection["demo"]
, ! SQLConnectionOpenQ[conn],     OpenSQLConnection[conn]
, ! SQLConnectionUsableQ[conn],   CloseSQLConnection[conn]; OpenSQLConnection[conn]
, True,                           conn
  • 2
    $\begingroup$ +1 And gosh, working the WL all this time and I was not aware that one can use postfix notation (aka reverse Polish notation) for setting Attributes in this way. :) $\endgroup$
    – gwr
    Commented Mar 22, 2018 at 10:09
  • 2
    $\begingroup$ @gwr yes, I like to start 'definition' lines with the symbol name. You can also do symbol ~ SetAttributes ~ attr but I don't like it :) $\endgroup$
    – Kuba
    Commented Mar 22, 2018 at 10:12
  • 1
    $\begingroup$ One has to be aware of SetAttributes adding to potentially existing list and Attributes resetting it. The devil is in the details as always. $\endgroup$
    – gwr
    Commented Mar 22, 2018 at 10:17
  • $\begingroup$ @Kuba I think that the second test in your function is misleading. The documentation states that "SQLConnectionOpenQ is strictly a client-side test and initiates no communication with the connection's target server, local or remote." So, SQLConnectionOpenQ is not enough to test if the connection is really working. SQLConnectionUsableQ is much slower but in my opinion has to be part of the test. One more thing, how do you know if OpenSQLConnection[conn] returns a valid connection? $\endgroup$ Commented Jul 15, 2018 at 21:49
  • $\begingroup$ @ArielSepulveda Feel free to add an appendix or a separate answer. Your notes make sense but I have no experience with SQLConnection and the answer here addresses a specific idiom and the final function is only a cleaner version of OP's logic. $\endgroup$
    – Kuba
    Commented Jul 16, 2018 at 5:20

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.