Skip to main content

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

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 (%CacheSQLStorageOpens in a new tab) provides object persistence by means of specially generated SQL queries.

A class that uses %CacheSQLStorageOpens in a new tab 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.

    • For ODBC: a DSN (data source name), defined in the usual way (see Using Caché as an ODBC Data Source on Windows and Using Caché as an ODBC Data Source on UNIX® in Using Caché with ODBC).

      Note:

      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:

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 Caché applications using objects and/or SQL queries.

  • Store persistent Caché 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 Caché 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 a Caché 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 Caché SQL Gateway is independent of application logic; an application can be modified to switch between external databases and the built-in Caché database with minimal effort and no change to application logic.

Any class that uses the Caché SQL Gateway to provide object persistence is identical in usage to classes that using native persistence and can make full use of Caché features including Java, ActiveX, SQL, and Web access.

Using the Link Table Wizard

When you link to an external table or view, you create a persistent Caché 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 Caché class and the corresponding SQL table in Caché.

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 Using Caché Objects).

  • If you have not yet created a 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 Link Table.

  • On the first page of the wizard, select one or more table or views, 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. 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 Caché. 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 Caché 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 Caché 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 Caché 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 Caché. 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 Caché 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 Caché class/table). You can now use the new class as you would any other persistent class within Caché.

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 Gateway Connections” for more information.

Limitations When Using the 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:

  • Informix: You cannot create a view inside of Caché that is based on a linked Informix table, because the generated SQL is not valid in Informix.

  • Sybase: As part of query processing, Caché 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.

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,SQL and select Browse SQL Schemas in the SQL Operations column.

  • Click the namespace you are interested in.

  • Click the Queries link next to 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.

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 Using Caché Objects).

  • If you have not yet created a 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 Link Procedure.

  • On the first page of the wizard, select one or more procedures, 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 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 Caché:

    • 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 Caché database and compiles it.

Note:
Closing the Link Procedure Connection

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 Gateway Connections” for more information.

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.SQLGatewayOpens in a new tab class, which provides methods such as the following:

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")

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.

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

FeedbackOpens in a new tab