Skip to main content

Connecting the SQL Gateway via ODBC

This chapter describes how to create an ODBC logical connection definition for the SQL Gateway, and how to use the Data Migration Wizard. See Using the InterSystems ODBC Driver for complete information on how to use InterSystems ODBC.

The following topics are discussed:

Creating Connections for External Sources

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.

The following topics are discussed in this section:


For OS-specific instructions on how to create a DSN, see the following sections in Using the InterSystems ODBC Driver:

Defining a Logical Connection in the Management Portal

To define a connection for an ODBC-compliant data source, perform the following steps:

  1. Define an ODBC data source name (DSN) for the external database (the procedure is probably described in the documentation for that database). .

  2. In the Management Portal, go to the System Administration > Configuration > Connectivity > SQL Gateway Connections page.

  3. Click Create New Connection.

  4. On the Gateway Connection page, enter or choose values for the following fields:

    • For Type of connection, choose ODBC.

    • Connection Name — Specify an identifier for the connection, for use within InterSystems IRIS.

    • Select an existing DSN — Choose the DSN that you previously created. You must use a DSN, since the ODBC SQL Gateway does not support connections without a DSN.

    • 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.

    For example, a typical connection might use the following values:

    Setting Value
    Type ODBC
    Connection Name ConnectionODBC1
    Select an existing DSN MyAccessPlayground
    User DBOwner
    Password DBPassword

    For the other options, see “Implementation-specific ODBC Connection Options” later in this section.

  5. 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 in the previous step allow for a valid connection.

  6. To create the named connection, click Save.

  7. Click Close.

Using the SQL Gateway as an ODBC Data Source

InterSystems IRIS provides ODBC drivers and thus can be used as an ODBC data source. That is, an InterSystems IRIS instance can connect to itself or to another InterSystems IRIS instance via ODBC 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.

To configure an InterSystems IRIS instance (InterSystems IRIS_A) to use another InterSystems IRIS instance (InterSystems IRIS_B) as an ODBC data source, do the following:

  1. On the machine that is running InterSystems IRIS_A, create a DSN that represents the namespace in InterSystems IRIS_B that you want to use.


    If InterSystems IRIS_B is installed on this machine, a suitable DSN might already be available, because when you install InterSystems IRIS, the installer automatically creates DSNs.

  2. Within InterSystems IRIS_A, use the SQL Gateway to create an ODBC connection that uses that DSN. Provide the following details:

    • For Type, choose ODBC.

    • Connection Name — Specify an identifier for the connection, for use within InterSystems IRIS_A.

    • Select an existing DSN — Choose the DSN that you previously created for InterSystems IRIS_B.

    For example, a typical connection might use the following values:

    Setting Value
    Type ODBC
    Connection Name TestConnection
    Select an existing DSN TestConnection

    You do not need to specify User and Password because that information is part of the DSN itself.

  3. Click Save.

  4. Click Close.

Implementation-specific ODBC 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 following options do not apply to all driver implementations.

Legacy Outer Join

The Enable legacy outer join syntax (Sybase) option controls whether the connection will enable you use to use legacy outer joins. Legacy outer joins use SQL syntax that predates the SQL-92 standard. To find out whether the external database supports such joins, consult the documentation for that database.

Needs Long Data Length

The Needs long data length option controls how the connection will bind data. The value of this option should agree with the SQL_NEED_LONG_DATA_LEN setting of the database driver. To find the value of this setting, use the ODBC SQLGetInfo function. If SQL_NEED_LONG_DATA_LEN equals Y, then select the Needs long data length option; otherwise clear it.

Supports Unicode Streams

The Supports Unicode streams option controls whether the connection supports Unicode data in streams, which are fields of type LONGVARCHAR or LONGVARBINARY.

  • Clear this check box for Sybase. If you are using a Sybase database, all fields you access via the SQL Gateway should include only UTF-8 data.

  • Select this check box for other databases.

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:

  • 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, which is consistent with the behavior of typical ODBC clients.

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:

  • 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 uses CAST 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.

Using the Data Migration Wizard

The Management Portal provides a wizard that you can use to migrate data from an external table or view.

When you migrate data from a table or view in an external source, the system generates a persistent class to store data of that table or view and then copies the data. This wizard assumes that the class should have the same name as the table or view from which it comes; similarly, the property names are the same as in the table or view. After the class has been generated, it does not have any connection to external data source.

  • If you have not yet created an SQL Gateway connection to the external database, do so before you begin (see “Creating SQL Gateway Connections for External Sources”).

  • From the Management Portal select System Explorer, then SQL. If necessary, change namespaces by clicking the current namespace displayed at the top of the page; this displays the list of available namespaces.

    At the top of the page, click the Wizards drop-down list, and select Data Migration.

  • On the first page of the wizard, select the table or view, as follows:

    • Select a destination namespace — Select the InterSystems IRIS namespace to which the data will be copied.

    • Schema Filter — Specify a schema (class package) name that contains the table or view. You can specify a name with wildcards to return multiple schemas, or % to return all schemas. For example, C% will return all schemas in the namespace beginning with the letter C. Use of this filter is recommended, as it will shorten the return list of schemas to select from, and thus improve loading speed.

    • Table Filter — Specify a table or view name. You can specify a name with wildcards to return multiple tables and/or views, or % to return all tables/views.

    • Table type — Select TABLE, VIEW, SYSTEM TABLE, or ALL. The default is TABLE.

    • Select a SQL Gateway connection — Select the SQL Gateway connection to use.

  • Click Next.

  • On the next page, you can optionally specify the following information for each class:

    • New Schema — Specify the package to contain the class or classes. Be sure to follow the rules for ObjectScript identifiers, including length limits (see the section on Naming Conventions in Defining and Using Classes).


      To change the package name for all classes, type a value at the top of this column and then click Change all.

    • Copy Definition — Select this check box to generate this class, based on the table definition in the external source. If you have already generated the class, you can clear this check box.

    • Copy Data — Select this check box to copy the data for this class from the external source. When you copy data, the wizard overwrites any existing data in the InterSystems IRIS class.

  • Click Next. The wizard displays the following optional settings:

    • Disable validation — If checked, data will be imported with %NOCHECK specified in the restriction parameter of the INSERT command.

    • Disable journaling for the importing process — If checked, journaling will be disabled for the process performing the data migration (not system-wide). This can make the migration faster, at the cost of potentially leaving the migrated data in an indeterminate state if the migration is interrupted by a system failure. Journaling is re-enabled at the end of the run, successful or not.

    • Defer indices — If checked, indices are built after the data is inserted. The wizard calls the class' %SortBegin() method prior to inserting the data in the table. This causes the index entries to be written to a temporary location for sorting. They are written to the actual index location when the wizard calls the %SortEnd() method after all rows have been inserted. Do not use Defer Indices if there are Unique indices defined in the table and you want the migration to catch any unique constraint violations. A unique constraint violation will not be caught if Defer Indices is used.

    • Disable triggers — If checked, data will be imported with %NOTRIGGER specified in the restriction parameter of the INSERT command.

    • Delete existing data from table before importing — If checked, existing data will be deleted rather than merged with the new data.

  • Click Finish. The wizard opens a new window and displays the Background Jobs page with a link to the background tasks page. Click Close to start the import immediately, or click the given link to view the background tasks page. In either case, the wizard starts the import as a background task.

  • In the Data Migration Wizard window, click Done to go back to the home page of the Management Portal.


The %SQL.Migration.ImportOpens in a new tab class contains wrappers around the Data Migration Wizard. See the class library documentation for details.

Microsoft Access and Foreign Key Constraints

When you use the Data Migration Wizard with Microsoft Access, the wizard tries to copy any foreign key constraints defined on the Access tables. To do this, it queries the MSysRelationships table in Access. By default, this table is hidden and does not provide read access. If the wizard can't access MSysRelationships, it migrates the data table definitions to InterSystems SQL without any foreign key constraints.

If you want the utility to migrate the foreign key constraints along with the table definitions, set Microsoft Access to provide read access for MSysRelationships, as follows:

  • In Microsoft Access, make sure that system objects are displayed.

  • Click Tools > Options and select the setting on the View tab.

  • Click Tools > Security > User and Group Permissions. Then select the Read check box next to the table name.

FeedbackOpens in a new tab