Mathematica 9.0 problem: ParallelTable with SQLExecute

I have run into the following problem: when I try to run the following code in Mathematica 9 I get SQL connection error on all kernels. The code is:

ParallelTable[SQLExecute[conn, "Select 2+2"], {4}]


And the error message: SQLConnection::conn: Connection is not opened properly.

The connection itself works fine - I tested it. Also the above code used to work in Mathematica 7. On 8 a similar but more complex SQL request did not work.

I would appreciate any suggestions what I could do to solve this (tried distribute Definition on conn and also tried creating a list of connections and selecting one according to \$KernelId).

• Not sure but I think you would need to ParallelEvaluate the conn=OpenSQLConnection[...], so that each Kernel has it's own connection to the database. Note that this is different than creating a list of connections on the master and distributing them, which I definitely would not expect to work. I wonder how the above could work with version 7, though. Are you sure that with version 7 the ParallelTable was really executed in parallel? Feb 25 '13 at 13:24
• just another comment to ring a bell, forgot that in my last. Sorry for the noise... Feb 25 '13 at 13:50
• Thanks a lot, it seems this did the trick. About Mathematica 7: I am sure that it launched subKernels and did not give error messages, but I don't know if it actually used the subKernels or not. I will check it once i get my hands on it again.
– Vajk
Feb 25 '13 at 14:50

To make this work you need to open an independent connection from every parallel kernel. It is important the the call to OpenSQLConnection is evaluated on the parallel kernels, so generating a list of connection on the master and distributing these to the parallel kernels will not work. To generate the connections on the parallel kernels the most straightforward solution is to ParallelEvaluate something like conn=OpenSQLConnection[...]. This should work even if the parallel kernels run on different machines and platforms (as long as they can connect to the database). It is the database that will need to handle potential problems from simultaneous access from the parallel kernels, so you'll need a database system that supports concurrent access (which most but not all such systems do).
• Just ran into this exact problem with v11.3. When I run something like ParallelMap[(res=SQLExecute[conn,"SELECT 2+2"];Print@First@res;)&,Range@4], I expect to see "4" printed 4 times during every evaluation on every thread. BUT... seems that what's being assigned to res isn't the result of evaluation, but rather the unevaluated SQLExecute statement. I did create my connection on all threads with ParallelEvaluate[]. What gives? Thanks in advance! Oct 2 '18 at 21:30
• Have you checked that conn is a valid connection on each kernel? And that you did Needs["DatabaseLink"] on each of them? Oct 3 '18 at 7:06
• @GregoryKlopper: How do you know that SQLExecute works? In Mathematica, if it works it will evaluate to its result. If it doesn't evaluate, it doesn't work. I see only two ways how that could happen: a) SQLExecute is in the wrong context or its code has not been loaded, b) one of its arguments is not of the expected form (as the second is clearly the expected string that only leaves conn as a potential problem). Why don't you just do a Print[conn]; before the SQLExecute and see what that looks like? You could also add a Print[Context[SQLExecute]] which also might help... Oct 3 '18 at 9:33
• I know it works because when I do ParallelMap[SQLExecute[conn,"select 2+2"]&,Range@4] I get {{4},{4},{4},{4}}, but if I do ParallelMap[(res=SQLExecute[conn,"select 2+2"]; Print@res)&,Range@4]` I get my actual SQLExecute call, fully qualified, printed 4 times. I'm expecting to see 4 prints of 4. Oct 3 '18 at 12:13