JMP supports ODBC access to SQL databases through JSL with the Open Database function.
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: The characters $# -+/%()&|;? in a database table name must be quoted with square brackets [].
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 Select Data Source window (Windows) or Choose DSN window (macOS).
• "DSN=" and then the data source name and any other information needed to connect to the data source. On Windows, the data source name is shown in the name column of the User DSN or System DSN tab of the ODBC Data Source Administrator. On macOS, the DSN is shown in the ODBC Manager or iODBC Driver Manager. The rest of the string depends on the ODBC data source.
For example:
"DSN=dBASE Files;DBQ=C:/Program Files/SAS/JMP/16/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"
The SQL must conform to the SQL that the data source supports. That is, a table named "Big Class" would need to be appropriately quoted because of the space between “Big” and “Class” (if whitespace is supported). The method for quoting depends on the data source and is usually done with ", ’, ‘, or [] characters.
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" );
Note: JMP 13 or later can save tables with spaces and mixed case in the names if the data source supports them. Most data sources do support spaces, except Apache Hive and Apache Hadoop. Mixed case is preserved, but for the most part, SQL is case insensitive.
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)). You can also select the File menu (Windows) or JMP menu (macOS), Preferences > Tables, and then select ODBC Hide Connection String. See Preferences for Data Tables in Using JMP.