topic Re: Scripting: Save SQL Query Results in Project in Discussions

Scripting: Save SQL Query Results in Project

UEJ — Wed, 27 Sep 2023 06:36:02 GMT

Hi, probably a newbie question, but I couldn't find a solution myself: I want to save the table generated by an SQL Query via a JSL into a project. The code is below; labels, etc. were omitted. Unfortunately, the dt seems to be deleted after running the query in background. Is it a timing issue? Thanks for any help.

query = New SQL Query(
	Connection(""),
	QueryName(""),
	Select(Column("", "t1"), ),
	From(Table("", Schema(""), Alias("t1")), )
);
 
query << Run Background(OnRunComplete(dt = queryResult));
 
project = New Project();
 
project << Save As("");

Re: Scripting: Save SQL Query Results in Project

mmarchandTSI — Wed, 27 Sep 2023 11:10:17 GMT

Running the query in the background causes JMP to not wait for the results before moving on to the next command, so you have to tell it to wait. I generally define dt as Empty() before the query and then tell JMP to wait until dt is scriptable before moving on.

dt = Empty();

query = New SQL Query(
	Connection(""),
	QueryName(""),
	Select(Column("", "t1"), ),
	From(Table("", Schema(""), Alias("t1")), )
);
 
query << Run Background(OnRunComplete(dt = queryResult));

While( !Is Scriptable( dt ), Wait( 1 ) );
 
project = New Project();
 
project << Save As("");

Re: Scripting: Save SQL Query Results in Project

jthi — Wed, 27 Sep 2023 12:22:13 GMT

If you wish to run the query on background and still perform some actions after the query finishes, you can use PostQueryScript

Names Default To Here(1);
obj = New SQL Query(Connection("ODBC:DSN=SampleDSN;"), Custom SQL("SELECT c1, c2, c3 FROM my_table;"));
obj << Post Query Script("show( queryResult << Get As Matrix );");

or I think you could also use <<On Run Complete but you need to add more of the script within that (and you might need global scope...).