topic Re: connect to SQL databse programmatically in Discussions

connect to SQL databse programmatically

paologrigis — Fri, 04 Nov 2011 09:46:29 GMT

Hi,

I am trying to connect to an SQL database in a script using the

Open Database command.

Now, when I work interactively, I create a "Machine Data Source" from

the database conect widget. When I connect, a window opens up asking

for username and password. When I insert them, I can succesfully connect

to the database and import tables into JMP.

So I would like to try the same from a script. Unfortunately, I don't see

where I would insert the username and password. When I try to connect

I get the following error message in the log window.

[Microsoft][ODBC SQL Server Driver][SQL Server]Login failed for user ''.

I guess this is not surprising since I didn't specify how to authenicate...

The code is

DataBase = Open Database(

"DSN=myDatabase", // data source

"myTablename", //table

"JMPtable" // new table name

);

So how can I specify my authentication details?

Ciao,

Paolo

connect to SQL databse programmatically

pmroz — Fri, 04 Nov 2011 13:33:35 GMT

Paolo,

You need the proper DSN connect string. To get this, run the command File > Database > Open Table. Click Connect and select your ODBC source. You will be prompted for a username and password. After entering that information, a list of schemas and tables will appear. Select any (small) table and click Open Table. After that is read in click Disconnect. The JMP dataset that ges created will have two table variables: JSL and SQL. If you click on them you will see the connect string and the SQL statement that was used. You can double-click on the JSL variable to see it's value - it will look something like this:

Open Database("DSN=ODBC Databasexxx;UID=xxxx;PWD=xxxxxxx;DBQ=mydatabasexxxx;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;",

"SELECT * FROM myschema.mytable");

My example shows a typical Oracle database dsn string. Yours will look slightly different for SQL Server. Save this string - to use it programmatically you would do something like this:

dsn_string = "DSN=ODBC Databasexxx;UID=xxxx;PWD=xxxxxxx;DBQ=mydatabasexxxx;DBA=W;" ||

"APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;" ||

"BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;";

sql_string = "select * from myschema.othertable"

open database(dsn_string, sql_string, "other table");

Regards,

Peter

connect to SQL databse programmatically

paologrigis — Fri, 04 Nov 2011 14:10:03 GMT

Thanks,

that worked perfectly.

Ciao,

Paolo

connect to SQL databse programmatically

GoodMan — Fri, 04 Nov 2011 14:15:37 GMT

Dear Mr. Mroz,

This is Kun. I have joined this group.

Thanks for your reply into this database connection question. Here i also used JMP to connect some MS Access database files by

File > Database > Open Table.

. I wonder the password and ID you are discussing is for an access file or it is for a sever where the database files were saved? If the PW and ID is for a Access file, where i can set these parameters?

Kun

connect to SQL databse programmatically

pmroz — Fri, 04 Nov 2011 19:27:05 GMT

Hi Kun,

You would use whatever is listed for the JSL variable in your dataset that resulted from reading an Access table. You don't really "set parameters" for userid and password - you just build a connection string and use it. I don't know if Access has the capabillity of using usernames and passwords. Here's a connection string that I used for an Access database. Note that there is no username/password.

// Data Source

dsn_string = "DSN=MS Access Database;" ||

"DBQ=C:\Discovery2011\JMP Surveillance1.accdb;" ||

"DefaultDir=C:\Discovery2011;DriverId=25;" ||

"FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;";

connect to SQL databse programmatically

GoodMan — Sat, 05 Nov 2011 02:40:50 GMT

Mr. Mroz,

i did some research into user/password set from the side of MS Access.Basing the current information, we did have a way to set password for a MS Access file. You can visit the following link to get some ways.It seems i can not find way to set username for access file.

http://office.microsoft.com/en-us/access-help/CH001010607.aspx?CTT=97

I have tested add 123 as password for the Northwind.mdb. And try to connect it with mdb. If i operate with menu, a prompt window will ask me ID/PW. after i insert"123". i can get the database file and open table.Then i disconnect and get the following JSL. You can find there are PWD and UID information.

Open Database(

"DSN=MS Access Database;DBQ=D:\My Business\Supplier\IT\Access\Northwind.mdb;DefaultDir=D:\My Business\Supplier\IT\Access;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=123;UID=admin;",

"SELECT * FROM Customers"

)

After i close all table, i run it, i find i can open the password protected access table.But if i can not obtain that window to specify the authentication process.

I have tested to change PWD=123 to ***. It does not work.

Can you give me some input?

connect to SQL databse programmatically

pmroz — Mon, 07 Nov 2011 14:32:48 GMT

I haven't used passwords with Access databases. If you don't hear from someone on this forum I'd suggest contacting JMP tech support.

Re: connect to SQL databse programmatically

jmpbeginner — Tue, 25 Mar 2014 18:16:35 GMT

"The JMP dataset that gets created will have two table variables: JSL and SQL"

Solved my own problem, but thought i would share here as well:

If you do not see the two table variables "JSL" and "SQL" or "Source", they are being hidden due to preferences.

run this JSL script to solve the problem:

pref(ODBC Hide Connection String(0));

Re: connect to SQL databse programmatically

wu — Thu, 13 Jun 2019 01:45:57 GMT

Hi,Mr. Mroz,
Appreciate your sharing.
I am learning JMP connection to database; based on your script, I am able to open database.
My next question is, how to insert a line (lines) into the existing tables in the database.
I could get the following:
(1). existing_col_names=Current data table() << get column name( string);
(2). get new values (in matrix ) by new_values= dt << Get As Matrix();
How to append the new_values into the existing table in database?

Best Regards,
WGan

Re: connect to SQL databse programmatically

pmroz — Fri, 14 Jun 2019 18:46:27 GMT

Here's a simple example using Big Class that should get you started. It inserts all the rows into a database table called big_class. Note the use of evalinsert - makes for cleaner looking strings.

dt = open("$sample_data\big class.jmp");
dbc = create database connection("<proper connection parameters>");
for (i = 1, i <= nrows(dt), i++,
	one_name = dt:name[i];
	one_age  = dt:age[i];
	one_sex  = dt:sex[i];
	one_ht   = dt:height[i];
	one_wt   = dt:weight[i];
	sql = evalinsert(
"INSERT INTO big_class(name, age, sex, height, weight)
 VALUES('^one_name^', ^one_age^, '^one_sex^', ^one_ht^, ^one_wt^)");
	print(sql);
	execute sql(dbc, sql);	// Does an implicit commit
);
close database connection(dbc);

If you have a lot of rows to insert consider doing multiple inserts at a time. In Oracle you can use INSERT ALL to insert multiple rows at a time.

Re: connect to SQL databse programmatically

wu — Fri, 21 Jun 2019 03:43:04 GMT

Thank for your sharing, it is very helpful !

Re: connect to SQL databse programmatically

ron_horne — Sat, 12 Nov 2022 22:32:49 GMT

@jmpbeginner ,

your response is actually very valuable.

Thank you very much!

Ron