Creating SQL Gateway Connections

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 user name 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.

See the following sections for detailed information on creating logical connection definitions:

• Connecting the SQL Gateway via JDBC

• Connecting the SQL Gateway via ODBC

Each SQL Gateway connection consists of the following details:

• A logical name for the connection. This name would be used, for example, within any InterSystems SQL queries.

• Optional login credentials to access the database.

• Optional information to control the JDBC or ODBC driver.

• Driver-specific connection details:

  • For JDBC: The full class name of the JDBC client driver, the driver class path (a list of JAR files to search when locating the JDBC driver), and the JDBC connection URL.

  • For ODBC: a DSN (data source name), defined in the usual way (see Defining an ODBC Data Source on Windows and Defining an ODBC Data Source on UNIX® in Using the InterSystems ODBC Driver).

    Note:

    When creating a connection for use by the Link Table Wizard using Microsoft SQL Server DNS configuration, do not set the Use regional settings option. This option is intended only for applications that display data, not for applications that process data.

Managing SQL Gateway Connections

In some cases, it may be necessary to manage connections created by code that links external tables or stored procedures (see Using Linked Tables and Linked Procedures). SQL Gateway connections can be managed by the %SYSTEM.SQLGatewayOpens in a new tab class, which provides methods such as the following:

• DropAll()Opens in a new tab — drop all open connections and unload the SQL Gateway library.

• DropConnection()Opens in a new tab — disconnect the specified JDBC or ODBC connection.

• TestConnection()Opens in a new tab — test a previously defined SQL Gateway connection and write diagnostic output to the current device.

• Various methods for opening connections and controlling transactions. See the %SYSTEM.SQLGatewayOpens in a new tab class documentation for full details.

These methods can be called with the special $SYSTEM object. For example, the following command would close a previously defined SQL Gateway connection named "MyConnectionName":

   do $system.SQLGateway.DropConnection("MyConnectionName")

SQL Gateway connection names are case-sensitive.