ODBC Connection Timeout

Question

I have been trying to debug an existing program that daisy chains a whole list of scripts for tons of lengthy ODBS calls and table manipulation. It seems like at some point the ODBC tables aren't being built because the log shows the table as not existing when the JSL code references (which table that isn't being built changes for run to run) it. Each ODBC call returns the correct data when tested by itself, so I am wondering if JMP has a script wide limit to how long it will wait to respond to an ODBC calls. Any advice would be greatly appreciated.

jkwiggins · Answer

Hey Dan

I think I was having a similar problem to yours running two different queries from JSL.  It was important that the first query finish before the second in my script.  The first query pulls more data than the second and does several joins.  The second query would always finish before the first unless I put in a wait().  I wanted to find a better solution than wait().  I banged my head against the wall for a bit then reached out to Dave in tech support.  Here is his reply:

"By default, JMP runs the SQL queries in the background so it does not always know when the query is finished.

This behavior can be changed.  If you go File > Preferences and select the Query Builder option in the Preference Group container, you can uncheck the "Run queries in the background when possible."  This will force JMP to run the query in the foreground and it should not run the second one until the first has completed.

Another option, is to use the OnRunComplete flag to tell JMP when to start the second query.

NextSQL = specify your second sql query here

query = New SQL Query(specify your first query here);

query << Run( OnRunComplete( NextSQL ) );

This will force JMP to not start the second sql query until it has verified that the first one is complete."

Hope this helps you.

Jason

firemandan9 · Answer

Adding the waits in didn't seem to fix the problem of given the same inputs, random ODBC queries are not actually making tables.

The log only knows something is up when it tries referencing it to join with another in a JSL command and it can't find the table.

Is there anyway to get more granular logs as to why the table is not being built to begin with?

pmroz · Answer

Try putting wait(0); statements after each of the SQL readin statements.  That will force JSL to flush its buffers.