Status of Query running in the Foreground

[#1]

Is it possible to update the user on the status of a query running in the foreground using JSL? I have a SQL query that takes hours to run and the script needs to operate on the table after it opens so I cannot run it as a background query. 

Currently I am using Open Database():

/* fictitious database and table */
dt = Open Database( "DSN=ODBC Connection",
	"SELECT * FROM some_table" )

[#2]

There is not a dedicated JSL option for this.  If you have the JMP UI visible, you can always check on the status of a query with the View->Running Queries menu item. Within JSL, you can also use the Main Menu command to bring up the dialog, like:

Main Menu("VIEW:RUNNING QUERIES");

Brian Corcoran

JMP Development

[#3]

I should mention the above is really for background queries, sorry.  For foreground queries, there should be a progress bar that is informing the user of the number of records read and preventing further input.

Brian

[#4]

If the query itself takes a long time to process you won't see a progress bar until data is actually being read.  I message the user with the caption() command to at least let them know something is going on.  For some applications I can estimate the time it will take and I'll include that in the message.  Time is relative!

caption("Reading data, please be patient...");
wait(0);

/* fictitious database and table */
dt = Open Database( "DSN=ODBC Connection",
	"SELECT * FROM some_table" );

caption(remove);
wait(0);

[#5 · Accepted solution]

Hey, ih,

There is not a way to update status from a foreground query, because the query ties up JMP's main thread, so JMP really can't do anything while it's running.

The workaround I would suggest would be to use Query Builder to create your query (using Custom SQL if you already have the SQL you want to run), and then save the query as a file.  Let's suppose it was named "oneMillionRows.jmpquery" on disk.

Then, write a script like this:

query = include("oneMillionRows.jmpquery");

query << Run Background( 
	On Run Complete(
		Write("\!NQuery is complete!  Rows in result: ", NRows(queryResult));
	)
);

Whatever JSL you want to run after the query completes can go in the On Run Complete parameter to Run Background().  So JMP will still be usable while the query is running.  The user could use View > Running Queries to see the progress.  Whether or not there will be much progress to report depends on whether the query spends most of its time in the execution phase or in the data retrieval phase.  We don't get any updates from the database during the execution phase; we just update the number of rows retrieved as we fetch the data from the database after the query executes.

Eric

[#6]

Thank you Eric,

Splitting the script up to run from the query will be a bit of work but it looks like it will get the job done.