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 username 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.
Caché provides JDBC drivers and can be used as a JDBC data source. That is, a Caché instance can connect to itself or to another Caché instance via JDBC and the SQL Gateway. Specifically, the connection is from a namespace in one Caché to a namespace in the other Caché. To connect in this way, you need the same information that you need for any other external database: the connection details for the database driver that you want to use. This section provides the basic information.
Before you define an SQL gateway connection, you should make sure that you understand the requirements of the external database and of the database driver, because these requirements affect how you define the connection.
Do Not Use Delimited Identifiers by Default
The Use COALESCE
option controls how a query is handled when it includes a parameter (?
), and it has an effect only when a query parameter equals null.
If you do not select Use COALESCE
and if a query parameter equals null, the query returns only records that have null for the corresponding value. For example, consider a query of the following form:
SELECT ID, Name from LinkedTables.Table WHERE Name %STARTSWITH ?
If the provided parameter is null, the query would return only rows with null-valued names.
If you select Use COALESCE
, the query wraps each parameter within a COALESCE
function call, which controls how null values are handled.
Then, if a query parameter equals null, the query essentially treats the parameter as a wildcard. In the previous example, if the provided parameter is null, this query returns all rows.
Whether you select this option depends on your preferences and on whether the external database supports the COALESCE
To find out whether the external database supports the COALESCE
function, consult the documentation for that database.
Conversion in Composite Row IDs