Using Caché SQL
Using the Caché SQL Gateway
[Back] [Next]
   
Server:docs2
Instance:LATEST
User:UnknownUser
 
-
Go to:
Search:    

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:
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:
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:
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:
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 Caché ObjectScript code with class names and class member names that you control. When you use this wizard, be sure to follow the rules for Caché ObjectScript identifiers, including length limits (see the section on Naming Conventions in Using Caché Objects).
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. For example, 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.
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:
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, Caché 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 Caché ObjectScript code with class names and class member names that you control. When you use this wizard, be sure to follow the rules for Caché ObjectScript identifiers, including length limits (see the section on Naming Conventions in Using Caché Objects).
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.SQLGateway 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 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, Caché 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 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: