Handling error in database connection (TimeConstrained)

I have a lot of scripts that use a database connection, and I have realized that my code should be protected against errors in my connection. For example, when my database is out for some technical reason.

I tried to use this in a fake broke connection:

sqlConn:=OpenSQLConnection[JDBC["Microsoft SQL Server(jTDS)","187.111.111.111"],"Username"->"myUser","Password"->"myPass"];
(conn = TimeConstrained[sqlConn, 1]) // AbsoluteTiming


But without success as you can see. The 1 second argument is not respected. I get this:

"JDBC::error: Network error IOException: Operation timed out >>"
{76.518145, $Aborted}  Wolfram technical support asked me to try changing $SQLTimeout, but that did not work either.

I tried before the last code:

$SQLTimeout=1  and the time restriction is also not respected. They then told me: "Most of the timeout functions in Mathematica takes into account only the CPU time spent inside the main Mathematica kernel process; it does not include additional threads or processes. And the time OpenSQLConnection spend is mostly on it's own thread." Some clue on how to deal with this? - What about throwing in a mention of TimeConstrained in the question title? – Yves Klett Jan 28 '13 at 13:11 AFAIK the $SQLTimeout variable is there exactly for that purpose. Can you explain what you have tried and why you think it does not work? – Albert Retey Jan 28 '13 at 13:46
@AlbertRetey Hi. Tks, I have made some changes to make it clearer in this topic. – Murta Jan 28 '13 at 14:31
@YvesKlett tks. Done! – Murta Jan 28 '13 at 14:33
documentation of $SQLTimeout is vague, but could be understand as if $SQLTimeout doesn't work for initiating the connection but only for queries. Have you tried that? Maybe this question is also of interest. As mentioned there you might need to change some settings for the network connection on the client or for the connection settings on the sql server. Another possibility would be to build something with the v9 asynchronouse tasks, but I haven't tried that... – Albert Retey Jan 28 '13 at 15:00

I was able to reproduce your behaviour on Mathematica V9 64-bit under Windows 7. Neither TimeConstrained nor $SQLTimeout would work. However, an explicit "Timeout" option worked for me: OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)", "187.111.111.111/mydb"] , "Username" -> "myUser" , "Password" -> "myPass" , "Timeout" -> 1 ]  The evaluation stopped with the message JDBC::error: Login timed out. after one second. Interestingly, this expression apparently made a persistent change to the DatabaseLink state because thereafter any attempt to open the database would time out after one second -- even if I did not specify the "Timeout" option! If I subsequently tried a longer explicit timeout setting, that longer value would then "stick" and be applied to all future connection attempts. It would seem that we are looking at a bug here. I cannot say that I am surprised however. The handling of timeouts and early cancellation of SQL transactions is notoriously unreliable in many, if not most, SQL software stacks. In our context here, we are doubly removed from SQL Server by both JDBC and DatabaseLink. Incidentally, it also does not surprise me that TimeConstrained fails here. In practice, I find that TimeConstrained sometimes has difficulty interrupting a process that engages in a blocking I/O operation (and the front-end does too). Update Further investigation reveals that DatabaseLink is using the Java class java.sql.DriverManager to allocate non-pooled SQL connections. Furthermore, it is setting the loginTimeout property of this class in a persistent fashion. Therefore, we can adjust that property ourselves without actually attempting to open a connection thus: Needs["JLink"] LoadJavaClass["java.sql.DriverManager"]; javasqlDriverManagersetLoginTimeout[2]  We can query the current setting like this: javasqlDriverManagergetLoginTimeout[]  The same investigation also revealed that OpenSQLConnection only uses the $SQLTimeout global variable if we pass an invalid value for the "Timeout" option:

Block[{$SQLTimeout = 1} , OpenSQLConnection[ JDBC["Microsoft SQL Server(jTDS)","187.111.111.111/mydb"] , "Username" -> "myUser" , "Password" -> "myPass" , "Timeout" -> "invalid" ] ]  We are even informed that the default will be used: OpenSQLConnection::timeout: Illegal value for Timeout option: invalid (continuing with default value) All this, and more, can be found if one studies the files found in: FileNameJoin[{$InstallationDirectory, "SystemFiles", "Links", "DatabaseLink"}] //
SystemOpen

-
Tks a lot, nice answer! You beat Wolfram Suport. +1. Do you know how can I set back this parameter without make a new call? What is the default value? This persistent state is really annoying. – Murta Jan 28 '13 at 16:59
@Murta Yes, we can reset the parameter without opening a new connection -- see my update. – WReach Jan 28 '13 at 17:36
@WReach Thank you for this quite descent investigative reverse engineering. Thank you! – Stefan Jan 28 '13 at 17:53

In Mathematica V10, the jtds driver was updated to version 1.3.1.

Now the command:

OpenSQLConnection[
JDBC["Microsoft SQL Server(jTDS)","187.111.111.111/mydb"]
`