Connecting the SQL Gateway via JDBC
This chapter describes how to create a JDBC logical connection definition for the SQL Gateway. See Using Java with InterSystems Software for more information on the InterSystems JDBC driver.
InterSystems IRIS maintains a list of SQL Gateway connection definitions, which are logical names for connections to external data sources. Each connection definition consists of a logical name (for use within InterSystems IRIS), information on connecting to the data source, and a username and password to use when establishing the connection. These connections are stored in the table %Library.sys_SQLConnection. You can export data from this table and import it into another instance of the same version of InterSystems IRIS.
To monitor problems when connected via JDBC, you can enable JDBC logging for the SQL Gateway connection (see “SQL Gateway Logging”). The same dialog also allows you to specify JAVAHOME and other JDBC settings.
Defining a Logical Connection in the Management Portal
To define a SQL Gateway connection for a JDBC-compliant data source, perform the following steps:
-
In the Management Portal, go to the System Administration > Configuration > Connectivity > SQL Gateway Connections page.
-
Click Create New Connection.
-
On the SQL Gateway Connection page, enter or choose values for the following fields:
-
For Type, choose JDBC.
-
Connection Name — Specify an identifier for the connection, for use within InterSystems IRIS.
-
User — Specify the name for the account to serve as the default for establishing connections, if needed.
-
Password — Specify the password associated with the default account.
-
Driver name — Full class name of the JDBC client driver.
-
URL — Connection URL for the data source, in the format required by the JDBC client driver that you are using.
-
Class path — Specifies a comma-separated list of additional JAR files to load.
-
Properties — Optional string that specifies vendor-specific connection properties. If specified, this string should be of the following form:
property= value; property= value;...
See the JDBC Quick Reference for more information on connection properties.
For example, a typical connection might use the following values:
Setting Value Type JDBC Connection Name ConnectionJDBC1 User JDBCUser Password JDBCPassword Driver name oracle.jdbc.driver.OracleDriver URL jdbc:oracle:thin:@//oraserver:1521/SID Class path /fill/path/to/ojdbc14.jar Properties oracle.jdbc.V8Compatibility=true; includeSynonyms=false;restrictGetTables=true For the other options, see “Implementation-specific JDBC Connection Options.”
-
-
Optionally test if the values are valid. To do so, click the Test Connection button. The screen will display a message indicating whether the values you have entered allow for a valid connection.
-
To create the named connection, click Save.
-
Click Close.
Creating a Connection between Namespaces
InterSystems IRIS provides JDBC drivers and can be used as a JDBC data source. That is, an InterSystems IRIS instance can connect to itself or to another InterSystems IRIS instance via JDBC and the SQL Gateway. Specifically, the connection is from a namespace in one InterSystems IRIS to a namespace in the other InterSystems IRIS. To connect in this way, you need the same information that you need for any other external database: the connection details for the database driver that you want to use. This section provides the basic information.
Using the SQL Gateway as a JDBC Data Source
To configure one InterSystems IRIS instance (IrisDB-1) to use another separate instance (IrisDB-2) as a JDBC data source, do the following:
-
Within IrisDB-1, use the SQL Gateway to create a JDBC connection to the namespace in IrisDB-2 that you want to use.
-
For Type, choose JDBC.
-
Connection Name — Specify an identifier for the connection, for use within IrisDB-1.
-
User — Specify the username needed to access IrisDB-2, if needed.
-
Password — Specify the password for this user.
-
Driver name — Use com.intersystems.jdbc.IRISDriver
-
URL — Connection URL for the data source, in the following format:
jdbc:IRIS://IP_address:port/namespace
Here IP_address:port is the IP address and TCP port where IrisDB-2 is running, and namespace is the namespace to which you want to connect (see “Defining a JDBC Connection URL”).
For example, a typical connection might use the following values:
Setting Value Type JDBC Connection Name ConnectUser User _SYSTEM Password SYS Driver name com.intersystems.jdbc.IRISDriver URL jdbc:IRIS://127.0.0.1:1972/User -
Class path — Leave this blank.
-
Properties — Optional string that specifies connection properties supported by the InterSystems JDBC drivers. If specified, this string should be of the following form:
property= value; property= value;...
For the other options, see “Implementation-specific JDBC Connection Options” later in this section.
-
-
Click Save.
-
Click Close.
Implementation-specific JDBC Connection Options
Before you define an SQL Gateway connection, you should make sure that you understand the requirements of the external database and of the database driver, because these requirements affect how you define the connection.
The Do not use delimited identifiers by default option controls the format of identifiers in the generated routines.
Select this check box if you are using a database that does not support delimited SQL identifiers. This currently includes the following databases:
-
Sybase
-
Informix
-
MS SQL Server
Clear the check box if you are using any other database. All SQL identifiers will be delimited.
The Use COALESCE option controls how a query is handled when it includes a parameter (?), and it has an effect only when a query parameter equals null.
-
If you do not select Use COALESCE and if a query parameter equals null, the query returns only records that have null for the corresponding value. For example, consider a query of the following form:
SELECT ID, Name from LinkedTables.Table WHERE Name %STARTSWITH ?
If the provided parameter is null, the query would return only rows with null-valued names.
-
If you select Use COALESCE, the query wraps each parameter within a COALESCE function call, which controls how null values are handled.
Then, if a query parameter equals null, the query essentially treats the parameter as a wildcard. In the previous example, if the provided parameter is null, this query returns all rows.
Whether you select this option depends on your preferences and on whether the external database supports the COALESCE function.
To find out whether the external database supports the COALESCE function, consult the documentation for that database.
The Conversion in composite Row IDs option controls how non-character values are treated when forming a composite ID. Choose an option that is supported by your database:
-
Do not convert non-character values — This option performs no conversion. This option is suitable only if your database supports concatenating non-character values to character values.
-
Use CAST — This option usesCAST to convert non-character values to character values.
-
Use {fn convert ...} — This option uses{fn convert ...} to convert non-character values to character values.
In all cases, the IDs are concatenated with|| between the IDs (or transformed IDs).
Consult the documentation for the external database to find out which option or options it supports.
SQL Gateway Logging
A log can be generated for the SQL Gateway when it is using a JDBC connection. To enable this logging:
-
In the Management Portal, go to System Administration > Configuration > External Language Servers.
-
Select the %JDBC_Server link to bring up the Edit dialog (caution: do not select the %Java_Server link, which is completely different).
-
At the bottom of the Edit dialog, select the Advanced Settings Show link if advanced settings are not already displayed.
-
Specify a name for LogFile (for example, jdbcSqlGateway.log) to record the interaction between the SQL Gateway and the database. If you do not specify a fully qualified path, the log file will be in the current directory from when the JDBC SQL Gateway was initially started (probably the /mgr or /mgr/namespace directory.
-
Select the Save button at the top of the dialog.
-
On the External Server main page, shut down and restart the %JDBC_Server connection to enable the new settings.
You can also specify the Java version (JAVAHOME) to be used with the SQL Gateway by setting the Java Home Directory field in the edit dialog. See %JDBC Server in the Configuration Parameter File Reference for more information about these settings.
Enable logging only when you need to perform troubleshooting. You should not enable logging during normal operation, because it will dramatically slow down performance.