Using Caché with ODBC
Using Caché as an ODBC Data Source on UNIX®
[Home] [Back] [Next]
InterSystems: The power behind what matters   
Class Reference   

An external application can use Caché as an ODBC data source. This chapter describes how to do this on UNIX®. It discusses the following topics:

If you are performing custom configuration of the Caché ODBC driver on UNIX®, you should be familiar with using UNIX®, compiling and linking code, writing shell scripts, and other such tasks.
The sample ODBC initialization file and test files may include the _SYSTEM/SYS or _system/sys username-password pair in unencrypted form. It is recommended that you remove this data before deployment; it is also recommended that you remove the _SYSTEM account before deployment.
Performing a Stand-alone Installation
By default, Caché performs a full ODBC installation with a standard installation. If you perform a custom installation (as described in Caché Installation Guide), you can select “SQL client only” option to install only the client access components (ODBC client driver).
In addition, however, Caché provides a stand-alone installer for Caché ODBC. To use this installer:
  1. Create the directory where you wish to install the client, such as /usr/cacheodbc/.
  2. Copy the appropriate zipped tar file from the Caché DVD into the directory that you just created.
    On the Caché DVD, the ./dist/ODBC/ directory contains zipped tar files with names like the following:
    where release-code is a release-specific code (that varies among Caché versions and releases) and platform specifies the operating system that the ODBC client runs on.
  3. Go to the directory you created and manually unpack the .tar file, as follows:
    # gunzip ODBC-release-code-platform.tar.Z
    # tar xvf ODBC-release-code-platform.tar
    This creates bin and dev directories and installs a set of files.
  4. Run the ODBCInstall program, which will be in the directory that you created. This program creates several sample scripts and configures cacheodbc.ini under the mgr directory. For example:
    # pwd
    # ./ODBCInstall
Identifying the correct platform name
In some releases, the ./dist/ODBC/ directory contains the following command to display the platform name that identifies the file you need:
# ./cplatname identify
This command is not present in releases where it is not required.
Key File Names
Depending on your configuration needs, it may be useful to know the specific file names of some of the installed components. In the following lists, install-dir is the Caché installation directory (the path that $SYSTEM.Util.InstallDirectory() returns on your system).
ODBC driver managers
The install-dir/bin/ directory contains the following driver managers:
ODBC on 64-bit UNIX® platforms
Between releases of the ODBC specification, various data types such as SQLLen and SQLULen changed from being 32-bit values to 64-bit values. While these values have always been 64-bit on iODBC, they have changed from 32-bit to 64-bit on unixODBC. As of unixODBC version 2.2.14, the default build uses 64-bit integer values. Caché drivers are available for both 32-bit and 64-bit versions of unixODBC.
Caché ODBC client drivers
Caché ODBC client drivers are provided for both ODBC 2.5 and ODBC 3.5. The ODBC 3.5 versions will convert 3.5 requests to the older 2.5 automatically, so in most cases either driver can be used. The install-dir/bin/ directory contains the following versions (*.so or *.sl):
iODBC-compliant drivers
unixODBC-compliant drivers
Caché SQL Gateway drivers
The install-dir/bin/ directory contains the following versions of the shared object used by the Caché SQL Gateway. This enables you to connect from Caché to other ODBC client drivers. These files are not installed if you perform a stand-alone installation.
linked against iODBC
linked against unixODBC
Other files
The install-dir/mgr/cacheodbc.ini file is a sample ODBC initialization file.
The files for the test programs are discussed in Testing the Caché ODBC Configuration.
Troubleshooting for Shared Object Dependencies
After installing, you should validate dependencies on other shared objects and correct any problems. The process is as follows:
  1. Use the appropriate command to list the dynamic dependencies of the Caché ODBC driver.
    For example, on Solaris and other platforms, the command is ldd:
    # ldd install-dir/bin/
    Here install-dir is the directory where Caché is installed. If no dependencies are found, you will see a message like the following: => not found
  2. If there are no errors, then all dependencies are valid; if there are errors, run the following commands to force the shared object loader to look in the current directory:
    # sh
    # cd install-dir/bin 
    # export LD_LIBRARY_PATH
    The sh command starts the Bourne shell; the cd command changes to the appropriate directory; and the export command sets the path to look up shared objects.
    Note that on AIX®, you would use LIBPATH instead of LD_LIBRARY_PATH.
  3. Once you have added the current directory to the path, run ldd again and check for missing dependencies. If any shared objects cannot be found, add them to the same directory as the ODBC client driver.
Configuring the ODBC Initialization File
This section describes how to create a DSN for a Caché database on UNIX®, which you do by editing the ODBC initialization file. Caché provides a sample.
Introduction to the UNIX® ODBC Initialization File
The ODBC initialization file is used as follows:
Name and Location of the Initialization File
The initialization file can have any name, but, typically, it is called .odbc.ini when it is located in a user’s personal directory, odbc.ini when located in an ODBC-specific directory. The Caché sample is called cacheodbc.ini and is located in the install-dir/mgr directory.
To locate this file, the Caché ODBC client driver uses the same search order as iODBC. It looks for the file in the following places, in this order:
  1. The file specified by the ODBCINI environment variable, if this is defined. When defined, this variable specifies a path and file, such as:
    export ODBCINI
  2. The .odbc.ini file in the directory specified by the user’s $HOME variable, if $HOME is defined and if .odbc.ini exists.
  3. If $HOME is not defined, the .odbc.ini file in the “home” directory specified in the passwd file.
  4. The file specified by the system-wide SYSODBCINI environment variable, if this is defined. When defined, this variable specifies a path and file, such as:
    export SYSODBCINI 
  5. The file odbc.ini file located in the default directory for building the iODBC driver manager (/etc/), so that the full path and file name are /etc/odbc.ini.
To use a different odbc.ini file, delete or rename the Caché sample initialization file to allow the driver manager to search the $HOME or /etc/odbc.ini paths. For example, go to <cache_sys>/bin and execute the following command:
and then move your user-defined odbc.ini to etc/odbc, where the driver manager can find it.
Details of the ODBC Initialization File
The following is a sample initialization file for the Caché ODBC driver:
[ODBC Data Sources]

Driver          = /usr/cachesys/bin/
Description     = Cache ODBC driver
Host            = localhost
Namespace       = SAMPLES
UID             = _SYSTEM
Password        = SYS
Port            = 1972
Protocol        = TCP
Query Timeout   = 1
Static Cursors  = 0
Trace           = off
TraceFile       = iodbctrace.log 
Authentication Method   = 0
Security Level          = 2
Service Principal Name  = cache/

Driver = /usr/cachesys/bin/
This file includes the following variables:
For more information on Kerberos, see the Caché Security Administration Guide.
Custom Installation and Configuration for iODBC
If you want to build your own iODBC driver manager to operate under custom conditions, you can do so. The iODBC executable and include files are in the directory install-dir/dev/odbc/redist/iodbc/. You need to set LD_LIBRARY_PATH (LIBPATH on AIX®) and the include path in order to use these directories to build your applications.
If you want to customize the iODBC driver manager, you can also do that. Download the source from the iODBC Web site ( and follow the instructions.
Configuring PHP with iODBC
You can use the ODBC functionality of Caché in conjunction with PHP (PHP: Hypertext Processor, which is a recursive acronym). PHP is a scripting language that allows developers to create dynamically generated pages. The process is as follows:
  1. Get or have root privileges on the machine where you are performing the installation.
  2. Install the iODBC driver manager. To do this:
    1. Download the kit.
    2. Perform a standard installation and configuration, as described earlier in this chapter.
    3. Configure the driver manager for use with PHP as described in the iODBC+PHP HOWTO document on the iODBC web site (
    Note that LD_LIBRARY_PATH (LIBPATH on AIX®) in the iODBC PHP example does not get set, due to security protections in the default PHP configuration. Also, copy to /usr/lib and run ldconfig to register it without using LD_LIBRARY_PATH.
  3. Download the PHP source kit from and un-tar it.
  4. Download the Apache HTTP server source kit from and un-tar it.
  5. Build PHP and install it.
  6. Build the Apache HTTP server, install it, and start it.
  7. Test PHP and the Web server using info.php in the Apache root directory, as specified in the Apache configuration file (often httpd.conf). The URL for this is
  8. Copy the Caché-specific initialization file, cacheodbc.ini to /etc/odbc.ini because this location functions better with the Apache Web server if the $HOME environment variable is not defined.
  9. Configure and test the client driver file.
  10. Copy the sample.php file from the Caché ODBC kit to Apache root directory (that is, the directory where info.php is located), and tailor it to your machine for the location of Caché.
  11. You can then run the sample.php program, which uses the Caché SAMPLES namespace, by pointing your browser to
Testing the Caché ODBC Configuration
You should test the ODBC configuration to make sure that the Caché ODBC driver and the driver manager have been installed and configured correctly.
To test the ODBC configuration, you can use the following tools:
Using the Select Test Program
The Caché select test program consists of files in the directory install-dir/dev/odbc/samples/select
Modifying the Shell Script for the SELECT Test
You may need to modify the shell script (, depending on your configuration:
Using the SELECT Test
To use the test program:
  1. Execute the test script by typing the following:
    ./select dsn
    where dsn is the name of the DSN that you want to use in the test.
This test works as follows:
  1. The shell script calls the select program.
  2. The select program is linked to a driver manager, which reads the ODBC initialization file to get connection information for the given DSN.
  3. The driver manager determines the location of the Caché ODBC client driver and loads it into memory.
  4. The client driver then establishes a TCP/IP connection to the port specified in the ODBC initialization file and is connected to the given Caché namespace using the DSN definition from the ODBC initialization file.
  5. Once the connection is established, the client application executes your SELECT statement against the Caché database.