Open Data from a Database
After you connect to the ODBC database and select a table to import, the data is opened in a data table. Several table scripts are included in the data table.
• Run the Source script to reconnect to the database.
• Run the Update from DB script to re-import and refresh the data. If the database table has the same number of columns as the original JMP table, the values in that existing JMP table are updated in place. However, if the number of columns is not the same, then a new data table opens where the updated data is stored.
• Run the Save to DB script to save the data table to the database. The existing data in the database is replaced. This script might contain the user name and password. The preference called Hide ODBC Connection Strings can be set to prevent including this possibly sensitive information. Select File > Preferences > Tables (Windows) to find the preference (or JMP > Preferences > Tables on macOS).
To import data from a database
1. Select File > Database > Open Table.
The Database Open Table window appears (Figure 3.67).
2. If you are already connected to the database, select it in the Connections box. Follow the steps in Connect to a Database.
The Connections box lists data sources to which JMP is connected. The Schemas - Tables box lists schemas for those databases that support them.
Figure 3.67 Database Open Table Window
Note: The Fetch Procedures check box is disabled if the ODBC driver does not support fetching procedures.
3. If the desired data source is not listed in the Connections box, click New Connection to choose a data source. The method of choosing a data source depends on your operating system.
4. Select the desired data source in the Connections box. The tables list in the Tables box updates accordingly. The update might take a several seconds, depending on the number of tables and the speed of the connection to the database. If your database supports schemas, tables are loaded for the first schema in the list, and on other schemas as you click them.
5. Control which tables are listed by choosing the options in the Include in Table List group of check boxes.
Note: Different drivers interpret these labels differently.
User Tables When clicked, displays all available user tables in the Tables list. User tables are specific to which user is logged on to the computer.
Views When clicked, displays “views” in the Tables list along with all other file types that can be opened. “Views” are virtual tables that are query result sets updated each time you open them. They are used to extract and combine information from one or more tables.
System Tables When clicked, displays all available system tables in the Tables list. System tables are tables that can be used by all users or by a system-wide service.
Synonyms When clicked, displays all available ORACLE synonyms in the Tables list.
Sampling Enter the percentage of rows that you want to appear in the list of tables. Selecting this option speeds up queries in large databases. JMP uses the sampling method supported by the database. The check box is unavailable when the database does not support sampling.
6. Select the desired table from the Tables list.
Note: If you are connected to a dBase database, select the database folder to which you would like to connect. Individual files are grayed out and cannot be selected.
7. Click Open Table to import all the data in the selected table, or click Advanced to specify a subset of the table to be imported. Some databases require that you enter the user ID and password to access the data.
You might see a short delay when opening large tables. To see the status of all active ODBC queries, select View > Running Queries.
Note: If the data were previously exported to a database in JMP and contained an Expression column, the column will be imported as a Character column. Select Cols > Column Info and change the Data Type to Expression.