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
paologrigis
Level II

connect to SQL databse programmatically

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

11 REPLIES 11
pmroz
Super User

Re: connect to SQL databse programmatically

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.  

wu
wu
Level III

Re: connect to SQL databse programmatically

Thank for your sharing, it is very helpful !