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.

Note:

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.

Note:

Closing the Link Table Connection

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

Creating SQL Gateway Connections for External Sources