681483_en-US

liliconcom

Scripting: Save SQL Query Results in Project

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

Automation and ScriptingData AccessRe: Scripting: Save SQL Query Results in Project

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

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 < but you need to add more of the script within that (and you might need global scope...).