Database Access

JMP supports ODBC access to SQL databases through JSL with the Open Database command.

dt = Open Database(

"Connect Dialog" | "DSN=...", // data source

"sqlStatement" | "dataTableName" | "SQLFILE=...", // SQL statement

Invisible, //Optional keyword to hide the table upon importing it

"outputTableName" // new table name

);

Note: Database table names that contain the characters $# -+/%()&|;? are not supported.

The first argument is a quoted connection string to specify the data source. It should be either of the following:

•	"Connect Dialog" to display the ODBC connection dialog box

•	"DSN=" and then the data source name and any other information needed to connect to the data source.

For example:

"DSN=dBASE Files;DBQ=C:/Program Files/SAS/JMP/<version number>/Samples/Import Data;"

The second argument is a double-quoted string that can be one of the following:

1.	An SQL statement to execute. For example, the second argument might be a SELECT statement in a quoted string like the following:

"SELECT AGE, SEX, WEIGHT FROM BIGCLASS"

2.	The name of a data table. In this case, the effect is an SQL "SELECT * FROM" statement for the data table indicated. For example, Open Database would in effect execute the statement "SELECT * FROM BIGCLASS" if you specify this for the second argument:

"BIGCLASS"

3.	"SQLFILE=" and a path to a text file containing an SQL statement to be executed. For example, with the following argument, JMP would attempt to open the file mySQLFile.txt from the C:\ directory and then execute the SQL statement in the file.

"SQLFILE=C:\mySQLFile.txt"

The optional Invisible argument creates a hidden data table. Hidden data tables remain in memory until they are explicitly closed, reducing the amount of memory that is available to JMP. To close the hidden data table, call Close(dt), where dt is the data table reference.

The optional outputTableName argument is optional and specifies the name of the output table to be created, if any. Note that Open Database does not always return a data table. The return value might be null. Whether it returns a data table depends on the type of SQL statement executed. For example, a SELECT statement would return a data table, but a DROP TABLE statement would not.

To save a table back to a database through JSL, send the data table reference a Save Database( ) message:

dt << Save Database("connectInfo", "TableName");

The first argument works the same way as it does in Open Database. Note that some databases do not allow you to save a table over one that already exists. If you want to replace a table in a database, use a DROP TABLE SQL statement in an Open Database command:

Open Database ("connectinfo", "DROP TABLE TableName");

The following script opens a database with an SQL query, saves it back to the database under a new name, and then deletes the new table.

dt = Open Database("Connect Dialog", "SELECT age, sex, weight FROM \!"Bigclass$\!"", "My Big Class");

dt << Save Database("Connect Dialog", "MY_BIG_CLASS");

Open Database("Connect Dialog", "DROP TABLE BIGCLASS.MY_BIG_CLASS");

Note: When you import data from an ODBC database, a table variable is added that can contain user ID and password information. To prevent this from happening, set the following preference: pref(ODBC Hide Connection String(1)); or go to File > Preferences > Tables and select the ODBC Hide Connection String option.See Using JMP for details.

Creating a Database Connection and Executing SQL

You can use the following functions to handle more complex database operations:

Create Database Connection("Connection String With Password");

Execute SQL(db, "SQL statement", <invisible>, <"New Table Title">);

Close Database Connection(db);

Using these three functions, you can open a connection, call Execute SQL several times, and then close the connection. Create Database Connection returns a handle for use in Execute SQL and Close Database Connection.

Depending on the SQL submitted, a table might or might not be returned. A SELECT statement typically returns a JMP table. INSERT INTO would not return a table, because it is modifying one in the database.

Example

Open a connection to your database:

dbc = Create Database Connection(

"DSN=dBASE Files;DBQ=C:/Program Files/SAS/JMP/<version number>/Samples/Import Data/;"

);

Execute one or more SQL statements using this connection:

dt = Execute SQL(dbc,

"SELECT HEIGHT, WEIGHT FROM Bigclass", "NewTable"

);

When you are finished, close your connection.

Close Database Connection(dbc);