Using the Caché SQL Gateway
The Caché SQL Gateway provides access from Caché to external databases via JDBC and ODBC. This chapter discusses the following topics:
Architecture of the Caché SQL Gateway — describes the internal structure and limitations of the SQL Gateway
Creating 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 Caché object.
The Link Procedure Wizard: Linking to a Stored Procedure — describes the procedure for linking to stored procedures in external sources.
Controlling Gateway Connections — describes methods used to manage SQL Gateway connections.
The Data Migration Wizard: Migrating Data from an ODBC Source — describes how to migrate data from external ODBC sources and create an appropriate Caché class definition to store the data.
Architecture of the Caché SQL Gateway
Internally, the Caché SQL Gateway uses the following components:
The Connection Manager maintains a list of logical connection definitions for Caché. Each definition has a logical name used in Caché, as well as connection details for a specific external ODBC or JDBC compliant database. The Caché SQL Gateway uses these logical names when it establishes connections (see Creating Gateway Connections for External Sources).
The Caché SQL Gateway API is a set of functions used by a Caché 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 Caché 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 Caché 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 Caché SQL Gateway API instead of accessing data stored within Caché.
Persisting External Tables in Caché
All object persistence in Caché is provided by means of a storage class (see “Storage Definitions and Storage Classes” in Using Caché Objects), which generates the code needed to save and retrieve a persistent object within a database. The SQL storage class (%CacheSQLStorage) provides object persistence by means of specially generated SQL queries.
A class that uses %CacheSQLStorage 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 Caché SQL Gateway, note the following restrictions:
All the tables listed in the FROM clause of an SQL query must come from the same data source. Queries that join data from heterogeneous data sources are not allowed.
SQL queries targeted at external databases cannot use the following Caché SQL extensions:
Creating Gateway Connections for External Sources
Caché 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 Caché), 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 Caché instance.
Each gateway connection consists of the following details:
A logical name for the gateway connection. This name would be used, for example, within any Caché 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.
When creating an SQL gateway 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.
For detailed information on creating logical connection definitions for JDBC and ODBC, see:
Controlling 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.SQLGateway class, which provides methods such as the following:
DropAll() — drop all open connections and unload the SQL Gateway library.
DropConnection() — disconnect the specified JDBC or ODBC connection.
TestConnection() — test a previously defined SQL Gateway connection (see “Creating Gateway Connections for External Sources”) and write diagnostic output to the current device.
Various methods for opening connections and controlling transactions. See the %SYSTEM.SQLGateway 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":
Note that SQL Gateway connection names are case-sensitive.
The Data Migration Wizard: Migrating Data from an ODBC or JDBC Source
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 Gateway Connections for External Sources).
From the Management Portal select System Explorer, then SQL (System, SQL). Select a namespace with the Switch option 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 Caché 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.
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 Using Caché Objects).Tip:
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 Caché 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.
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 Caché 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.