cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
vince_faller
Super User (Alumni)

Getting a data table reference from NewSQLQuery() << Run Background with Names Default to Here(1);

I'm having trouble getting the data table reference if I run in the background.  Anyone know how to get around this?  

 

names default to here(1);

sql_query = New SQL Query(
	Connection(
		"ODBC:DSN=Test Source;DATABASE=Test;"
	),
	QueryName( "Thingy" ),
	Select(
		blah
	),
	From(
		other blah
	)
);

sql_query << Run Background(
	OnRunComplete(
		dt = queryResult;
		dt = data table(queryResult<<Get Name);
		show(dt); //shows correctly for either one
	)
);

show(dt); // it seems like on run complete is local
Vince Faller - Predictum
2 ACCEPTED SOLUTIONS

Accepted Solutions

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

In my test I did not add the Names Default To Here(1) - it looks like that is the difference.  The OnRunComplete() function is not running in the same Here namespace as the original script.  If you create your own namespace to hold the results, you should be able to access the variables after the query completes.

View solution in original post

ih
Super User (Alumni) ih
Super User (Alumni)

Re: Getting a data table reference from NewSQLQuery() << Run Background with Names Default to Here(1);

One option is to use a new namespace for the subquery:

names default to here(1);

// Namespace to share data between background query and this script
ns = new namespace( "subquery" );

// The background query will set to 1 when the background query is complete
ns:isdone = 0;

sql_query = New SQL Query(
	Connection( "ODBC:DSN=Test Source;DATABASE=Test;" ),
	QueryName( "Thingy" ),
	Select( blah ),
	From( other blah )
);

sql_query << Run Background(
	OnRunComplete(
		ns = namespace( "subquery" );
		ns:result = queryResult;
		ns:dt = data table ( queryResult << Get Name );
		ns:isdone = 1;
	)
);

//every second check if the query is done (timeout after 10 seconds)
i=0;
while( ( i < 10 ) & ( ns:isdone == 0 ) ,
	i++;
	if( ns:isdone == 1, show( ns:dt ), show( "Query not done yet." ); wait( 1 ); );
);

//now you can reference the data table
show( ns:dt );

Edit: Here is a link to a similar example:

https://community.jmp.com/t5/JMP-Scripts/Run-queries-in-background-wait-for-them-to-complete-and/ta-...

View solution in original post

7 REPLIES 7

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

Maybe I'm not understanding, but I think this shows why you need the <<OnRunComplete() script.  When run in the background, control returns immediately and the data table does not exist yet.  So you will get an error if you try to use dt before it is created.

 

If you add a Wait(1) between the <<RunBackground() call and the second Show(dt) message, I think both Show() commands will succeed.

vince_faller
Super User (Alumni)

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

Adding the wait(1) does not make the second show(dt) work.  That's the problem.  There is no way I can find to get a data table reference from a sql query run in the background. 

Vince Faller - Predictum
vince_faller
Super User (Alumni)

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

Even if I do this. 

 


query_complete = 0;
query << Run Background(
	OnRunComplete(
		query_complete = 1;
		dt = queryResult;
		show(dt); //shows correctly for either one
	)
);

It seems like On Run Complete doesn't affect me.  If I run it it in parts then check the value of query_complete, it's still 0  even after the Run complete script is run.  

Vince Faller - Predictum

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

In my test I did not add the Names Default To Here(1) - it looks like that is the difference.  The OnRunComplete() function is not running in the same Here namespace as the original script.  If you create your own namespace to hold the results, you should be able to access the variables after the query completes.

vince_faller
Super User (Alumni)

Re: Solving Common Data Table Problems With JMP® 13: Can We Replace Summary and Join With the New JMP Query Builder?

I put in a bug to support.  It looks to me like <<Run Background has its own here namespace that is separate and unable to communicate with mine.  Notice how the value doesn't show the first time, but once it has run the query remembers it's value set.  

query_complete = 0;
dt_list = {};
dt_array = associative array();
str = "A";
ns = new namespace("Test");
query << Run Background(
	OnRunComplete(
		try(show(query_complete)); //this won't show, but if you run it twice it will show as 1
		try(show(dt_list));
		try(show(dt_array));
		try(show(str));
		try(show(ns));
		try(show(namespace("Test")));
		
		query_complete = 1;
	)
);

Vince Faller - Predictum
Phil_Brown
Super User (Alumni)

Re: Getting a data table reference from NewSQLQuery() << Run Background with Names Default to Here(1);

Any news on this. I'm running into the very same problem!
PDB
ih
Super User (Alumni) ih
Super User (Alumni)

Re: Getting a data table reference from NewSQLQuery() << Run Background with Names Default to Here(1);

One option is to use a new namespace for the subquery:

names default to here(1);

// Namespace to share data between background query and this script
ns = new namespace( "subquery" );

// The background query will set to 1 when the background query is complete
ns:isdone = 0;

sql_query = New SQL Query(
	Connection( "ODBC:DSN=Test Source;DATABASE=Test;" ),
	QueryName( "Thingy" ),
	Select( blah ),
	From( other blah )
);

sql_query << Run Background(
	OnRunComplete(
		ns = namespace( "subquery" );
		ns:result = queryResult;
		ns:dt = data table ( queryResult << Get Name );
		ns:isdone = 1;
	)
);

//every second check if the query is done (timeout after 10 seconds)
i=0;
while( ( i < 10 ) & ( ns:isdone == 0 ) ,
	i++;
	if( ns:isdone == 1, show( ns:dt ), show( "Query not done yet." ); wait( 1 ); );
);

//now you can reference the data table
show( ns:dt );

Edit: Here is a link to a similar example:

https://community.jmp.com/t5/JMP-Scripts/Run-queries-in-background-wait-for-them-to-complete-and/ta-...