Database Connection with login prompt

[#1]

Hi all,

I have been working on a script which connects to a database system and then pulls some data.

The script works fine using my credentials however as these credentials change every couple of months due to the password having to be updated, I am often having to change the script. We are also looking at pushing this script down the route of validation, which means that we wouldn't be able to change the script once it is validated. I want to therefore add a prompt so that anybody could use this script and it would ge through validation.

Below is the script currently (the UID and password are the details that would need to be inputted):

Dt1=Open Database(
"DSN=MACPAC;SYSTEM=MEDIUK;UID=INSERT;PASSWORD=INSERT;DBQ=MPDAT10;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;",
       "Select ixplot as batchid,
ixppn as product_part_no,
ixrecq as batch_size,
ixlevl as macpac_level,
ixcpn as comp_part_no,
ixcpde as comp_desc,
ixclot as comp_lot_no,
ixcmpo as comp_order_no,
ixissq as comp_qty,
ixvend as vendor_code,
ixvlot as vendor_lot_no,
ixseqn as sequence        
FROM \!"MPDAT10\!".\!"ICM860P\!""
)

If somebody could help me in finding a way to add a prompt then that would be great.

Thanks in advance,

Jacob

[#2]

Probably something like this:

NamesDefaultToHere(1);

nw = NewWindow("Cooext To Database",
		PanelBox("Input your credentials",
			LineUpBox(NCol(2),
				TextBox("User:"),
				teb1 = TextEditBox("", << setWidth(200)),
				TextBox("Password:"),
				teb2 = TextEditBox("", << setWidth(200), << passwordStyle(1))
				);
			),
		LineUpBox(NCol(2),
			ButtonBox("OK", OKscript),
			ButtonBox("Cancel", nw << closeWindow)
			);
		);

OKScript =
Expr(
	nw << closeWindow;
	user = teb1 << getText;
	pass = teb2 << getText;
	// DB connection info . . .
	Speak("User was "||user, wait(1));
	Speak("Password was "||pass, wait(1));	
);

[#3]

Hi Ian,

Thanks for getting back to me.

Do you know how you would be able to use this new prompt box but to sandwich this into my current code? So that the box appears, we then input the ID and password and then that is put into the line of code where we are then connecting to the database?

I have tried to use "teb1" and "user" both with and without quotations. Surely I must be a missing a little something which means that it isn't working?

I see that the prompt works for the passowrd however I cannot work out how to get this to then put the password into the database query.

NamesDefaultToHere(1);
nw = NewWindow("Cooext To Database",
PanelBox("Input your credentials",
LineUpBox(NCol(2),
TextBox("User:"),
teb1 = TextEditBox("", << setWidth(200)),
TextBox("Password:"),
teb2 = TextEditBox("", << setWidth(200), << passwordStyle(1))
 
);
),
LineUpBox(NCol(2),
ButtonBox("OK", OKscript),
ButtonBox("Cancel", nw << closeWindow)
 
);
);
OKScript =
Expr(
nw << closeWindow;
user = teb1 << getText;
pass = teb2 << getText;
// DB connection info . . .
Speak("User was "||user, wait(1));
Speak("Password was "||pass, wait(1));
 
);
Dt1=Open Database(
"DSN=MACPAC;SYSTEM=MEDIUK;UID=(HOW TO GET UID HERE?);PASSWORD=(HOW TO GET PASSWORD HERE?;DBQ=MPDAT10;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;",
"Select ixplot as batchid,
ixppn as product_part_no,
ixrecq as batch_size,
ixlevl as macpac_level,
ixcpn as comp_part_no,
ixcpde as comp_desc,
ixclot as comp_lot_no,
ixcmpo as comp_order_no,
ixissq as comp_qty,
ixvend as vendor_code,
ixvlot as vendor_lot_no,
ixseqn as sequence
FROM \!"MPDAT10\!".\!"ICM860P\!""
);

 Thanks,

Jacob

[#4]

I tend to like a non-modal UI, so was assuming that your code would go into 'OKscript' above.

If you want to put the dialog inline with existing code, you need to make a few small changes.

Be aware that there are other (better?) ways to build the DB connection string.

NamesDefaultToHere(1);

// Your first code block
Speak("First code block", wait(1));

// Using a 'modal' window delays execution of subsequent code until input is given
nw = NewWindow("Connect To Database", << Modal, << returnResult,
		PanelBox("Input your credentials",
			LineUpBox(NCol(2),
				TextBox("User:"),
				teb1 = TextEditBox("", << setWidth(200)),
				TextBox("Password:"),
				teb2 = TextEditBox("", << setWidth(200), << passwordStyle(1))
				);
			),
		LineUpBox(NCol(2),
			ButtonBox("OK"),
			ButtonBox("Cancel")
			);
		);

// Your second code block
// Stop if the user canecls . . .
if(nw["Button"] == -1, Speak("User Cancelled"); Stop());
// . . . else get the values the user gave
user = nw["teb1"];
pass = nw["teb2"];
DBconnectionString = "User is "||user||" password is "||pass;
Speak(DBconnectionString);

[#5]

Would your database people be happy with this sort of solution?

• Create an application account on the database that has the necessary privs, whose password never expires.
• Create an encrypted jsl file that defines the function GET_DBC. 
• The GET_DBC function calls CREATE DATABASE CONNECTION using the proper dsn string, and returns the database connection.
• In your main program you would make calls to EXECUTE SQL using the database connection
• When you're all done call CLOSE DATABASE CONNECTION.

Here's a simple version of GET_DBC.

/*
File Name: 		Get_DBC.JSL
Description:    Function to get database connection string
*/
get_dbc = function({}, {default local},

	my_dbc = "";
	dsn_string = "Driver={Oracle in OraClient12Home1};Dbq=MY_TNS_ENTRY;UID=USERNAME;PWD=PASSWORD;";
	my_dbc = create database connection(dsn_string);

// Return the connection to Oracle
	my_dbc;
);	// end Get_DBC.JSL

[#6]

On my system (v17.1), looking at the data sets which store the query results, there are tables scripts Source, Update from DB, Save to DB with the password in plain text.

The generation of such Tables scripts can be deactivated in the preferences via Tables > Hide ODBC Connection String.
Jmp Note 621050

Bis is there an option to prevent a user from deactivating Hide ODBC Connection String, running Execute SQL(my_dbc, ...) and getting the password from the table scripts.

[#7]

If I remember correctly this has some ideas How to handle user password information in JSL ( ODBC connection strings ) 

[#8]

Hi Ian,

Thanks again for replying to my question. I see that this produces the dialog box and saves the password that I input but I am still unsure as to how I am then suppose to feed this into the code below when connecting to the database.

Sorry for being a pain, you have been very helpful! 

NamesDefaultToHere(1);
 
// Your first code block
Speak("First code block", wait(1));
 
// Using a 'modal' window delays execution of subsequent code until input is given
nw = NewWindow("Connect To Database", << Modal, << returnResult,
PanelBox("Input your credentials",
LineUpBox(NCol(2),
TextBox("User:"),
teb1 = TextEditBox("", << setWidth(200)),
TextBox("Password:"),
teb2 = TextEditBox("", << setWidth(200), << passwordStyle(1))
 
);
),
LineUpBox(NCol(2),
ButtonBox("OK"),
ButtonBox("Cancel")
 
);
);
// Your second code block
// Stop if the user canecls . . .
if(nw["Button"] == -1, Speak("User Cancelled"); Stop());
 
// . . . else get the values the user gave
user = nw["teb1"];
pass = nw["teb2"];
DBconnectionString = "User is "||user||" password is "||pass;
Speak(DBconnectionString);
MACPAC_CHARTGEN = Function( {},/* Tell MACPAC to open the materials data from a certain table*/
Dt1=Open Database(
"DSN=MACPAC;SYSTEM=MEDIUK;UID=||user||;PASSWORD=||pass;DBQ=MPDAT10;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;",
"Select ixplot as batchid,
 
ixppn as product_part_no,
ixrecq as batch_size,
ixlevl as macpac_level,
ixcpn as comp_part_no,
ixcpde as comp_desc,
ixclot as comp_lot_no,
ixcmpo as comp_order_no,
ixissq as comp_qty,
ixvend as vendor_code,
ixvlot as vendor_lot_no,
ixseqn as sequence
FROM \!"MPDAT10\!".\!"ICM860P\!""
));

[#9]

The double vertical bars ('||') tell JMP to concatenate two character strings. So try:

"DSN=MACPAC;SYSTEM=MEDIUK;UID="||user||";PASSWORD="||pass||";DBQ=MPDAT10;DFTPKGLIB=QGPL;LANGUAGEID=ENU;PKG=QGPL/DEFAULT(IBM),2,0,1,0,512;QRYSTGLMT=-1;"