This section covers how to access databases from JMP.
Query Builder is the preferred method for selecting and importing data from a SQL database without writing SQL statements. You can preview the data before importing it into a data table. Share your queries so that other users can customize and run the queries.
After you create the query, you can run the query with a JSL script. Use Include() to include the query in your script without opening it.
query = Include( "$DOCUMENTS/Airline.jmpquery" );
query << Run Foreground( );
Instead of running the query in the foreground, you can use Run Background() to run the query in the background or Run() to use the Query Builder preference for running queries. By default, queries run in the background.
See SQL Messages in the JSL Syntax Reference for more information about query messages.
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\17\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(Hide ODBC Connection Strings(1)). You can also select the File menu (Windows) or JMP menu (macOS), Preferences > Tables, and then select Hide ODBC Connection Strings. See Preferences for Data Tables in Using JMP.
You can use the following functions to handle more complex database operations:
db = 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.
Open a connection to your database:
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_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 );
You might want to create a database connection without selecting a table. The script would open the Database Open window with the connection already showing. We strongly recommend that you encrypt this script. Here’s an example:
dbc = Create Database Connection(
"DSN=dBASE Files;DBQ=$SAMPLE_IMPORT_DATA/;UID=MyDatabase;pwd=MyPassword");
Main Menu( "Open Table" );
Note: Execute SQL() creates a connection string that includes the cleartext password in the resulting data table. We encourage you to create the SQL query in Query Builder and then run the query in JSL. Then the password is not included in the data table.