docs.intersystems.com
Home  /  Application Development: Language Bindings and Gateways  /  Using the InterSystems SQL Gateway  /  Connecting with the ODBC Driver


Using the InterSystems SQL Gateway
Connecting with the ODBC Driver
[Back]  [Next] 
InterSystems: The power behind what matters   
Search:  


This chapter describes how to create an ODBC logical connection definition for the SQL Gateway, and use the Data Migration Wizard. See Using ODBC with InterSystems IRIS for complete information on how to use InterSystems ODBC.
The following topics are discussed:
Creating ODBC 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 InterSystems IRIS instance.
The following topics are discussed in this section:
Note:
For OS-specific instructions on how to create a DSN, see the following sections in Using ODBC with InterSystems IRIS:
Defining a Logical Connection in the Management Portal
To define a gateway connection for an ODBC-compliant data source, perform the following steps:
  1. Define an ODBC data source name (DSN) for the external database. See the documentation for the external database for information on how to do this.
  2. In the Management Portal, go to the [System Administration] > [Configuration] > [Connectivity] > [SQL Gateway Connections] page (full menu path [System Administration] > [Configuration] > [Connectivity] > [SQL Gateway Connections]).
  3. 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 later in this section.
  4. 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.
  5. To create the named connection, click Save.
  6. Click Close.
Creating an ODBC Connection through the SQL Gateway
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.
    Tip:
    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 example, a typical connection might use the following values:
    Tip:
    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 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 SQL gateway 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 SQL gateway 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 SQL gateway connection supports Unicode data in streams, which are fields of type LONGVARCHAR or LONGVARBINARY.
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.
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.
Note:
This wizard does not yet support JDBC data sources.
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:
Special Considerations for UNIX® and Related Platforms
This section provides technical information about SQL Gateway drivers and the SQL Gateway test program.
SQL Gateway Drivers for UNIX® Systems
The <install-dir>/bin/ directory contains the following versions of the shared object used by the SQL Gateway. This enables you to connect from InterSystems IRIS to other ODBC client drivers. These files are not installed if you perform a stand-alone installation.
linked against iODBC
linked against unixODBC
For more information, see Using InterSystems IRIS as an ODBC Data Source on UNIX® in Using ODBC with InterSystems IRIS.
Note:
Setting the Shared Library Path on UNIX® Systems
When using third-party shared libraries on a UNIX® system, LD_LIBRARY_PATH must be defined by setting the InterSystems IRIS LibPath parameter (see LibPath in the Configuration Parameter File Reference). This is a security measure to prevent unprivileged users from changing the path.
Using the UNIX® ODBC SQL Gateway Test Program
Within a full UNIX® InterSystems IRIS installation, you can use a special program to test gateway access from InterSystems IRIS. In gatewaytest.sh, the InterSystems IRIS process making the initial call is the client application. Typically, a Gateway call from InterSystems IRIS calls the DSN of another vendor’s database.
Note:
The test program uses the default 8-bit iODBC–compliant drivers (libirisodbc.so and odbcgateway.so). For a complete list of the InterSystems ODBC client drivers and InterSystems SQL Gateway drivers available for supported UNIX® platforms, see Using InterSystems IRIS as an ODBC Data Source on UNIX® in Using ODBC with InterSystems IRIS.
The gateway test program consists of files in the directory install-dir/dev/odbc/samples/sqlgateway
You may need to modify the shell script(gatewaytest.sh), depending on your configuration.
To use the test program:
  1. Execute the test script by typing the following:
    ./sqlgateway/gatewaytest.sh
    
The gatewaytest.sh script does the following:
  1. It starts an InterSystems IRIS session and runs the routine SQLGatewayTest in the USER namespace.
  2. This application routine then loads the default InterSystems SQL Gateway driver, odbcgateway.so, which is linked against the iODBC driver manager.
  3. The driver manager loads the client driver using information from the ODBC initialization file.
  4. The client driver then establishes a TCP/IP connection to port 51773 and is connected to the InterSystems IRIS USER namespace using the DSN definition from the ODBC initialization file.
  5. The routine executes the following query:
    SELECT * FROM SAMPLE.PERSON
  6. The routine then fetches the first ten rows of the result set.