cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
JacobPardew
Level II

Database Connection with login prompt

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

8 REPLIES 8
ian_jmp
Staff

Re: Database Connection with login prompt

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));	
);
JacobPardew
Level II

Re: Database Connection with login prompt

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

ian_jmp
Staff

Re: Database Connection with login prompt

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);

 

pmroz
Super User

Re: Database Connection with login prompt

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

 

 

hogi
Level XI

Re: Database Connection with login prompt

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 Stringrunning Execute SQL(my_dbc, ...) and getting the password from the table scripts.



 

jthi
Super User

Re: Database Connection with login prompt

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

-Jarmo
JacobPardew
Level II

Re: Database Connection with login prompt

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\!""
));

 

 

ian_jmp
Staff

Re: Database Connection with login prompt

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;"