# Maintaining SQLConnections

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[SQLConnectionOpenQ[conn],
If[(! SQLConnectionUsableQ[conn]), CloseSQLConnection[conn];
conn = OpenSQLConnection[conn];], conn = OpenSQLConnection[conn];],
conn = OpenSQLConnection["demo"];]


Question:

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

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
]

• +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. :) – gwr Mar 22 '18 at 10:09
• @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 :) – Kuba Mar 22 '18 at 10:12
• One has to be aware of SetAttributes adding to potentially existing list and Attributes resetting it. The devil is in the details as always. – gwr Mar 22 '18 at 10:17
• @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? – Ariel Sepulveda Jul 15 '18 at 21:49
• @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. – Kuba Jul 16 '18 at 5:20