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

[#1]

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

[#2]

I'm not sure if you can build such a function in JMP. You might have to use << OnRunComplete within your << Run Background and perform whatever you are doing, after all queries are ready, using that.

[#3]

Unfortunately JMP only provides a single execution thread for JSL.  For my company I get around this limitation and do exactly what you're wanting to do by using a persistent Python session.  I launch the Python session from JMP, which has a hook for when the STDOUT buffer is not empty, and in Python I start a localhost server and wait for commands.  Then in JMP I can connect to the localhost server and send messages.  Python will then, in parallel, complete the assigned tasks (whether it's SQL or file-transfers or anything needed) and have special print statements for when complete, thus closing the loop and allowing JMP to know when the queries are completed.

For SQL I have Python save the results in a temporary file then JMP can simply read the file.

It is a little round-about, but not too much more difficult than if JMP provided a means for concurrency or asynchronous execution.

This works quite well and the slowdown due to IPC is not noticeable.

[#4 · Accepted solution]

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