topic Creating a Database Connection using JSL (and closing it) in Discussions

Creating a Database Connection using JSL (and closing it)

tbidwell — Fri, 09 Jun 2023 16:06:24 GMT

I am trying to Create a db connection and then close it when my script is complete. I've copied the Connection string from the "Connection()" command in my "New SQL Query()" function and pasted it into the "Create Database Connection()" function. But when I run the script I get an error in the log related to the definition of dbc1 that says "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".

Below is my JSL script that I am trying to run and the old method that runs without a problem.

Notes:

the CustomSQL in the Query is defined elsewhere and isn't shown to make this example shorter.
I was trying to follow the solution shown in this post: https://community.jmp.com/t5/Discussions/How-to-Disconnect-from-a-database-via-script/m-p/597749#M80126
UPDATE: I am running JMP 16.1 and get the error shown above. When I run this in JMP 17.0, it works just fine.

names default to here(1);

// trying to create the db connection and then insert into the Query
dbc1 = Create database Connection("ODBC:DSN=SQLplus 64-bit ODBC Connection;");
New SQL Query( 
	Connection( dbc1 ), 
	CustomSQL( combined ) 
) << Run Foreground();


// vs. my typical method
New SQL Query( 
	Connection( "ODBC:DSN=SQLplus 64-bit ODBC Connection;" ), 
	CustomSQL( combined ) 
) << Run Foreground();

Re: Creating a Database Connection using JSL (and closing it)

tbidwell — Tue, 28 Mar 2023 18:00:27 GMT

JMP support found the problem. I copied the connection string from the New SQL Query() script from Query Builder. The Create database connection() function doesn't allow the 'ODBC:' part of the DSN definition. So, the JSL script needs to look like:

names default to here(1);

// trying to create the db connection and then insert into the Query
dbc1 = Create database Connection("DSN=SQLplus 64-bit ODBC Connection;");
New SQL Query( 
	Connection( dbc1 ), 
	CustomSQL( combined ) 
) << Run Foreground();

Re: Creating a Database Connection using JSL (and closing it)

tbidwell — Tue, 28 Mar 2023 18:04:19 GMT

Thank you to @Dahlia_Watkins