topic Re: Create Database Connection Error in Discussions

Create Database Connection Error

Djtjhin — Fri, 09 Jun 2023 18:06:28 GMT

I'm trying to establish connection to database through scripting and it's the first time I'm doing this. I was able to create the following script but it seems that there's a bug that everytime there's unsuccessful connection, there's a default "JMP Alert" pop-up (see snapshot) instead of being contained within the "Try" function (I was trying with an incorrect database name of "TEST" which led to the pop-up. Is there a way to control this ?

Additionally, are there any good reading material (e.g. basic best practice) for incorporating SQL to JMP script ?

Many Thanks!

names default to here(1);

//Standard SQL syntax string to create database connection from JMP
DB.SQL = "DSN="||"DSN_Name" ||";Description=" || "DSN_Desc" || ";
	Trusted_Connection=Yes;APP=JMP;DATABASE="|| "DB_Name" || ";";

//Expression to arrange the User Interface of PODS setup
//Require users to input Database Source Name (DSN Name), Database Source Description (DSN Description) and Database Name 
DB_Connection.UI = expr(
	Hlistbox(spacerbox(size(5,5)),
		Vlistbox(align("right"),spacerbox(size(5,5)),
			vb1_1 = vlistbox(align("left"),
				panelbox("Insert PODS Connection Info",
					lineupbox(ncol(2),
						Textbox("DSN Name : "),TEB_DSN_Name = Texteditbox("",set width(150)),
						Textbox("DSN Description : "),TEB_DSN_Desc = Texteditbox("",set width(150)),
						Textbox("Database Name : "),TEB_DB_Name = Texteditbox("",set width(150))
					)
				),
				spacerbox(size(5,5)),
				temp1_1 = Textbox("PODS Connection Status: ")
			),
			spacerbox(size(3,3)),
			Test_DB_Connection.button,
			spacerbox(size(3,3)),
			button box("           Cancel          ",currentwindow()<<close window)
		)
	)
);

//Expression to set up "Set Connection" button
//Takes input values, substitute Standard SQL syntax DB.SQL with input values and execute open connection command
//Flag out "Successful" or "Unsuccessful" as results
Test_DB_Connection.button = expr(
	button box("   Set Connection   ",
		Input.DSN_Name = TEB_DSN_Name << Get Text();
		Input.DSN_Desc = TEB_DSN_Desc << Get Text();
		Input.DB_Name = TEB_DB_Name << Get Text();
		
		DB.SQL_Updated = Substitute(DB.SQL,"DSN_Name",Input.DSN_Name,"DSN_Desc",Input.DSN_Desc,"DB_Name",Input.DB_Name);
		try(db = Create database connection (DB.SQL_Updated),
			print("Unable to connect to Database")
		);
		try(db << get type;
			try(temp1_1 << delete);
			temp1_1 = hlistbox(
				Textbox("PODS Connection Status: "),
				Textbox("Successful", << font color(4)) 
			);
			vb1_1 << append (temp1_1);
			,
			
			try(temp1_1 << delete);
			temp1_1 = hlistbox(
				Textbox("PODS Connection Status: "),
				Textbox("Unsuccessful", << font color(3)) 
			);
			vb1_1 << append (temp1_1);
			,
		);
	)
);


nw1 = new window("PODS Connection Setup",
	showtoolbars(0), showmenu(0),
	DB_Connection.UI;
);

Re: Create Database Connection Error

Craige_Hales — Thu, 02 Dec 2021 09:52:17 GMT

Try some variation of this.

bi = Batch Interactive( 1 );
Try(
    db = Create Database Connection( DB.SQL_Updated );
    If( Is Missing( db ),
        Print( "unable..." ) // I think this is the right way to notice the error
    );
,
    Print( "Unable to connect to Database" ) // I think this may not happen?
);
Batch Interactive( bi );

You can use logcapture() if the log message is an issue.

I think using the returned value from the first batchinteractive call to pass to the second is the right way to push/pop the flag. I think JMP is internally pushing "interactive" for the flag when a script is run from a button.

Note from @Wendy_Murphrey . Searching the community will produce more history about batch interactive.

Re: Create Database Connection Error

Djtjhin — Thu, 02 Dec 2021 10:19:33 GMT

Thanks @Craige_Hales. I don't understand what exactly "Batch Interactive ()" does, but it solves the issue. :D

Re: Create Database Connection Error

Craige_Hales — Thu, 02 Dec 2021 12:47:55 GMT

Ah, sorry, left that out. If JMP believes it is running in Batch mode (ancient terminology about batch processing data, without a terminal for user input) then those alerts are written to the log and a default response is made. Otherwise, in Interactive mode, JMP opens a modal dialog to get the response. Most of the time, if a button was pressed to start some JSL, the interactive behavior is correct. And if a script is submitted (over lunch perhaps) you'd rather not come back to an "ok?" dialog. Button scripts tend to be short, so the heuristic tends to work.

Re: Create Database Connection Error

Mark_Bailey — Thu, 02 Dec 2021 15:05:09 GMT

I suggest that, in the future, you first connect interactively to prove that a connection is possible. An added benefit is that JMP will write a Source table script with the correct JSL for the same result. You can copy this script to your own instead of writing and debugging your own,

Re: Create Database Connection Error

Djtjhin — Fri, 03 Dec 2021 00:59:50 GMT

Hi @Mark_Bailey. Yep, you're right. Definitely a good starting point to leverage the Source table script. I was exploring other ways as I might need to expand the usage of this script to other potential users within my organizations which may not be as familiar with JMP, let alone scripting. Thanks for the advise :)