Accessing a Database with the SQL Gateway
The InterSystems SQL Gateway provides access from InterSystems IRIS® data platform to external databases via JDBC and ODBC, as described in the following topics:
-
Architecture of the InterSystems SQL Gateway — describes the internal structure and constraints of the SQL Gateway
-
Creating SQL Gateway Connections for External Sources — gives an overview of logical connection definitions, which are used by the SQL Gateway wizards to identify the external databases.
-
The Link Table Wizard: Linking to a Table or View — describes the procedure for linking to tables or views in external sources so that you can access the data in the same way you access any InterSystems IRIS object.
-
The Link Procedure Wizard: Linking to a Stored Procedure — describes the procedure for linking to stored procedures in external sources.
-
Controlling SQL Gateway Connections — describes methods used to manage SQL Gateway connections.
See the following sections for detailed information on creating logical connection definitions:
SQL Gateway connections are used to access remote tables and procedures created by the Link Table Wizard and Link Procedure Wizard (respectively), programmatically, or as part of an interoperability function. The information captured in an SQL Gateway definition can also be used to create foreign tables, which is a pure SQL approach to projecting data from remote databases or files to InterSystems IRIS SQL. This page describes only how to use the Link Table Wizard to access external tables; to read more about using foreign tables, see Foreign Tables.
Architecture of the InterSystems SQL Gateway
Internally, the InterSystems SQL Gateway uses the following components:
-
The Connection Manager maintains a list of logical connection definitions for InterSystems IRIS. Each definition has a logical name used in InterSystems IRIS, as well as connection details for a specific external ODBC or JDBC compliant database. The InterSystems SQL Gateway uses these logical names when it establishes connections (see “Creating SQL Gateway Connections for External Sources”).
-
The InterSystems SQL Gateway API is a set of functions used by an InterSystems IRIS program to communicate with a third-party RDBMS. These functions are implemented by means of a shared library, which is responsible for making the ODBC or JDBC calls.
-
The External Table Query Processor is an extension to the InterSystems SQL Query Processor that handles queries targeted at external tables.
-
The SQL Dictionary stores a list of all defined SQL tables. A given table is marked as "external" when its data is stored in a third-party RDBMS. When the InterSystems SQL Query Processor detects that the table (or tables) referenced within an SQL query are external, it invokes the External Table Query Processor, which generates a query execution plan by calling the InterSystems SQL Gateway API instead of accessing data stored within InterSystems IRIS.
Persisting External Tables in InterSystems IRIS
All object persistence in InterSystems IRIS is provided by means of a storage class (see “Storage Definitions and Storage Classes” in Defining and Using Classes), which generates the code needed to save and retrieve a persistent object within a database. The SQL storage class (%Storage.SQL) provides object persistence by means of specially generated SQL queries.
A class that uses %Storage.SQL for persistence indicates that it is an "external" class by providing a value for its CONNECTION and EXTERNALTABLENAME class parameters. The class compiler creates an SQL table definition for the class, and generates the SQL queries for the object persistence code. These queries automatically make calls to the correct external database by means of the External Table Query Processor.
Restrictions on SQL Gateway Queries
When you use the InterSystems SQL Gateway, note the following restrictions:
-
Queries that join data from heterogeneous data sources are only supported for ODBC connections. When using JDBC, all tables listed in the FROM clause of an SQL query must come from the same data source. .
-
SQL queries targeted at external databases cannot use the following InterSystems SQL extensions:
-
The "->" operator.
-
The %EXACT function, or the %SYSTEM.UtilOpens in a new tab Collation()Opens in a new tab method with the collation flag set to EXACT.
-
The inclusion of other columns within a count (*) query.
-
InterSystems IRIS-specific operators that have % as the first character of their name.
-
Creating SQL Gateway 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 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.
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.
-
See the following chapters for detailed information on creating logical connection definitions:
The Link Table Wizard: Linking to a Table or View
The Management Portal provides a wizard that you can use to link to an external table in an ODBC- or JDBC-compliant database. When you have linked to an external table, you can:
-
Access data stored in third-party relational databases within InterSystems IRIS applications using objects and/or SQL queries.
-
Store persistent InterSystems IRIS objects in external relational databases.
For example, suppose you have an Employee table stored within an external relational database. You can use this table within InterSystems IRIS as an object by creating an Employee class that communicates (by executing SQL queries via JDBC or ODBC) with the external database.
From the perspective of an InterSystems IRIS application, the Employee class behaves in much the same way as any other persistent class: You can open instances, modify, and save them. If you issue SQL queries against the Employee class, they are automatically dispatched to the external database.
The use of the InterSystems SQL Gateway is independent of application logic; an application can be modified to switch between external databases and the built-in InterSystems IRIS database with minimal effort and no change to application logic.
Any class that uses the InterSystems SQL Gateway to provide object persistence is identical in usage to classes that using native persistence and can make full use of InterSystems IRIS features including Java, SQL, and Web access.
Using the Link Table Wizard
When you link to an external table or view, you create a persistent InterSystems IRIS class that is linked to that table or view. The new class stores and retrieves data from the external source using the SQL Gateway. You can specify information about both the InterSystems IRIS class and the corresponding SQL table in InterSystems IRIS.
This wizard generates ObjectScript code with class names and class member names that you control. When you use this wizard, be sure to follow the rules for ObjectScript identifiers, including length limits (see the section on Naming Conventions in Defining and Using Classes).
-
If you have not yet created a 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. Select a namespace by clicking the name of 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 Link Table.
-
On the first page of the wizard, select one or more table or views, 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. You can select multiple items. In this case, when you click Next, the next screen prompts you for a package name. Specify the name of the package to contain the classes and then click Finish.
-
Table Filter — Specify the table or view to link to. 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 second page, specify which fields should be available as object properties in InterSystems IRIS. Make changes as follows:
-
Highlight one or more fields and click the single arrow to move it or them from one list to another; click the double arrow to move all fields (selected or not) from one list to another.
-
In the selected list, use the up and down arrows to modify the order of the fields in the table that InterSystems IRIS projects for the given class. This does not affect the order of the properties in the class definition.
-
-
Click Next.
-
On the third page, specify information about the properties in the generated class. For each property, you can specify all the available options:
-
Read only — Select this check box to make the property read-only. This controls the ReadOnly keyword for the property.
Tip:Use the select_all check box to select or clear all the check boxes in this column.
-
New Property Name — Specifies the name of the object property that will contain the data from this field.
-
New Column Name (SQL Field Name) — Specifies the SQL field name to use for this property. This controls the SqlFieldName keyword for the property.
-
-
Click Next.
-
On the last page, specify the following:
-
Primary Key — Select the primary key for the new InterSystems IRIS table from the list provided. In addition to the default key provided, you can click the "Browse" button to select one or more columns. You may select multiple columns; multiple columns are returned as a composite key separated by commas. You must specify a primary key.
-
New class name — Specify the name of the InterSystems IRIS class to create, including the package. The default package name is nullschema.
-
New table name — Specify the name of the SQL table to create in InterSystems IRIS. This controls the SqlTableName keyword for the class.
-
-
Click Finish. The wizard displays the Background Jobs page with a link to the background tasks page.
-
Click Close. Or click the given link to view the background tasks page. In either case, the wizard starts a background task to do the work.
The wizard stores a new class definition in the InterSystems IRIS database and compiles it. If data is present, it should be immediately visible in the external database (you can check by issuing SQL queries against the newly created InterSystems IRIS class/table). You can now use the new class as you would any other persistent class within InterSystems IRIS.
By design, the code generated by the Link Table Wizard does not close the connections that it opens. This avoids problems such as conflicts between SQL statements that share the same connection. See “Controlling SQL Gateway Connections” for more information.
Limitations When Using a Linked Table
As always, it is important to be aware of the particular limitations (syntactical or otherwise) and requirements of the database to which you are connecting. The following are a few examples:
-
JDBC connections, unlike ODBC, do not support heterogeneous INSERT...SELECT statements (involving both a local and a linked table, or two linked tables).
-
ODBC connections, unlike JDBC, do not fill the %ROWID property.
-
Informix: You cannot create a view inside of InterSystems SQL that is based on a linked Informix table, because the generated SQL is not valid in Informix.
-
Sybase: As part of query processing, InterSystems SQL can transform the expression of an outer join into an equivalent canonicalized form. The SQL92-standard CROSS JOIN syntax may be required to reconstruct this form as SQL in order to access a linked table. Because Sybase does not support SQL92-standard CROSS JOIN, some queries using outer joins on linked Sybase tables will fail to execute.
-
Oracle: the CAST must be performed around any COUNT aggregates used in Oracle sourced tables.
-
MySQL: when accessing a stored procedure with INOUT and OUT, bound parameters are not updated correctly. Instead, the value is returned in the result set.
Before you try to use a linked table, you might want to examine the cached query that is generated for it, to ensure that the syntax is valid for the database you are using. To see the cached query for a given linked table:
-
In the Management Portal, go to System Explorer, SQL.
-
Click the namespace you are interested in.
-
Select the Schema from the pull-down list.
-
Click Cached Queries for the package that contains the table. The system displays a table of the cached queries for this package. The Query column displays the full query.
-
Optionally click the link for the query to see more details.
The Link Procedure Wizard: Linking to a Stored Procedure
The Management Portal provides a wizard that you can use to link to a stored procedure defined in an external ODBC- or JDBC-compliant database. When you link to the procedure, the system generates a method and a class to contain the method. When you link to an stored procedure, you create a class method that does the same action that the stored procedure does. This method is marked with the SqlProc keyword. The class method is generated within a new class, and you can specify information such as the class and package name. This method cannot accept a variable number of arguments. Default parameters are permitted, but the signature of the stored procedure is fixed.
By design, the code generated by the Link Procedure Wizard does not close the connections that it opens. This avoids problems such as conflicts between SQL statements that share the same connection. See “Controlling SQL Gateway Connections” for more information.
-
If you have not yet created a 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. Select a namespace by clicking the name of 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 Link Procedure.
-
On the first page of the wizard, select one or more procedures, 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 procedure. 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.
-
Procedure Filter — Specify a procedure to link to. You can specify a name with wildcards to return multiple procedures, or % to return all procedures. You can select multiple procedures. In this case, when you click Next, the next screen prompts you for a package name. Specify the name of the package to contain the classes and then click Finish.
-
Select a SQL Gateway connection — Select the SQL Gateway connection to use.
-
-
Click Next.
-
On the second page, specify details about the class to generate in InterSystems IRIS:
-
New package name — Specify the name of the package to contain the class or classes.
-
New class name — Specify the name of the class to generate.
-
New procedure name — Specify the name of the procedure; specifically this controls the SqlName keyword of the method.
-
New method name — Specify the name of the method to generate.
-
Description method name — Optionally provide a description of the method; this is used as a comment for the class definition, to be displayed in the class reference.
-
-
Click Finish. The wizard displays the Background Jobs page with a link to the background tasks page.
-
Click Close. Or click the given link to view the background tasks page. In either case, the wizard starts a background task to do the work.
The wizard stores a new class definition within the InterSystems IRIS database and compiles it.
This wizard generates ObjectScript code with class names and class member names that you control. When you use this wizard, be sure to follow the rules for ObjectScript identifiers, including length limits (see the section on Naming Conventions in Defining and Using Classes).
Controlling SQL Gateway Connections
In some cases, it may be necessary to manage connections created by code that links external tables or stored procedures (see “The Link Table Wizard” and “The Link Procedure Wizard”). 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 (see “Creating SQL Gateway Connections for External Sources”) 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.