Subscribe Bookmark RSS Feed

ODBC Connection Timeout

firemandan9

Community Trekker

Joined:

Jun 28, 2012

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.

5 REPLIES
gbu

Community Trekker

Joined:

Jun 23, 2011

Hello,

I'm using a lot of sql in my jsl scripts and never have issue with jmp timeout execution.

Did you test all the sql query in jmp or in another sql query program ?

Regards,

Guillaume

firemandan9

Community Trekker

Joined:

Jun 28, 2012

I have tested individually both inside and outside JMP. In fact sometimes I can start the code back up from where it stalled and it will go ahead and complete, which makes troubleshooting very difficult so far.

pmroz

Super User

Joined:

Jun 23, 2011

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

firemandan9

Community Trekker

Joined:

Jun 28, 2012

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?

jkwiggins

Community Trekker

Joined:

Sep 24, 2014

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