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 );
Article Labels
Article Tags
Contributors