Skip to main content

Using the SQL Gateway with ODBC

The SQL Gateway allows Caché to access external databases via both JDBC and ODBC. For a detailed description of the SQL Gateway, see the chapter on Using the SQL Gateway in Using Caché SQL.

This chapter discusses the following topics:

Note:
Setting the Shared Library Path on UNIX® Systems

When using third-party shared libraries on a UNIX® system, LD_LIBRARY_PATH must be defined by setting the Caché LibPath parameter (see “LibPath” in the Caché Parameter File Reference). This is a security measure to prevent unprivileged users from changing the path.

Creating ODBC SQL 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 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.

Creating an ODBC SQL Gateway Connection

To define a gateway connection for an ODBC-compliant data source, perform the following steps:

  1. Define an ODBC data source name (DSN) for the external database. See the documentation for the external database for information on how to do this.

  2. In the Management Portal, go to the System Administration > Configuration > Connectivity > SQL Gateway Connections page.

  3. Click Create New Connection.

  4. On the Gateway Connection page, enter or choose values for the following fields:

    • For Type, choose ODBC.

    • Connection Name — Specify an identifier for the connection, for use within Caché.

    • Select an existing DSN — Choose the DSN that you previously created. You must use a DSN, since the ODBC SQL Gateway does not support connections without a DSN.

    • User — Specify the name for the account to serve as the default for establishing connections, if needed.

    • Password — Specify the password associated with the default account.

    For example, a typical connection might use the following values:

    Setting Value
    Type ODBC
    Connection Name ConnectionODBC1
    Select an existing DSN MyAccessPlayground
    User DBOwner
    Password DBPassword

    Also see “Implementation-specific Options” later in this chapter.

  5. Optionally test if the values are valid. To do so, click the Test Connection button. The screen will display a message indicating whether the values you have entered in the previous step allow for a valid connection.

  6. To create the named connection, click Save.

  7. Click Close.

Creating an ODBC Connection to Caché via the SQL Gateway

Caché provides ODBC drivers and thus can be used as an ODBC data source. That is, a Caché instance can connect to itself or to another Caché instance via ODBC 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.

Connecting to Caché as an ODBC Data Source

To configure a Caché instance (Caché_A) to use another Caché instance (Caché_B) as an ODBC data source, do the following:

  1. On the machine that is running Caché_A, create a DSN that represents the namespace in Caché_B that you want to use. (See “Using an InterSystems database as an ODBC Data Source on Windows” or “Using an InterSystems database as an ODBC Data Source on UNIX®” for OS-specific instructions on how to create a DSN.)

    Tip:

    If Caché_B is installed on this machine, a suitable DSN might already be available, because when you install Caché, the installer automatically creates DSNs.

  2. Within Caché_A, use the SQL Gateway to create an ODBC connection that uses that DSN. Provide the following details:

    • For Type, choose ODBC.

    • Connection Name — Specify an identifier for the connection, for use within Caché_A.

    • Select an existing DSN — Choose the DSN that you previously created for Caché_B.

    For example, a typical connection might use the following values:

    Setting Value
    Type ODBC
    Connection Name Cache2Samples
    Select an existing DSN Cache2Samples
    Tip:

    You do not need to specify User and Password because that information is part of the DSN itself.

  3. Click Save.

  4. Click Close.

Implementation-specific Options

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. The following options do not apply to all driver implementations.

Legacy Outer Join

The Enable legacy outer join syntax (Sybase) option controls whether the SQL gateway connection will enable you use to use legacy outer joins. Legacy outer joins use SQL syntax that predates the SQL-92 standard. To find out whether the external database supports such joins, consult the documentation for that database.

Needs Long Data Length

The Needs long data length option controls how the SQL gateway connection will bind data. The value of this option should agree with the SQL_NEED_LONG_DATA_LEN setting of the database driver. To find the value of this setting, use the ODBC SQLGetInfo function. If SQL_NEED_LONG_DATA_LEN equals Y, then select the Needs long data length option; otherwise clear it.

Supports Unicode Streams

The Supports Unicode streams option controls whether the SQL gateway connection supports Unicode data in streams, which are fields of type LONGVARCHAR or LONGVARBINARY.

  • Clear this check box for Sybase. If you are using a Sybase database, all fields you access via the SQL gateway should include only UTF-8 data.

  • Select this check box for other databases.

Do Not Use Delimited Identifiers by Default

The Do not use delimited identifiers by default option controls the format of identifiers in the generated routines.

Select this check box if you are using a database that does not support delimited SQL identifiers. This currently includes the following databases:

  • Sybase

  • Informix

  • MS SQL Server

Clear the check box if you are using any other database. All SQL identifiers will be delimited.

Use COALESCE

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, which is consistent with the behavior of typical ODBC clients.

Whether you select this option depends on your preferences and on whether the external database supports the COALESCE function.

To find out whether the external database supports the COALESCE function, consult the documentation for that database.

Conversion in Composite Row IDs

The Conversion in composite Row IDs option controls how non-character values are treated when forming a composite ID. Choose an option that is supported by your database:

  • Do not convert non-character values — This option performs no conversion. This option is suitable only if your database supports concatenating non-character values to character values.

  • Use CAST — This option uses CAST to convert non-character values to character values.

  • Use {fn convert ...} — This option uses {fn convert ...} to convert non-character values to character values.

In all cases, the IDs are concatenated with || between the IDs (or transformed IDs).

Consult the documentation for the external database to find out which option or options it supports.

Using the UNIX® ODBC SQL Gateway Test Program

Within a full UNIX® Caché installation, you can use a special program to test gateway access from Caché.

Note:

The test program uses the default 8-bit iODBC–compliant drivers (libcacheodbc.so and cgate.so). See “Key File Names” in the chapter on “ODBC Installation and Validation on UNIX® Systems” for a complete list of the Caché ODBC client drivers and Caché SQL Gateway drivers available for supported UNIX® platforms.

The gateway test program consists of files in the directory install-dir/dev/odbc/samples/sqlgateway

  • gatewaytest.sh — The shell script that runs the test. This script defines the ODBCINI environment variable (so that the ODBC initialization file can be found), sets up the search path to find the driver manager, and then accesses a DSN named samples, and executes a routine. This DSN is defined in the sample ODBC initialization file and points to the Caché SAMPLES namespace.

    You may need to modify the shell script, depending on your configuration. See the section “Modifying the Shell Script for the SELECT Test” for details.

  • SQLGatewayTest.ro — A routine that makes the callout to the Caché SAMPLES namespace using iODBC and the Caché ODBC client driver libcacheodbc.so.

To use the test program:

  1. Go to install-dir/dev/odbc/samples/

  2. Execute the test script by typing the following:

    ./sqlgateway/gatewaytest.sh
    
    

The gatewaytest.sh script does the following:

  1. It starts a Caché session and runs the routine SQLGatewayTest in the SAMPLES namespace.

  2. This application routine then loads the default Caché SQL Gateway driver, cgate.so, which is linked against the iODBC driver manager.

  3. The driver manager loads the client driver using information from the ODBC initialization file.

  4. The client driver then establishes a TCP/IP connection to port 1972 and is connected to the Caché SAMPLES namespace using the DSN definition from the ODBC initialization file.

  5. The routine executes the following query:

    SELECT * FROM SAMPLE.PERSON
    
  6. The routine then fetches the first ten rows of the result set.

The difference between this example and the simple select test is that in gatewaytest.sh, the Caché process making the initial call is the client application. Typically, a Gateway call from Caché calls the DSN of another vendor’s database.

Using the ODBC SQL Gateway Programmatically

If you require options that are not provided by the standard SQL Gateway wizards, you can use the %SQLGatewayConnectionOpens in a new tab class to access an ODBC-compliant database programmatically. You can either execute a dynamic query (obtaining a result set) or you can perform low-level ODBC programming.

To use this section, you should have some experience with ODBC — this book does not provide details on the ODBC functions. You should also have a basic familiarity with ObjectScript and the InterSystems IDE.

If you encounter any problems, you can monitor the gateway by enabling logging for ODBC (as described in “Logging and Environment Variables”).

Creating and Using an External Data Set

To create and use a data set that queries an external database, do the following:

  1. Create an instance of %SQLGatewayConnectionOpens in a new tab via the %New method.

  2. Call the Connect method of that instance, passing arguments that specify the ODBC data source name, as well as the username and password that are needed to log into that source, if necessary.

    The Connect method has the following signature:

    method Connect(dsn, usr, pwd, timeout) returns %Status
    
    

    Here dsn is the DSN for the data source, usr is a user who can log into that data source, pwd is the corresponding password, and timeout specifies how long to wait for a connection.

    For more information on connecting, see “Managing the Connection” in the section on Performing ODBC Programming.

  3. Create an instance of %ResultSetOpens in a new tab via the %New method, providing the string argument "%DynamicQueryGW:SQLGW".

    Note:

    Notice that this is slightly different from the argument that you use with a typical dynamic query ("%DynamicQuery:SQL").

  4. Invoke the Prepare method of the data set. The first argument should be a string that consists of a SQL query, the second argument should be omitted, and the third argument should be the instance of %SQLGatewayConnectionOpens in a new tab.

    This method returns a status, which should be checked.

  5. Call the Execute method of the data set, optionally providing any arguments in the order expected by the query. This method returns a status, which should be checked.

To use the data set, you generally examine it one row at a time. You use methods of %ResultSetOpens in a new tab to retrieve information such as the value in a given column.

To advance to the next row, you use the Next method; typically you iterate through all the rows until you reach the end, when Next returns 0. The Next method also returns 0 if an error occurs.

An example follows:

ClassMethod SelectAndWrite() as %Status
{
    Set conn=##class(%SQLGatewayConnection).%New()
    Set sc=conn.Connect("AccessPlayground","","")
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set res=##class(%ResultSet).%New("%DynamicQueryGW:SQLGW")
    Set sc=res.Prepare("SELECT * FROM PEOPLE",,conn)
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    Set sc=res.Execute()
    If $$$ISERR(sc) do $System.Status.DisplayError(sc) quit

    While res.Next()
    { Write !,res.GetData(1)," ",res.GetData(2)," ",res.GetData(3)
    }
    Set sc=conn.Disconnect()
    Quit sc
}

For more information on %ResultSetOpens in a new tab, see the chapter “Dynamic SQL” in Using Caché SQL. Also see the class documentation for %ResultSetOpens in a new tab.

Performing ODBC Programming

If %ResultSetOpens in a new tab does not provide enough control, you can perform ODBC programming. The %SQLGatewayConnectionOpens in a new tab class provides a set of methods that correspond to ODBC functions, as well as other utility functions. With this class, you can connect to and use an ODBC-compliant database and then perform low-level ODBC programming. The overall procedure is as follows:

  1. Create an instance of %SQLGatewayConnectionOpens in a new tab via the %New method.

  2. Call the Connect method of that instance, passing arguments that specify the ODBC data source name, as well as the username and password that are needed to log into that source, if necessary.

    For more information on connecting, see “Managing the Connection.”

  3. Call the AllocateStatement method and receive (by reference) a statement handle.

  4. Call other methods of the gateway instance, using that statement handle as an argument. Most of these methods call ODBC functions.

This section discusses the following:

Note:

This section assumes that you are familiar with ODBC programming.

Null Values and Empty Strings

When you use the methods described in this chapter, remember that Caché and SQL have the following important differences:

  • In SQL, "" represents an empty string.

  • In Caché, "" equals null.

  • In Caché, $char(0) equals an empty string.

Checking the Status

Most of the methods of %SQLGatewayConnectionOpens in a new tab return a status, which you should check. Status information is also available via the following properties and methods:

sqlcode property (%Integer)

Contains the SQL code return by the last call (if any).

GatewayStatus property (%Integer)

Indicates the status of the last call. This is one of the following:

  • 0 - success

  • -1 - SQL error

  • -1000 - critical error

GetLastSQLCode() method
method GetLastSQLCode() returns %Integer

Returns an SQL code for the last call if this call does not return an SQL code (for example, if you used SQLGetData).

GatewayStatusGet() method
method GatewayStatusGet() returns %Integer

Returns an error code for the last call. It does not initialize the error code and can be called multiple times. See the previous notes for the GatewayStatus property.

Managing the Connection

The %SQLGatewayConnectionOpens in a new tab class provides properties and methods that you can use to manage the connection to the external data source.

DSN property (%String)

Data source name of the ODBC-compliant data source to which you want to connect.

User property (%String)

Username to log into the data source.

Password property (%String)

Associated password.

ConnectionHandle property (%Binary)

The current connection handle to the ODBC-compliant data source.

Connect() method
method Connect(dsn, usr, pwd, timeout) returns %Status

Establishes a connection to a DSN. If username and password are both empty, this method calls the ODBC function SQLDriverConnect. If that call is unsuccessful or username/password are specified, the method calls the ODBC function SQLConnect.

If the timeout parameter is not 0, SQLSetConnectAttr is first called to set SQL_ATTR_LOGIN_TIMEOUT.

GetConnection() method
method GetConnection(conn, timeout) returns %Status

Establishes a connection. This method uses an entry from the Caché configuration to determine the DSN, username, and password.

SetConnectOption() method
method SetConnectOption(opt, val) returns %Status

Invokes the ODBC function SQLSetConnectAttr. Only integer values are supported. Integer values for the opt argument may be taken from the sql.h and sqlext.h header files.

Disconnect() method
method Disconnect() returns %Status

Closes the connection.

Basic Methods

The following table lists the supported ODBC functions and indicates which methods access those functions. For details on the method arguments, actions, and return values, see the class reference for %SQLGatewayConnectionOpens in a new tab.

Calling ODBC Functions from %SQLGatewayConnection
ODBC Function Method That Calls This Function
SQLAllocHandle AllocateStatement()
SQLBindParameter BindParameter()
SQLCloseCursor CloseCursor()
SQLColAttribute DescribeCols()
SQLColumns Columns()
SQLColumnsW ColumnsW()
SQLDescribeCols DescribeCols()
SQLDescribeParam DescribeParam()
SQLDiagRec GetErrorList()
SQLEndTran Transact()
SQLExecute Execute()
SQLFetch Fetch()
SQLFreeHandle DropStatement()
SQLFreeStmt UnbindParameters()
SQLGetData GetData()
SQLGetDataW GetDataW()
SQLGetInfo GetInfo()
SQLMoreResults MoreResults()
SQLNumParams DescribeParameters()
SQLParamData ParamData()
SQLPrepare Prepare()
SQLPrepareW PrepareW()
SQLPrimaryKeys PrimaryKeys()
SQLPrimaryKeys PrimaryKeysW()
SQLProcedureColumns ProcedureColumns()
SQLProcedureColumnsW ProcedureColumnsW()
SQLProcedures Procedures()
SQLPutData PutData()
SQLPutDataW PutDataW()
SQLRowCount RowCount()
SQLSetConnectAttr SetConnectOption()
SQLSetStmtAttr SetStmtOption()
SQLSpecialColumns SpecialColumns()
SQLSpecialColumnsW SpecialColumnsW()
SQLTables Tables()
SQLTablesW TablesW()

Getting Information about the Shared Library

The %SQLGatewayConnectionOpens in a new tab class provides properties and methods that you can call to get information about the shared library used by the ODBC SQL Gateway.

Note:

The phrase shared library refers in general to the file or library that comprises the ODBC SQL Gateway. On Windows platforms, this is a file with the extension .dll, but the filename is different on other platforms (see “Key File Names” for a complete list of Caché SQL Gateway shared objects available for supported UNIX® platforms). The properties and methods described here apply in all cases.

DLLHandle property (%Binary)

Handle for the shared library, as currently in use. This is set when you connect.

DLLName property (%String)

Name of the shared library currently in use. This is set when you connect.

GetGTWVersion() method
method GetGTWVersion() returns %Integer

Returns the current version of the shared library.

GetUV() method
method GetUV(ByRef infoval) returns %Status

Returns (by reference) whether the shared library was built as Unicode. Note that this method always returns a status of $$$OK.

Unloading the Shared Library

The %SQLGatewayConnectionOpens in a new tab class provides a method that you can use to unload the shared library for the ODBC SQL Gateway.

UnloadDLL() method
method UnloadDLL() returns %Status

Unloads the shared library from the process memory.

Other Methods

The %SQLGatewayConnectionOpens in a new tab class provides other utility methods:

FetchRows()
method FetchRows(hstmt, Output rlist As %List, nrows As %Integer) returns %Status

Returns (by reference) a specified number of rows for the given connection handle. Here hstmt is the connection handle, returned (by reference) from AllocateStatement(). Also, rlist is the returned list of rows; this is a Caché $list. Each item in the list contains a row. If there is no data (SQL_CODE = 100), fetching is assumed to be successful but the return list is empty.

Caution:

This method is primarily useful for testing, and it truncates character fields up to 120 characters so that more fields would fit in a row. Use GetData() instead when you need non-truncated data.

GetOneRow()
method GetOneRow(hstmt, ByRef row) returns %Status

Returns (by reference) the next row for the given connection handle. Here hstmt is the connection handle, returned (by reference) from AllocateStatement(). Also, row is the returned row, a Caché $list. Each item in the list contains a field. If there is no data (SQL_CODE = 100), fetching is assumed to be successful but the return list is empty.

Caution:

This method is primarily useful for testing, and it truncates character fields up to 120 characters so that more fields would fit in a row. Use GetData() instead when you need non-truncated data.

GetParameter()
method GetParameter(hstmt, pnbr, ByRef value) returns %Status

Returns (by reference) the current value of the indicated parameter. Here hstmt is the connection handle returned (by reference) from AllocateStatement() and pnbr is the ordinal number of the parameter.

SetParameter()
method SetParameter(hstmt, pvalue, pnbr) returns %Status

Sets the value of a previously bound parameter. Here hstmt is the connection handle returned (by reference) from AllocateStatement(), pvalue is the value to use, and pnbr is the ordinal number of the parameter. The parameters are stored in $list format. If the allocated buffer is not sufficient, a new buffer will be allocated.

Example

The following shows a simple example that executes a query:

ClassMethod ExecuteQuery(mTable As %String)
{
 set mDSN="DSNtest"
 set mUsrName="SYSDBA"
 set mUsrPwd="masterkey"

 set mx=##class(%SQLGatewayConnection).%New()
 set status=mx.Connect(mDSN,mUsrName,mUsrPwd)
 if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
 set hstmt=""
 set status=mx.AllocateStatement(.hstmt)
 if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
 set status=mx.Prepare(hstmt,"SELECT * FROM "_mTable)
 if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
 set status=mx.Execute(hstmt)
 if $$$ISERR(status) do $System.Status.DisplayError(status) quit $$$ERROR()
 quit mx.Disconnect()
}
FeedbackOpens in a new tab