topic Re: Closing SQL query after running in Discussions

Closing SQL query after running

Polyabc — Fri, 09 Jun 2023 22:02:01 GMT

Hello,

I have a JSL script that calls for different SQL queries. After the data table is complete, I want to close the queries. Is this possible?

query = Close( "C:\\example.jmpquery" )

does not work.

Thanks.

Re: Closing SQL query after running

txnelson — Tue, 29 Dec 2020 09:58:21 GMT

See

Close Database Connection

in the Scripting Index.

I believe that

Close Database Connection( query );

should work

Re: Closing SQL query after running

Polyabc — Tue, 29 Dec 2020 11:07:54 GMT

Thanks!

What I want to know if it is possible to close the queries themselves, not necessarily the connection to the database. At the end of the running the script I'm left with 10 open queries which I don't need. Any idea how to close the individual queries?

Re: Closing SQL query after running

Mark_Bailey — Tue, 29 Dec 2020 13:24:41 GMT

I assume that you mean by 'query' the resulting JMP data tables. If so, then this example should help you.

// an open query
 query = Open Database( ... );


// close query
Close( query, No Save );

Re: Closing SQL query after running

Polyabc — Tue, 29 Dec 2020 13:32:44 GMT

Thanks very much for your reply.

I meant the query itself, not the data table. the query that is opened with Select File > New > Database Query.

Is there any way to close this query itself?

Thanks.

Re: Closing SQL query after running

Mark_Bailey — Tue, 29 Dec 2020 14:10:09 GMT

I do not have an answer. You might take this problem to JMP Technical Support (support@jmp.com).

Re: Closing SQL query after running

pmroz — Tue, 29 Dec 2020 15:35:14 GMT

I'm not sure what you mean by "closing the query". How are you seeing these queries as "open"? I run multiple SQL queries in my applications, and when I'm done I call close database connection, as Jim indicated. Open tables can be closed with close(<tbl-handle>, nosave) as Mark showed.

This is an example of what I do:

dbc = create database connection("<connection-string>");

sql1 = "SELECT sysdate FROM DUAL";
dt1 = execute sql(dbc, sql1, invisible);

sql2 = "SELECT COUNT(*) FROM all_tables";
dt2 = execute sql(dbc, sql2, invisible);

close database connection(dbc);

Can you share your code?

Re: Closing SQL query after running

Polyabc — Wed, 30 Dec 2020 07:34:47 GMT

Thanks for your reply.

My code is something like this:

query1 = Open( "c:/iris/query1.jmpquery" );
query1 << Run Foreground;

query2 = Open( "c:/iris/query2.jmpquery" );
query2 << Run Foreground;

I join the tables that result from these queries, but am left with the open queries themselves.

Any thoughts?

Re: Closing SQL query after running

pmroz — Wed, 30 Dec 2020 15:16:22 GMT

I don't use JMP queries. You might need to contact tech support.

Totally random stab in the dark - would this do the trick?

query1 = Open( "c:/iris/query1.jmpquery" );
query1 << Run Foreground;
query1 = "";
query2 = Open( "c:/iris/query2.jmpquery" );
query2 << Run Foreground;
query2 = "";

Re: Closing SQL query after running

tom_abramov — Thu, 31 Dec 2020 07:30:00 GMT

q1 = Open("C:\myQuery.jmpquery");
q1 << Run Foreground;
q1 << Close Window();

Re: Closing SQL query after running

Polyabc — Thu, 31 Dec 2020 07:55:55 GMT

Thanks! this works!