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]

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.  

[#3]

Thank for your sharing, it is very helpful !