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
