It’s World Statistics Day! To honor the theme of the day, the JMP User Community is having conversations about the importance of trust in statistics and data. And we want to hear from you! Tell us the steps you take to ensure that your data is trustworthy.
Choose Language Hide Translation Bar
ih
ih
Level VII

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

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(
	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(
	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++;
	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;
	wait(0);
);

// 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 );
Comments

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?

 

 

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...

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

Article Labels
Article Tags
Contributors