topic Re: How can I reload data from a SQL table? in Discussions

How can I reload data from a SQL table?

juvethski — Mon, 06 Nov 2017 14:46:47 GMT

Hi, first of all, I am so glad to find this forum and hopefully in the future it will be my turn to help other new users (like me). I am very new to JMP, and I inherited a set of JMP files and scripts that I need to ramp up on very quickly.

I have a file that has ODBC connection to a SQL server and accesses a SQL table.

Here is the SQL:

select a.*
from table1 a
join table2 g
on a.col1=g.col1
where [Criteria is met]

This is the JSL:

Open Database("DRIVER=SQL Server;SERVER=server\server;UID=;APP=2007 Microsoft Office system;WSID=comp_name;DATABASE=database;Trusted_Connection=Yes", "select *
from table1 a
join table2 g
on a.col1=g.col1
where [Criteria is met]

In the online help, it says that there will be a SOURCE that can be executed to re-import the data:

[...]

When you import data from an ODBC database, within the data table, note the following:

There is a script called Source that you can run to re-import and refresh the data.

A table variable is added that might contain user ID and password information. There is a JSL-only preference that can be set to prevent including this possibly sensitive information. See the Scripting Guide for more details.

But when I try to run the script, it asks or looks for a file. Why is it looking for a file?

Thanks in advance.

Re: How can I reload data from a SQL table?

afterword — Thu, 29 Nov 2012 18:39:57 GMT

I'd recommend first connecting to the database manually to make sure you have access from your machine.

In the JMP menu, select "File -> Database -> Open Table..." That will open a dialog box called "Database Operations." Choose "Connect" and check the "Machine Data Source" or "File Data Source" tabs for the connection that matches the information in your "Open Database" string that you pasted above.

If you can successfully connect through this dialog, then connecting via script should be easy, but I suspect either the database or the connection parameters have changed since you inherited the file.

Also, I'm assuming that you intentionally obscured the connection parameters and SQL query above. I hope no one created a database called "database" on a computer called "comp_name" with tables called "table1" and "table2"!

Re: How can I reload data from a SQL table?

juvethski — Thu, 29 Nov 2012 18:58:53 GMT

Thanks for your reply, yes I did changed those parameters, for reasons I'm sure you know . I ended up doing the long route, created another file in jmp and then made the updates within jmp. But I still prefer the right way of doing it (I think), which is just to refresh the jmp table with the ones from the sql table. Thanks.