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[(! 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


Posted 2018-03-22T08:12:55.680

Reputation: 870



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


Posted 2018-03-22T08:12:55.680

Reputation: 129 207

2+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 – 2018-03-22T10:09:51.280

2@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 – 2018-03-22T10:12:34.383

1One has to be aware of SetAttributes adding to potentially existing list and Attributes resetting it. The devil is in the details as always. – gwr – 2018-03-22T10:17:36.363

@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 – 2018-07-15T21:49:53.080

@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 – 2018-07-16T05:20:45.177

@Kuba Your answer is fine but the question is confusing. The function maintainConnection does not ensure a continuous working connection. Indeed, in some situations (e.g. DB server down) it is impossible to ensure a working DB connection. – Ariel Sepulveda – 2018-07-16T11:55:58.677

@ArielSepulveda after a second look I'm not sure if any changes are needed, except maybe a missing usage message to clarify it. This function does not seem to be a quick check but rather a 'force connection or fail'. Notice that SQLConnectionOpenQ is not used to check wheter the connection is ok but as a lightweight check if it is not ok, if it is not, Open- is called, otherwise -UsableQ is called in the same manner. So the conn is left only when all checks pass. If any of them fails Open- will fail too when the DB server is down and the function returns $Failed at the end. – Kuba – 2018-07-16T12:05:34.890

@Kuba You are right. Maybe the function name is the main problem as there’s no way to maintain the connection in all situations. Renaming to checkDBConnection may be more appropriate. – Ariel Sepulveda – 2018-07-16T12:30:47.647

@ArielSepulveda I renamed it to forceConnection, there should be no doubt now. – Kuba – 2018-07-16T12:40:06.637