How to stop an SQL query

[#1]

Hello, I'm looking for a to stop a SQL query running in background from JSL, to close a script neatly in case the user wants to exit before the query is completed.

I am using JMP 18.0.1.

thanks 

Matteo

[#2]

View / Running Queries -> Select query, press stop and hope for the best

No cancel commands here https://www.jmp.com/support/help/en/18.0/index.shtml#page/jmp/sql-messages.shtml

From https://www.jmp.com/support/help/en/18.0/#page/jmp/sql-functions.shtml

So you might want to contact JMP Support about how JMP handles situations where user closes JMP before the background query finishes (does it just leave running query without any cancel message to database and let database manage with the open query? hopefully not). Post back if you get a good answer from JMP Support

[#3 · Accepted solution]

I'm not sure if this really does anything but you can also try using that window and press OK on each of the queries. I'm not sure if it is enough to just press the stop or do you have to also wait for all the queries to be stopped.

This is just an example which hasn't been tested

Names Default To Here(1);

Main Menu("Running Queries");
wait(2);

ws = Get Window List(); // you maybe can trust that last is Running Queries
idx = Contains(ws << get window title, "Running Queries");

mywindow = ws[idx];
gdb = mywindow[GridBox(1)];

// get list of all open queries (not implemented here as I cannot currently test it)
queries = gdb[1] << get;
queryrows = {};
For Each({query, idx}, queries,
	If(query != "        ",
		Insert Into(queryrows)
	);
);

gdb << Set Selected Rows({queryrows});
(mywindow << XPath("//ButtonBox[text() = 'Stop']")) << Click(1);

// maybe needs some sort of While loop or Schedule to check if all queries have closed?

[#4]

I will give it a try.

thanks

Matteo

[#5]

Thanks Jarmo.

the menu command is already a partial solution! 

For the scripting, I have contacted JMP support, I'll let you know. 

Matteo

[#6]

This is the feedback I got from JMP Support.

There are no known JSL methods to stop SQL statements as View > Running Queries > Stop does. Close Database Connection () is the only JSL method that may appear to behave this way. However, it only closes the connection. It does not stop the SQL.

....

Closing JMP kills all the database connections. If a query is being executed by the database when you close JMP, closing JMP does not kill that execution. The database will eventually see an idol connection or killed connection and self-terminate the execution of the SQL.