docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using JDBC with InterSystems IRIS  /  Using the InterSystems SQL Gateway with JDBC


Using JDBC with InterSystems IRIS
Using the InterSystems SQL Gateway with JDBC
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


The InterSystems SQL Gateway allows InterSystems IRIS™ to access external databases via both JDBC and ODBC. This chapter contains the SQL Gateway information concerning JDBC connections. For a detailed description of the SQL Gateway, see the chapter on Using the InterSystems SQL Gateway in Using InterSystems SQL .
Creating JDBC SQL Gateway Connections for External Sources
This section describes how to create a JDBC logical connection definition for the SQL Gateway.
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 InterSystems IRIS instance.
Note:
Controlling Gateway Logging and Java Version
To monitor SQL Gateway problems, you can enable SQL Gateway logging (see Enabling Logging for the SQL Gateway ). You can specify the version of Java to be used with the SQL Gateway by setting the InterSystems IRIS JavaHome parameter (see JavaHome in the Parameter File Reference ).
Creating a JDBC SQL Gateway Connection
To define a gateway connection for a JDBC-compliant data source, perform the following steps:
  1. In the Management Portal, go to the [System Administration] > [Connectivity] > [SQL Gateway Connections] page.
  2. On the Gateway Connection page, enter or choose values for the following fields:
    For example, a typical connection might use the following values:
    For the other options, see Implementation-specific Options . ”
  3. 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.
  4. To create the named connection, click Save .
  5. Click Close .
Creating a JDBC Connection to InterSystems IRIS via the SQL Gateway
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.
Connecting as a JDBC Data Source
  1. Within IrisDB-1, use the SQL Gateway to create a JDBC connection to the namespace in IrisDB-2 that you want to use.
    For example, a typical connection might use the following values:
    Setting Value
    Type JDBC
    Connection Name Cache2Samples FCL: should “Cache” be removed or replaced?
    User _SYSTEM
    Password SYS
    Driver name com.intersys.jdbc.CacheDriver FCL: did CacheDriver change to something else?
    URL jdbc:Cache://127.0.0.1:1972/SAMPLES
  2. Click Save .
  3. Click Close .
Implementation-specific 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.
Do Not Use Delimited Identifiers by Default
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:
Clear the check box if you are using any other database. All SQL identifiers will be delimited.
Use COALESCE
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.
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.
Conversion in Composite Row IDs
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:
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.