Connect to a Database using Data Connectors
Base configurations are available for a number of ODBC-compatible databases such as SQL Server, MySQL, and Excel. These can be customized and shared to provide multiple users in an organization with a single configuration for loading data into JMP.
JMP searches for existing ODBC configured DSNs (Data Source Names) found in the Windows registry or in ODBC.ini (on macOS) and converts them to JMP Data Connectors. This enables easy access to data sources without the need to manually recreate existing DSNs as configurations.
Establish a Connection
After installing the ODBC driver for your chosen data source, follow these steps to connect to the database in JMP.
1. Select File > Database > Data Connectors. The Connector box lists data sources that you have established a connection to.
2. Click New Connection.
The Select Data Connector window displays the New Connection dialog.
Figure 3.51 The New Connection Dialog
3. Select a data connector here from the registry of built-in connectors, add-in connectors supplied by your organization, and user-created connectors. Built-in connectors are prefixed with com.jmp, add-in connectors are prefixed with an add-in ID, and user-created connectors have no prefix. New connectors will need to be configured using the configuration options listed in Configure a Data Connector.
4. With a configured data connector selected, click Connect.
The New Connection dialog closes, returning you to the Select Data Connector window.
Import Data from a Database Using a Data Connector
To import data from a database
1. Select File > Database > Data Connectors.
The Select Data Connector window appears (Figure 3.52).
2. If you are already connected to the database, select it in the Connections box.
The Connectors box lists data sources to which JMP is connected. The Schemas - Tables box lists schemas for those databases that support them.
Figure 3.52 Select Data Connector Window Before Establishing Any Connections
3. If the desired data source is not listed in the Connectors box, click New Connection to choose a data connector. Follow the steps in Configure a Data Connector.
4. Select the desired data source in the Connectors box. The tables list in the Tables box updates accordingly. The update might take 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.
6. Select the desired table from the Tables list.
7. Click Open Table to import all the data in the selected table, or click Query Builder to specify a subset of the table to be imported. See Build SQL Queries in Query Builder.
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.
Configure a Data Connector
The Data Connector Editor window appears after selecting New in the Select Data Connector window. Configure options for connecting to a database in this window, such as login credentials and SQL formatting.
For the fields in the Connection group corresponding to ODBC connection string keys, setting them to No value corresponds to not specifying them in the connection string. Options that accept a Boolean accept the values ON and OFF.
Figure 3.53 The Data Connector Editor Window
Options in the Data Connector Editor Window
Base
Specifies the type of key to use to load a base configuration.
None
Provides a blank configuration with all fields at their default values.
ID
Provides a configuration associated with the provided ID.
File
Provides a configuration associated with the provided file path.
Spec
Provides a configuration associated with the provided specification. Specifications use a restricted form of JSL with the syntax KEY1 = VALUE1; KEY2 = VALUE2; where KEY refers to a keyword like ID or Server and VALUE refers to the ID or Server value of a data connection. Spec can be useful for testing configurations by accessing specific data sources using a named data connector configuration ID.
Connection
Options for connecting to a database.
Connection
Specifies an ODBC connection string.
Database
Specifies the name of the database for the connection.
Driver
Specifies the name of the driver.
Password
Specifies the password for access to the database.
Password Key
Specifies the connection string key to map to the value of the Password field.
Port
Specifies the port value.
Server
Specifies the name of the server instance.
Server Key
Specifies the connection string key to map to the value of the Server field.
Table
Specifies the name of the table to open.
User
Specifies the server login name.
User Key
Specifies the connection string key to map to the value of the Username field.
ODBC
Async Capable
(Boolean|AUTO) Specifies whether the driver supports asynchronous execution. If the AUTO option is selected, JMP will attempt to determine this itself.
Block Fetch
(Boolean) Specifies whether the driver supports block fetch.
Block Fetch Requires 8-Byte Alignment
(Boolean) Specifies whether the driver assumes that block fetch memory requires 8-byte alignment.
List Synonym Tables
Indicates whether table listings should include synonym tables according to the setting available in the Include in Table List box of the Select Data Connector window.
Always
Includes synonym tables regardless of user preference.
Never
Excludes synonym tables regardless of user preference.
Maybe
Includes synonym tables according to user preference.
List System Tables
Indicates whether table listings should include system tables according to the setting available in the Include in Table List box of the Select Data Connector window.
Some drivers, such as the Excel driver, considers all tables system tables. Using Always ensures those tables are listed.
Always
Includes system tables regardless of user preference.
Never
Excludes system tables regardless of user preference.
Maybe
Includes system tables according to user preference.
Scrollable Cursors
(Boolean|AUTO) Specifies whether the driver supports using scrollable cursors. If the AUTO option is selected, JMP will attempt to determine this itself.
JMP will use the SQLFetchScroll ODBC function instead of SQLFetch if they are supported.
SQLDescribeCol May Include Table Name
(Boolean) Specifies whether the column names returned by the SQLDescribeCol ODBC function may include the table name. Some drivers for cloud data sources do this. If the ON option is selected, JMP will parse the values returned by the SQLDescribeCol ODBC function.
Supports Schemas
(Boolean|AUTO) Specifies whether the driver supports schemas. If the AUTO option is selected, JMP will attempt to determine this itself.
Trailing Blanks in Values Matter
(Boolean) Specifies whether values that are the same up to trailing blanks should be considered different. Many databases consider such values to be equal, and JMP uses this when collecting distinct values.
Treat Success with Info as Fetch Error
(Boolean) Specifies whether to treat a “Success with Info” result as an error. When fetching data, some drivers will truncate data and return the result “Success with Info” rather than an error.
SQL
Options for formatting SQL commands.
Default Case
(None|Uppercase|Lower) Specifies how the database maps the case of unquoted identifiers. For example, SQL Server treats select colname from ... and select COLNAME from ... the same as the quoted select [COLNAME] from ... and so should have this option set to “Uppercase”. This option is used to know when to quote identifiers in SQL code to preserve the intended case.
Format Date Literal
(Function) Converts dates and times to SQL fragments using the provided function. If the string “Default” is provided, JMP will use its own conversion.
Functions provided should have the date or time as well as one of the following strings: “DATE”, “TIME”, or “TIMESTAMP”.
Formula Editor Mode
Determines what set of functions are shown by default in the formula editor when used to edit the formula for a computed column in Query Builder.
Generic
Specifies a set of generally applicable functions.
Access
Specifies a set of functions relevant to Access SQL.
DB2
Specifies a set of functions relevant to IBM Db2.
JMP
Specifies a set of functions relevant to JMP.
MySQL
Specifies a set of functions relevant to MySQL.
Oracle
Specifies a set of functions relevant to Oracle Database SQL.
SAS
Specifies a set of functions relevant to SAS.
SQL Server
Specifies a set of functions relevant to SQL Server.
All
Includes all functions available with the above options.
Generate Row Count SQL
(Function) Computes a SQL query using the provided function that determines the number of rows in the table. Alternatively, selecting the option No value will not generate a row count. Query Builder uses this to show the number of rows in a table.
If a function is provided, it must take two arguments, the schema and the table name, and return a string with the SQL query. The schema name may be empty if schemas are not in use. If the number of rows is unknown, the result set should be empty or have the value -1.
See the following for an example of a function.
// Returns a query like:// SELECT NUM_ROWS FROM all_tables WHERE// table_name = 'my_table' AND owner = 'my_schema';Function({schema, table}, {quote string},quote string = Function({str}, "'" || Substitute(str, "'", "''") || "'");
Concat( "SELECT NUM_ROWS FROM all_tables WHERE table_name = ",quote string(table),
If(schema == "", "", " AND owner = " || quote string(schema)),
";")
);
Keywords
(Function) Specifies strings to be considered as keywords rather than identifiers in SQL code. This option is used to determine when to quote identifiers to avoid their interpretation as keywords.
Quote Type
(Double (“id”)|Single ('id')|SAS ('id'n)|Brackets ([id])|Backticks (‘id‘)) Specifies the type of escape characters and quotation marks used for identifiers.
Supports Null With In
(Boolean) Specifies whether NULL can be used in the list of an IN test.
This example shows the type of code produced with the ON option:
col IN ("val1", "val2", NULL)
This example shows the type of code produced with the OFF option:
col IS NULL OR col IN ("val1", "val2")
Trailing Semicolon
(Boolean) Specifies whether the driver handles SQL queries that end in a semicolon. This does not directly affect the SQL code generated by Query Builder.
Unquoted Name Regex
(Regular expression) Specifies when to quote identifiers in SQL code using a provided regular expression that matches names usable as unquoted names in SQL code, not excluding keywords. The default regular expression is ^[A-Za-z_][A-Za-z_0-9]*$, matching exactly a letter or underscore followed by zero or more letters, underscores, or digits.
SQL Aggregation Functions
Options for specifying SQL aggregate functions. Each option’s is set to Has value with a string providing the function or set to No value indicating no support.
For example, Average Distinct Function has the default string “AVG(DISTINCT ”, which is used to generate SQL code that computes an average of distinct values like SELECT AVG(DISTINCT colname) FROM ....
SQL Joins
Specifies SQL Join options. These are used by Query Builder to determine what types of join to expect.
Requires Pairwise Joins
(Boolean) Specifies whether joins can only be performed pairwise, which requires parenthesization.
The following example shows a pairwise join that would be expected when this option is set to “ON”.
SELECT *FROM (( table1 t1 LEFT OUTER JOIN table2 t2 ON ( t1.col2 = t2.col2 ) ) LEFT OUTER JOIN table3 t3 ON ( t1.col3 = t3.col3 ) ) ;The following example shows a join that would be allowed when this option is set to “OFF”.
SELECT *FROM table1 t1 LEFT OUTER JOIN table2 t2 ON ( t1.col2 = t2.col2 ) LEFT OUTER JOIN table3 t3 ON ( t1.col3 = t3.col3 ) ;Supports Cross Join
(Boolean) Specifies whether cross joins are supported.
Supports Full Outer Join
(Boolean) Specifies whether full outer joins are supported.
Supports Inner Join
(Boolean) Specifies whether inner joins are supported.
Supports Left Outer Join
(Boolean) Specifies whether left outer joins are supported.
Supports Right Outer Join
(Boolean) Specifies whether right outer joins are supported.
SQL Sampling
Options for implementing SQL sampling to return rows sampled from a table.
Generate Sample Around SQL
(Function) Determines whether and how to wrap the core SQL query to implement sampling using a provided function. The function takes one argument, the sample settings, and returns either the empty string (indicating no wrapping) or a string containing the placeholder text ^QUERYSQL, indicating where to inject the core query.
The following example implements sampling the “First N” rows. It does this by returning a query template when the sample type is “First N” and an empty string otherwise.
Function({qss}, {},If(qss << Get Sample Type != "First N", "",
"SELECT * FROM (^QUERYSQL) WHERE ROWNUM <= "|| Char(qss << Get Sample Amount)
)
);
The sample settings object supports the following JSL messages:
<< Get Sample Amount
Returns the amount to sample. This is the number of rows or the percentage (between 0 and 100), depending on the sample type.
<< Get Sample Is Repeatable
Returns whether repeatable sampling is enabled.
<< Get Sample Seed
Returns the seed to use for repeatable sampling.
<< Get Sample Type
Returns the type of sampling to perform, one of the strings “Percent”, “N Rows”, or “First N”.
<< Get Use Block Sampling
Returns whether to use block sampling (1) or row sampling (0).
Generate Sample SQL
A function provided here determines the code used to implement sampling in a SQL query as well as its location within that query.
The function takes two arguments, the sampling settings (described above in Generate Sample Around SQL) and the context, which is a string. The context indicates where in the query JMP generates code.
The following contexts are available:
“Before Columns”
Returns values between SELECT and the column names.
“Before Alias”
Returns values between the first table name and its alias.
“After From”
Returns values between the query’s FROM clause and before additional clauses.
“After Joins”
Returns values between where the query’s JOIN clauses go and before additional clauses like WHERE.
“As Order By”
Returns values replacing the ORDER BY clause in the query. This is similar to “At End” below but should be used when the value includes an ORDER BY clause.
“At End”
Returns values at the end of the SQL query.
Supports Block Sampling
(Boolean) Specifies whether sampling can (or can only) be done by blocks – at a coarser level than individual rows, as with SAMPLE BLOCK in Oracle.
Supports Repeatable Sampling
(Boolean) Specifies whether sampling is reproducible through the use of a seed, as with SEED (123456) in Oracle.
Supports Row Sampling
(Boolean) Specifies whether sampling can (or can only) be done by rows.
Supports Sampling First N Rows
(Boolean) Specifies whether getting the first N rows of the result is supported for a user-given N, as with LIMIT 1000 in PostgreSQL.
Supports Sampling N Rows
(Boolean) Specifies whether getting N random rows from the result is supported for a user-given N, as with TABLESAMPLE (1000 ROWS) in SQL Server.
Supports Sampling N Rows and Order By
(Boolean) Specifies whether getting a number of random rows and ordering the output (with ORDER BY) can be used at the same time. This is "OFF" for databases like PostgreSQL, where getting a number of random rows is implemented with an operation like ORDER BY RANDOM() LIMIT 1000, which makes the ORDER BY clause unavailable for its usual purpose.
Supports Sampling Percent
(Boolean) Specifies whether getting the sampling percent of the result is supported for a user-given N, as with TABLESAMPLE (10 PERCENT) in SQL Server.
Test
Attempts to connect using the given values and displays a message box confirming if the test was successful.
Save Copy
After providing an ID, saves the current state of the editor and registers it in the list of available connectors in the Select Data Connector window with said ID. See Establish a Connection.
Write to Log
Writes the current state of the editor to the log. The text written to the log can be used to create a configuration file.
Share a Data Connector Configuration
Configured data connectors can be shared and used by multiple users even on different operating systems by saving the connector as a JMP add-in.
1. Select File > Database > Data Connectors.
2. Click the red triangle in the Connectors field.
3. Select Share Connectors.
4. Add each connector you want to share from the connector registry to the Selected Connectors field by double-clicking it or selecting it and clicking the > button.
5. Select Save in the Save Add-In File As window.
6. The data connector is saved as a JMP add-in file.
Install a Shared Data Connector Add-In
Shared data connectors can be installed and managed easily using JMP’s add-in feature. See Manage JMP Add-Ins in the JMP Scripting Guide.
Publishing a Data Table to JMP Live
Configurable Data Connectors allow a data table that connects to a database to be published to JMP Live and refreshed on a schedule. This enables multiple users across a variety of machines to access and analyze one data source using the functionality of JMP and JMP Live.
The data tables created using Configurable Data Connectors can be published to JMP Live and refreshed on a schedule. Compared to Open Table, these data tables make setting up refresh scripts easier because they do not depend as much on specifying the right ODBC driver.
For information on publishing a report to JMP Live, see Share Reports to JMP Live.