connect to SQL databse programmatically

[#1]

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

[#2 · Accepted solution]

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

[#3]

Thanks,

that worked perfectly.

Ciao,

Paolo

[#4]

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

[#5]

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

[#6]

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?

[#7]

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.

[#8]

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

[#9]

@jmpbeginner ,

your response is actually very valuable.

Thank you very much!

Ron

[#10]

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