topic Re: How to create database connection reference to a SQL server? in Discussions

How to create database connection reference to a SQL server?

lukasz — Mon, 11 May 2020 16:22:07 GMT

Hello Everybody,
I have an issue with creation of variable (or reference) after connecting to a database. I wanted to create variable conn that I could use later to run different queries. At the beginning, however, I needed to create 2 connections since I needed data from different databases, but on the same SQL server (the same user name and password are required). For both connections I need to provide twice password for a given user name. I would like to provide it once and preferably during creation of the conn variable.
Later, I wanted to execute INSERT query to update only one cell by using conn variable created earlier and without any success. JMP log says that there was problem with establishing connection to database with "engineer" user name and indicates the line where I am creating conn variable:
[Microsoft][ODBC SQL Server Driver][SQL Server] Error during the login for the user "engineer".
However, with the same user name I can successfully execute both "New SQL Queries" (after providing password for each query).
The evalinsert function seems to work as desired.

I would appreciate for hints how to solve the issue and indicating what I am missing. Best regards.

conn = Create Database Connection ("ODBC:Description=Server_Database;DRIVER=SQL Server;SERVER=Server;UID=engineer;PWD=%_PWD_%;APP=JMP;WSID=DDPC0283;DATABASE=Data_1;");

Param_Data = New SQL Query(
	Version( 130 ),
	//conn; //providing connection reference here instead of Connection (below) returns an error	
	Connection(
		"ODBC:Description=Server_Database;DRIVER=SQL Server;SERVER=Server;UID=engineer;PWD=%_PWD_%;APP=JMP;WSID=DDPC0283;DATABASE=Data_1;"
	),
	QueryName("Data1"),
	Select(
		Column( "Param1", "t1"),
		Column( "Param2", "t1"),
	),
	From( Table( "ParamData", Schema( "pi" ), Alias( "t1" ))),
);

Process_Data = New SQL Query(
	Version( 130 ),
	Connection(
		"ODBC:Description=Server_Database;DRIVER=SQL Server;SERVER=Server;UID=engineer;PWD=%_PWD_%;APP=JMP;WSID=DDPC0283;DATABASE=Data_2;"
	),
	QueryName( "Data2" ),
	Select(
		Column( "Process1", "t2"),
		Column( "Process1", "t2"),
	),
	From( Table( "ProcessData", Schema( "dbo" ), Alias( "t2" ))),
);

//sql query
sql_update = evalinsert(
			"
			UPDATE pi.ParamData
			SET Param1 = '^combined_params^'
			WHERE Process1 = '^selected_process^'
			"				
);
write(sql_update);
execute sql (conn, sql_update);

Re: How to create database connection reference to a SQL server?

pmroz — Mon, 11 May 2020 18:02:50 GMT

It appears that the query builder uses it's own database connection. This code using EXECUTE SQL might be simpler, and only requires one connection to the database:

conn = Create Database Connection ("ODBC:Description=Server_Database;DRIVER=SQL Server;SERVER=Server;UID=engineer;PWD=%_PWD_%;APP=JMP;WSID=DDPC0283;DATABASE=Data_1;");

sql1 = 
"SELECT t1.Param1, t1.Param2
   FROM pi.ParamData t1";

param_data = execute sql(conn, sql1, "Data1");

sql2 = 
"SELECT t2.Process1, t2.Process2
   FROM dbo.ProcessData t2";

process_data = execute sql(conn, sql2, "Data2");

//sql query
// Need to load values in for combined_params and selected_process variables
sql_update = evalinsert(
			"
			UPDATE pi.ParamData
			SET Param1 = '^combined_params^'
			WHERE Process1 = '^selected_process^'
			"				
);
write(sql_update);
execute sql (conn, sql_update);

close database connection(conn);

Re: How to create database connection reference to a SQL server?

lukasz — Mon, 11 May 2020 20:02:44 GMT

Hello pmroz,

thank you for your support! It works perfectly and it is much simpler. However, there was still problem with creating conn variable. What I did, I removed part UID=engineer from the parameter chain, since I think the authentication is based on logging to the internal network and it remembers the previous user name settings. Additionally, I removed the name of the database at the end of the chain, since it is provided in the query. Best regards.

conn = Create Database Connection ("ODBC:Description=Server_Database;DRIVER=SQL Server;SERVER=Server;PWD=%_PWD_%;APP=JMP;WSID=DDPC0283;");