Run queries in background, wait for them to complete, and reference them in the remaining JSL script

[#1]

Problem

You want to run a query in the background so the user interface doesn't freeze, or you want to run multiple queries at the same time and then combine the results. Sometimes it is difficult to put all of the functionality you need in the 'on run complete' script of the background queries.

Solution

Each query saves results in a new namespace that all scripts can access (queries and the original script).  The original script keeps checking for results before proceeding.

//Example script which runs queries in the background and references their results
//once finished.

Names default to here(1);

// Namespace to share data between background query and this script
ns = new namespace( "queries" );

// Flags which the background query will set to 1 when the background query is complete
ns:query1isdone = 0;
ns:query2isdone = 0;


// Open a window to display results
win = New window("Query Status", show menu(0),  show toolbars(0),
	text box("Status of queries:"),
	tb = tablebox( string col box( "Query" , { "Query 1", "Query 2" } ),
	               string col box( "Status", { "Not Started", "Not Started" } ) ),
	tbStatus = text box("Starting queries.")
);
wait(0); //make sure window shows up

// Define and start background queries

// First Query
sql_query1 = New SQL Query(
	Connection( "ODBC:DSN=Test Source;DATABASE=Test Database;" ),
	QueryName( "Query" ),
	Select( vars ),
	From( table ),
	Where( condition )
);

sql_query1 << Run Background( OnRunComplete(
	Names default to here(1);
	ns = namespace( "queries" );
	ns:dt1 = queryResult;
	ns:query1isdone = 1;
) );

// Second Query
sql_query2 = New SQL Query(
	Connection( "ODBC:DSN=Test Source;DATABASE=Test Database;" ),
	QueryName( "Query" ),
	Select( vars ),
	From( table ),
	Where( condition )
);

sql_query2 << Run Background( OnRunComplete(
	Names default to here(1);
	ns = namespace( "queries" );
	ns:dt2 = queryResult;
	ns:query2isdone = 1;
) );

// Every second check if the query is done (timeout after 10 seconds)
i=0;
allqueriesdone = 0;
starttime = tick seconds();
while( ( i < 10 ) & allqueriesdone == 0 ,
	i++;
	wait(1);
	if( ns:query1isdone == 1,
		tb << delete row( 1 );
		tb << insert row( 1, {"Query 1", "Complete"} );,
		tb << delete row( 1 );
		tb << insert row( 1, {"Query 1", "Still Running"} );
	);
	if( ns:query2isdone == 1,
		tb << delete row( 2 );
		tb << insert row( 2, {"Query 2", "Complete"} );,
		tb << delete row( 2 );
		tb << insert row( 2, {"Query 2", "Still Running"} );
	);
	if( (ns:query1isdone == 1 ) & (ns:query2isdone == 1 ), allqueriesdone = 1 ); //indicate that everything finished
	tbStatus << Set Text( "Queries are running, " || format(tick seconds() - starttime, "Fixed Dec", 12, 1) || " seconds elapsed." );
	win << bring window to front;
);

// Show an error if any queries didn't finish
if(	allqueriesdone == 0,
	tbStatus << Set Text("Error, a query didn't finish and is still running!"),
	tbStatus << Set Text("Queries finished after " || format(tick seconds() - starttime, "Fixed Dec", 12, 1) || " seconds.");
	//win << Close Window; //could close window here
);

// Now reference the data tables
show( ns:dt1 );
show( ns:dt2 );

[Byron_JMP]

this sounds like a really intriguing project idea. 

I just wanted to note that in Query Builder, there is a progress tab where I can see how many queries are running and their progress.  Maybe this would be a way to script the Query builder platform to display this information rather than  trying to get it from scratch?

[ih]

Hi @Byron_JMP,

I agree if the goal is to check on the status of queries run interactively then a user should look no further than the Query Builder.  This status window was meant to demonstrate the parts of working with queries that users had trouble finding with simple web searches, like referencing results in a script after it completed. To be honest I remember having a hard time coming up with a minimal example.  I'm sure there is a better use case out there...

[Byron_JMP]

still, a nice bit of code to reference in a tight spot. : )

[MarkDayton]

First, thank you for this script. Use of namespaces, scripting SQL queries, testing if they're done, providing status feedback, managing the results; this script packs a lot of teaching into a few lines of JSL. I can follow most of it, but I don't have much experience defining and using namespaces, so I have a question:

In the second line of the script, ns is defined as: 

ns = new namespace( "queries" );

Then it seems ns is re-defined in the Run Background() statements:

sql_query2 << Run Background( OnRunComplete(
	ns = namespace( "queries" );
	ns:dt2 = queryResult;
	ns:query2isdone = 1;
) );

Is the namespace within sql_query1 & 2 the same namespace as the one defined at the beginning of the script, it seems like it would have to be, or it a namespace that is local to the query statement because of the Names Default to Here(1) statement at the beginning? Why does ns need to be defined within the Run Background() statement? 

Thanks again for a simple, clear, beautiful bit of code.

[ih]

Hello @MarkDayton,

Thank you for digging into this as you pointed out a shortfall of the script which I just corrected. I did not have Names default to here(1) in the OnRunComplete scripts, so it wasn't working exactly how I planned.  Here is a description of the namespaces before and after the update I just made:

Overview

Note how the namespace 'queries' is created in the parent script and then referenced in the OnRunComplete scripts.  The variable ns itself is only used to reference the namespace 'queries'. For example, in a new script window window you could run:

Names default to here(1);
ns = New Namespace("queries");
ns:a = 1

and a second script window could get the value of a by calling:

Names default to here(1);
Show(Namespace("queries"):a);

but you cannot easily access ns in the other window. You can, however, define a new variable in the other window, then access a in a similar way:

Names default to here(1);
nsreference = Namespace("queries");
Show(nsreference);

Or, to make the code easier to read, you could use the same variable name in both windows.  The variables themselves are different as they are defined in different namespaces, but they reference the same thing so you can use them the same way.

Names default to here(1);
ns = Namespace("queries");
Show(ns:a);

This is how the variables are used in the example script above: a variable called 'ns' is created in each here namespace that reference the named namespace 'queries', making it easier to share values between those scripts.

Before change - how this would work without starting each OnRunComplete script with Names default to here(1):

View more...

Three namespaces are used by the script:
- The here namespace for the parent script window
- The global namespace, used by the OnRunComplete scripts
- The namespace named 'queries', referenced by both the parent script and the two OnRunComplete scripts

The variable ns in the parent script is defined in the here name space to reference the queries namespace, which is created with New Namespace().  That means only the script window has access to ns, but anything else in that instance of JMP can reference whatever is saved within the queries namespace that is referenced by ns. 

The OnRunComplete scripts are opened in a new environment and do not have access to the parent script's here namespace.  Thus a new variable that also called ns is defined in that script, which is created in the global namespace.  When the second query finishes the variable ns will be recreated in the global namespace. So both OnRunComplete scripts actually do reference the same variable, ns.

After Change - how the script works now

Four namespaces are used by the script:

1. The here namespace for the parent script window
2. The here namespace for the OnRunComplete script of the first query
3. The here namespace for the OnRunComplete script of the second query
4. The namespace named 'queries', referenced by both the parent script and the two OnRunComplete scripts

The variable ns in the parent script is defined in the here name space to reference the queries namespace, which is created with New Namespace().  That means only the script window has access to ns, but anything else in that instance of JMP can reference whatever is saved within the queries namespace that is referenced by ns. 

The OnRunComplete scripts are opened in a new environment and do not have access to the parent script's here namespace.  Thus a new variable that also called ns is defined in that script's here namespace, and it is set to reference the 'queries' namespace using Namespace().  Each run complete script creates its own variable called ns in its own here namespace, but then each of those will be set to reference the same 'queries' namespace.