3
$\begingroup$

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?

Improve this question
$\endgroup$
5
  • 1
    $\begingroup$ What about throwing in a mention of TimeConstrained in the question title? $\endgroup$ Commented Jan 28, 2013 at 13:11
  • 2
    $\begingroup$ 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? $\endgroup$ Commented Jan 28, 2013 at 13:46
  • $\begingroup$ @AlbertRetey Hi. Tks, I have made some changes to make it clearer in this topic. $\endgroup$ Commented Jan 28, 2013 at 14:31
  • $\begingroup$ @YvesKlett tks. Done! $\endgroup$ Commented Jan 28, 2013 at 14:33
  • $\begingroup$ 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... $\endgroup$ Commented Jan 28, 2013 at 15:00

2 Answers 2

Reset to default
6
$\begingroup$

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"];

java`sql`DriverManager`setLoginTimeout[2]

We can query the current setting like this:

java`sql`DriverManager`getLoginTimeout[]

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
Improve this answer
$\endgroup$
3
  • $\begingroup$ 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. $\endgroup$ Commented Jan 28, 2013 at 16:59
  • $\begingroup$ @Murta Yes, we can reset the parameter without opening a new connection -- see my update. $\endgroup$ Commented Jan 28, 2013 at 17:36
  • 1
    $\begingroup$ @WReach Thank you for this quite descent investigative reverse engineering. Thank you! $\endgroup$ Commented Jan 28, 2013 at 17:53
0
$\begingroup$

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"]
    , "Username" -> "myUser"
    , "Password" -> "myPass"
    , "Timeout" -> 3
    ]

Will respect the 3 seconds limit.

Improve this answer
$\endgroup$

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.