topic Re: How to Disconnect from a database via script? in Discussions

How to Disconnect from a database via script?

MBRackliffe — Thu, 08 Jun 2023 16:38:18 GMT

Hello,

I am using the File>Database>Query Builder method to connect to an excel file and create a table. I am able to close the table with jmp script but the connection remains active and locks up the excel file for other users. How can I disconnect via jmp script? I can manually go through File>Database>Open>Disconnect but I would like to do this automatically in the running script.

Here is the query code:

RedBinMain = New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
	),
	QueryName( "RedBinMain_beta" ),
	Select(
		Column( "RBNbr", "t1" ),
		Column( "DMR", "t1" ),
		Column( "Date_Entered", "t1" ),
		Column( "Initiator", "t1" ),
		Column( "Location", "t1" ),
		Column( "AssignedPerson", "t1" ),
		Column( "Problem", "t1" ),
		Column( "USI_PN", "t1" ),
		Column( "Customer", "t1" ),
		Column( "Disposition", "t1" ),
		Column( "LastUpdatedDate", "t1" ),
		Column( "Cause", "t1" ),
		Column( "Classification", "t1" ),
		Column( "Defect_Code", "t1" ),
		Column( "BrokenSubMenu", "t1"),
		Column( "BrokenSubMenu_1", "t1"),
		Column( "Dimensional_Code", "t1" ),
		Column( "Notes", "t1" ),
		Column( "Assignable_WorkCell", "t1" ),
		Column( "DispositionerInvestigator", "t1" ),
		Column( "ScrappedQuantity", "t1" ),
		Column( "PreEtch", "t1" ),
		Column( "PostEtch", "t1" )
	),
	From( Table( "RedBinMain_beta", Alias( "t1" ) ) )
) << Run Foreground;

I tried the below code with no luck

close database connection(RedBinMain);

What am I missing?

Here is where the connection is shown as "connected" using the ODBC driver "Excel 12.0"

Re: How to Disconnect from a database via script?

Jed_Campbell — Mon, 06 Feb 2023 21:28:30 GMT

The key is in naming the database connection separately from the query. In your example above, RedBinMain is for the query, not the connection. Example below that should make the previous sentence actually make sense:

dbc = Create Database Connection(
	"ODBC:DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
);

//your script here: RedBinMain = New SQL Query ........ << Run Foreground

Close Database Connection (dbc);

Re: How to Disconnect from a database via script?

MBRackliffe — Tue, 07 Feb 2023 15:18:25 GMT

Thank You! That makes sense and works now although I had a ODBC error at first until I removed the "ODBC:" before the DSN= statement.

I inserted "dbc" as the connection variable in the query as well in case anyone else reads this. Here is the new working code for reference.

dbc = Create Database Connection(
		"DSN=Excel 12.0;DBQ=I:\Engineering\Data\JMPTempData\redbindownload_JMPTMP.xlsx;DefaultDir=I:\Engineering\Data\JMPTempData;DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;PageTimeout=5;"
	);

RedBinMain = New SQL Query(
	Version( 130 ),
	Connection(dbc),
	QueryName( "RedBinMain_beta" ),
	Select(
		Column( "RBNbr", "t1" ),
		Column( "DMR", "t1" ),
		Column( "Date_Entered", "t1" ),
		Column( "Initiator", "t1" ),
		Column( "Location", "t1" ),
		Column( "AssignedPerson", "t1" ),
		Column( "Problem", "t1" ),
		Column( "USI_PN", "t1" ),
		Column( "Customer", "t1" ),
		Column( "Disposition", "t1" ),
		Column( "LastUpdatedDate", "t1" ),
		Column( "Cause", "t1" ),
		Column( "Classification", "t1" ),
		Column( "Defect_Code", "t1" ),
		Column( "BrokenSubMenu", "t1"),
		Column( "BrokenSubMenu_1", "t1"),
		Column( "Dimensional_Code", "t1" ),
		Column( "Notes", "t1" ),
		Column( "Assignable_WorkCell", "t1" ),
		Column( "DispositionerInvestigator", "t1" ),
		Column( "ScrappedQuantity", "t1" ),
		Column( "PreEtch", "t1" ),
		Column( "PostEtch", "t1" )
	),
	From( Table( "RedBinMain_beta", Alias( "t1" ) ) )
) << Run Foreground;

Close Database Connection (dbc);