Using Caché with ODBC
ODBC Installation and Validation on UNIX® Systems
This chapter provides detailed information about ODBC installation and validation on UNIX® and related operating systems. It discusses the following topics:
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.
You should test the ODBC configuration to make sure that the InterSystems ODBC driver and the driver manager have been installed and configured correctly. In addition to tests provided with the unixODBC driver manager, you can run the InterSystems Select Test program
, which provides specific tests for the InterSystems ODBC driver. The program allows you to specify a DSN and execute a SELECT statement on that connection.
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 executes the following SELECT statement:
select * from sample.person where ID < 11
It then executes the select
program using a DSN named sampleodbc
. This DSN is defined in the sample ODBC initialization file and points to the InterSystems SAMPLES
The executable built from select.c
. This is a sample ODBC program already linked with the iODBC driver manager.
This is the source code for the select program. This source is provided in case you want to make change and compile and link it yourself.
Modifying the Shell Script for the SELECT Test
You may need to modify the shell script (select.sh
), depending on your configuration:
The shell script is designed to work with Caché login or unauthenticated modes and in Minimal or Normal security installations. It may need modification in other cases.
By default, the shell script sets up the search paths to find the iODBC driver manager. You would change this if you use the unixODBC driver manager or if you install iODBC in a non-default way.
The script also assumes that the ODBC initialization file is in the install-dir/mgr
directory. You should adjust the script as needed to find the ODBC initialization file on your system.
Execute the test script by typing the following:
is the name of the DSN that you want to use in the test.
This test works as follows:
The shell script calls the select
program is linked to a driver manager, which reads the ODBC initialization file to get connection information for the given DSN.
The driver manager determines the location of the InterSystems ODBC client driver and loads it into memory.
The client driver then establishes a TCP/IP connection to the port specified in the ODBC initialization file and is connected to the given namespace using the DSN definition from the ODBC initialization file.
Once the connection is established, the client application executes your SELECT statement against the InterSystems database.
After installing, you should validate dependencies on other shared objects and correct any problems. The process is as follows:
Use the appropriate command to list the dynamic dependencies of the InterSystems ODBC driver.
For example, on Solaris and other platforms, the command is ldd
# ldd install-dir/bin/libcacheodbc.so
is the InterSystems installation directory. If no dependencies are found, you will see a message like the following:
libstlport_gcc.so => not found
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:
# cd install-dir/bin
# export LD_LIBRARY_PATH
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.
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.
In addition, however, Caché provides a stand-alone installer for Caché ODBC. To use this installer:
Create the directory where you wish to install the client, such as /usr/cacheodbc/
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:
is a release-specific code (that varies among Caché versions and releases) and platform
specifies the operating system that the ODBC client runs on.
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
directories and installs a set of files.
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:
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:
This command is not present in releases where it is not required.
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
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 (www.iodbc.org
) and follow the instructions.
You can use InterSystems ODBC functionality 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:
Get or have root privileges on the machine where you are performing the installation.
Install the iODBC driver manager. To do this:
Perform a standard installation and configuration, as described earlier in this chapter.
Build PHP and install it.
Build the Apache HTTP server, install it, and start it.
Copy the InterSystems-specific initialization file, cacheodbc.ini
because this location functions better with the Apache Web server if the $HOME
environment variable is not defined.
Copy the sample.php
file from the InterSystems 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 your InterSystems installation directory.
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 InterSystems installation directory (the path that $SYSTEM.Util.InstallDirectory()
returns on your system).
ODBC driver managers
The iODBC driver manager, which supports both 8-bit and Unicode ODBC APIs.
The unixODBC driver manager, for use with the 8-bit ODBC API.
ODBC on 64-bit UNIX® platforms
Between releases of the ODBC specification, various data types such as SQLLen
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. InterSystems drivers are available for both 32-bit and 64-bit versions of unixODBC.
InterSystems ODBC client drivers
InterSystems 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
InterSystems SQL Gateway drivers
directory contains the following versions of the shared object used by the InterSystems 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.