Connecting with the XDBC Gateway

At a high level, an XDBC Gateway connection between InterSystems IRIS and an external database consists of the following:

Starting the External Language Server for your connection type (JDBC or ODBC).
Connecting with %XDBC.Gateway.Connection:GetConnection(), passing in the connection information for the external database. This information can be either a named SQL Gateway connection definition (which requires a DSN) or a JSON connection string.

After you establish a connection to the external database, you can use the methods provided by the %XDBC package to interact with it. For details, see Using the XDBC Gateway.

Starting the External Language Server

InterSystems IRIS provides predefined External Language Servers for JDBC and ODBC connections through the XDBC Gateway: %Java Server and %ODBC Server. These are stopped by default and start automatically when a client attempts to connect, but starting them manually can be helpful for troubleshooting connection issues:

Go to System > Configuration > External Language Servers.
Click Start on the server for your connection type:
- JDBC: %Java Server
- ODBC: %ODBC Server

Establishing the Connection

After starting the External Language Server, you can establish a connection between InterSystems IRIS and the external database by passing the connection information to the GetConnection() method. GetConnection takes as an argument the connection information of the external database, which can be any of the following:

The name of a SQL Gateway connection definition
JSON (either a dynamic object or JSON string) containing the connection information to the external database.

SQL Gateway Connection Definition

To connect with a named SQL Gateway connection definition:

Create a SQL Gateway Connection definition for your connection type:
- JDBC
- ODBC

Specify the definition name in %XDBC.Gateway.Connection.GetConnection():

set conn = ##class(%XDBC.Gateway.Connection).GetConnection("ConnectionDefinitionName",.status)

Connection String

To connect with a connection string, create a JSON string or dynamic object and pass it to GetConnection(). The fields vary between connection types.

JDBC

JDBC connection strings all require a host, port, database name, username, password, connection type, and the information for the JDBC driver. When the connection is to a first-party database (that is, another instance of InterSystems IRIS), you can set the JDBC driver by specifying com.intersystems.jdbc.IRISDataSource.

A connection to another InterSystems IRIS database uses the following fields:

{
    "host": "external_db_hostname",
    "port": external_db_port,
    "databasename": "external_db_name",
    "datasource": "com.intersystems.jdbc.IRISDataSource",
    "user": "external_db_user",
    "password": "external_db_password",
    "type": "jdbc"
}

When the connection is to a third-party database, you can set the JDBC driver by specifying the location of the driver's .jar file with classpath and the JDBC DataSource with datasource. A connection to a Microsoft SQL Server database uses the following fields:

{
    "host": "external_db_hostname",
    "port"": external_db_port,
    "databasename":"external_db_name",
    "datasource": "com.microsoft.sqlserver.jdbc.SQLServerDataSource",
    "classpath": "/path/to/sqljdbc42.jar",
    "properties": "null",
    "user" : "external_db_user",
    "password" : "external_db_password",
    "type"":""jdbc"
}

The following examples connect to an InterSystems IRIS and Microsoft SQL Server database with JDBC using the above fields.

Connect to InterSystems IRIS:

set connInfo = "{""host"":""192.0.2.0"",""port"":1972,""databasename"":""USER"",""datasource"":""com.intersystems.jdbc.IRISDataSource"",""user"":""_SYSTEM"",""password"":""SYS"",""type"":""jdbc""}"
set conn = ##class(%XDBC.Gateway.Connection).GetConnection(connInfo,.status)

Connect to Microsoft SQL Server:

set connInfo = "{""host"":""192.0.2.1"",""port"":1433,""databasename"":""master"",""datasource"":""com.microsoft.sqlserver.jdbc.SQLServerDataSource"",""classpath"":""C:\\Users\\Bob\\Drivers\\sqljdbc42.jar"",""properties"":""null"",""user"":""sa"",""password"":""my_password"",""type"":""jdbc""}"
set conn = ##class(%XDBC.Gateway.Connection).GetConnection(connInfo,.status)

ODBC

ODBC connection strings can either be a DSN with a username and password or a string with the connection information that would normally be provided by the DSN.

To connect with a DSN, create a DSN in your system and specify it with the dsn field:

{
    "dsn": "dsn_name",
    "user": "external_db_user",
    "password": "external_db_password",
    "type": "odbc"
}

To connect without a DSN, you must specify in the string field the connection information that would normally be provided by the DSN. Different databases expect different things, so the contents of this string vary between databases:

DSN-less connection to InterSystems IRIS:

{
    "string" : "Driver=InterSystems IRIS ODBC35;HOST=192.0.2.0;Port=1972;Database=USER;UID=_SYSTEM;PWD=SYS;LOG=1"
    "type" : "odbc"
}

DSN-less connection to Microsoft SQL Server:

{
    "string" : "Driver=SQL Server;Server=MSSQLServer;Database=XDBCTest;UID=sa;PWD=my_password"
    "type" : "odbc"
}

The following examples connect to an InterSystems IRIS and a Microsoft SQL Server database with ODBC and both with and without a DSN.

Connect with a DSN to to any external database:

s connInfo = "{""type"":""odbc"",""dsn"":""my_dsn"",""username"":""_SYSTEM"",""password"":""SYS""}"
set conn = ##class(%XDBC.Gateway.Connection).GetConnection(connInfo,.status)

Connect without a DSN to InterSystems IRIS:

s connInfo= "{""type"":""odbc"",""string"":""Driver=InterSystems IRIS ODBC35;HOST=192.0.2.0;Port=1972;Database=USER;UID=_SYSTEM;PWD=SYS;LOG=1""}"
set conn = ##class(%XDBC.Gateway.Connection).GetConnection(connInfo,.status)

Connect without a DSN to Microsoft SQL Server:

s connInfo= "{""type"":""odbc"",""string"":""Driver=SQL Server;Server=MSSQLServer;Database=XDBCTest;UID=sa;PWD=my_password""}"
set conn = ##class(%XDBC.Gateway.Connection).GetConnection(connInfo,.status)

Using the XDBC Gateway

XDBC Gateway Overview