How to run multiple SQL queries in parallel, wait for fnish, and get list of datatables

UEJ

Hi,

 

I would like to have a function, that (i) runs multiple SQL queries in background (connection and sql statements from string lists with arbitrary lengths), (ii) waits until all queries are finished, and (iii) returns a list of datatables. Please find my code below. I don't know how to gather the data tables correctly.

 

Thank you in advance.

 

Run_SQL_Queries = Function({o_connections, o_sqls},  
	i_dts = List();

	For(i_counter = 1, i_counter <= N Items(o_connections), i_counter++,  
		i_dt = Empty();
		i_connection = o_connections[i_counter];
		i_sql = o_sqls[i_counter];

		query = New SQL Query(Connection(i_connection), Custom SQL(i_sql)) << Run Background(OnRunComplete(i_dt = queryResult));
		Insert Into(i_dts, i_dt);
	);
	Return(i_dts);
);

mmarchandTSI

I've been doing something similar for a while.  I run multiple queries simultaneously and then perform operations on the tables.  I have adapted my solution for your application.  It's not pretty, but I have tried many, many other ways in JMP, and nothing else has accomplished what I wanted.

 

Names Default To Here( 1 );
o_connections = {....Connection List....};
o_sqls = {....Query List....};
::i_dts = {};
Run_SQL_Queries = Function( {o_connection, o_sql, i_counter},
	{Default Local},
	Eval(
		Eval Expr(
			Expr(
				Parse( "::i_dt" || Char( i_counter ) || " = Empty()" )
			);
			New SQL Query( Connection( o_connection ), Custom SQL( o_sql ) ) << Run Background(
				OnRunComplete(
					Expr(
						Parse( "::i_dt" || Char( i_counter ) || " = QueryResult;" )
					);
					Insert Into( ::i_dts, QueryResult )
					;
				)
			);
		)
	)
);
For( i=1, i<=N Items(o_connections), i++, Run_SQL_Queries( o_connections[i], o_sqls[i], i ) );
For( i = 1, i <= N Items( o_connections ), i++,
	Eval( Parse( "While(!Is Scriptable(::i_dt" || Char( i ) || "), Wait(.5))" ) )
);
Show( ::i_dt1, ::i_dt2, ::i_dts );