cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Georg
Level VII

Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

Dear all,

I often Need data from different Sources and Combine them. As some queries May take a lot of time, I wrote a script, to start several queries in Background and Combine data at the end. Howewer, it is not so easy to find the trigger, when all queries have been finished, and when to start processing of the results. In the below script I tested two approaches (Setting flag by "OnRunComplete" and testing for existence of result table). They fairly work, but I think there May be even a better way.

One Problem is, that the loop will run infinitely, if one of the queries delivers no table. Another Problem is, that the script not really controls, whether the queries are still active.

The script is currently set to a debug mode, where no database Connection is neccesary to test this behaviour.

Any ideas for improvement? Thanks and BR

 

// Purpose:
// Start two database Queries (from different sources) in parallel and in background
// Wait for queries to finish
// and combine result
// JMP 15.0 Win10
//
// Details:
// After both queries are started in background
// a loop is started, that queries when both tables are existing
// In a previous version I worked with setting a flag ("OnRunComplete( B_QUERY_FINISHED = 1 )")
// 
// Problem:
// A better solution would be to test, whether DB query is still running, but how?
// If the database query ends unexpectedly (by passing no table), the loop runs infinetely

Names Default To Here( 1 );
Clear Log();

// debug_mode: 0 - query from database / 1 - make table
// for database query the connectionstring must be correct
debug_mode = 1;

A_QUERY_FINISHED = 0;
B_QUERY_FINISHED = 0;

A_NAME = "A_Table";
B_NAME = "B_Table";

// new function to check whether background query is finished (= table does exists)
table_exists = Function( {table_name},
	Names Default To Here( 1 );
	table_lst = Get Data Table List();
	table_name_lst = {};
	For( i = 1, i <= N Items( table_lst ), i++,
		Insert Into( table_name_lst, table_lst[i] << get name() )
	);
	Return( If( Contains( table_name_lst, table_name ) > 0, 1, 0 ) );
);

If( table_exists( A_NAME ) | table_exists( B_NAME ) ,
	Throw( eval insert ("^A_NAME^ or ^B_NAME^ open! Please close " ))
);

// Define query
sql_str = "Select * from dual";
A_sql_str = sql_str;
B_sql_str = Substitute( sql_str, "A_scheme", "B_scheme" );

// Database query expression in backgroud
nsqlq_A_expr = Expr(
	New SQL Query( Connection( "ODBC:DSN=XXX;DBQ=YYY;" ), QueryName( A_Name ), CustomSQL( A_sql_str ) ) <<
	Run Background( OnRunComplete( A_QUERY_FINISHED = 1 ) )
);
nsqlq_B_expr = Expr(
	New SQL Query( Connection( "ODBC:DSN=ZZZ;DBQ=AAA;" ), QueryName( B_Name ), CustomSQL( B_sql_str ) ) <<
	Run Background( OnRunComplete( B_QUERY_FINISHED = 1 ) )
);

// Create table expression for Debugging
A_expr = Expr(
	New Table( "A_Table", Add Rows( 1 ), Compress File When Saved( 1 ), New Column( "DUMMY", Character, "Nominal", Set Values( {"X"} ) ) )
);
B_expr = Expr(
	New Table( "B_Table", Add Rows( 1 ), Compress File When Saved( 1 ), New Column( "DUMMY", Character, "Nominal", Set Values( {"X"} ) ) )
);

// start queries only in case of no debug_mode
If( debug_mode == 0,
	Eval( nsqlq_A_expr );
	Eval( nsqlq_B_expr );
);

start_time = Tick Seconds();
timestep = 1;
While( (table_exists( A_NAME ) & table_exists( B_NAME )) != 1,
	Wait( timestep );
	time_running = Round( Tick Seconds() - start_time, 0 );
	Print( "waiting " || Char( time_running ) );
	Show( table_exists( A_NAME ) );
	Show( table_exists( B_NAME ) );
// For debugging only: create Table A after 5 seconds and Table B after 10 seconds
	If( debug_mode == 1,
		If( time_running > 5 & !table_exists( A_NAME ),
			Eval( A_expr )
		);
		If( time_running > 10 & !table_exists( B_NAME ),
			Eval( B_expr )
		);
	);
);

Wait( 1 );

Print( "Queries Finished" );
end_time = Tick Seconds();

Print( "Duration: " || Char( Round( end_time - start_time ) ) );

// concatenate
Try( Data Table( A_Name ) << Concatenate( Data Table( B_Name ), Append to first table ), Throw( Print( "Concatenate error" ) ) );
// Close( Data Table( B_Name ), nosave );

Data Table( A_Name )<< set name("Result Table");

Print( "Concatenating finished, script end " );
Georg
1 ACCEPTED SOLUTION

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

4 REPLIES 4
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

 Check out this example script, I think it does what you are asking for:

 

https://community.jmp.com/t5/JMP-Scripts/Run-queries-in-background-wait-for-them-to-complete-and/ta-...

Georg
Level VII

Re: Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

Thanks @ih for your solution, it is nice and well programmed, so I will try to work with it.

In a first test I saw still some Timing issue (I suppose), because a reference was (not yet) valid.

Introducing a wait for several seconds in the Statement "onRunComplete" then worked, but is not the solution.

 

I think that's why I earlier decided to query available tables before referencing it. I will further test it.

Georg

Re: Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

There is an ODBC setting in preferences that has lead to similar misbehavior for me. If "Run queries in the background if possible" option is checked then uncheck it and try your script again. I think there is a trade-off of speed for stability in doing this, but worth experimenting with if you have not already.

 

Jason_Wiggins_0-1592921523328.png

 

 

 

ih
Super User (Alumni) ih
Super User (Alumni)

Re: Run several database queries (ODBC) in background and combine data afterwards, how to control running queries?

I do not believe I've experienced this same issue, if you continue to have trouble can you post the log output with the error, or if you can fix it can you let me know how so I can update the example script?