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:
Testing the InterSystems ODBC Configuration — making sure that the InterSystems ODBC driver and the driver manager have been installed and configured correctly..
Troubleshooting for Shared Object Dependencies — how to validate dependencies on shared objects.
Performing a Stand-alone Installation — installing the InterSystems ODBC client driver and supported driver manager on UNIX®.
Custom Installation and Configuration for iODBC — installing and configuring the iODBC driver manager, and configuring PHP for iODBC.
Key File Names — specific file names of some of important installed components.
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.
Testing the InterSystems ODBC Configuration
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.
There is also a specific test program for the SQL Gateway. For details, see “Using the UNIX® Gateway Test Program” in the SQL Gateway chapter.
Using the Select Test Program
The InterSystems select test program consists of files in the directory install-dir/dev/odbc/samples/select
select.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 executes the following SELECT statement:
select * from sample.person where ID < 11Copy code to clipboard
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 namespace.
select — The executable built from select.c. This is a sample ODBC program already linked with the iODBC driver manager.
select.c — 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 InterSystems 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.
Running the SELECT Test
To use the test program:
Go to the directory install-dir/dev/odbc/samples/select.
Execute the test script by typing the following:
./select dsnCopy code to clipboard
where dsn 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.
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.
Performing a Stand-alone Installation
By default, a full ODBC installation is performed with a standard InterSystems installation. If you perform a custom installation (as described in the Installation Guide), you can select the “SQL client only” option to install only the client access components (ODBC client driver).
In addition, however, a stand-alone installer is provided for InterSystems 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 into the directory that you just created.
The ./dist/ODBC/ directory contains zipped tar files with names like the following:
ODBC-release-code-platform.tar.ZCopy code to clipboard
where release-code is a release-specific code (that varies among InterSystems 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.tarCopy code to clipboard
This creates bin and dev 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:
# pwd /usr/cacheodbc # ./ODBCInstallCopy code to clipboard
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.
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 (www.iodbc.org) and follow the instructions.
Configuring PHP with iODBC
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:
Download the kit.
Perform a standard installation and configuration, as described earlier in this chapter.
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 libiodbc.so to /usr/lib and run ldconfig to register it without using LD_LIBRARY_PATH.
Download the PHP source kit from https://www.php.net and un-tar it.
Download the Apache HTTP server source kit from http://httpd.apache.org/ and un-tar it.
Build PHP and install it.
Build the Apache HTTP server, install it, and start it.
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 http://127.0.0.1/info.php.
Copy the InterSystems-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.
Configure and test the libcacheodbc.so client driver file.
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.
You can then run the sample.php program, which uses the SAMPLES namespace, by pointing your browser to http://127.0.0.1/sample.php
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 InterSystems installation directory (the path that $SYSTEM.Util.InstallDirectory() returns on your system).
The install-dir/bin/ directory contains the following driver managers:
libiodbc.so — The iODBC driver manager, which supports both 8-bit and Unicode ODBC APIs.
libodbc.so — The unixODBC driver manager, for use with the 8-bit ODBC API.
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. InterSystems drivers are available for both 32-bit and 64-bit versions of unixODBC.
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 or *.sl):
libcacheodbc — default driver for 8-bit ODBC 2.5
libcacheodbc35 — supports 8-bit ODBC 3.5
libcacheodbciw — supports Unicode ODBC 2.5
libcacheodbciw35 — supports Unicode ODBC 3.5
libcacheodbciw.dylib — supports Unicode ODBC for MAC OS
libcacheodbcu. — default driver for 8-bit ODBC 2.5
libcacheodbcu35 — supports 8-bit ODBC 3.5
libcacheodbcur64 — supports 8-bit ODBC 2.5 for 64-bit unixODBC
libcacheodbcur6435 — supports 8-bit ODBC 3.5 for 64-bit unixODBC
The install-dir/bin/ 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.
cgate.so — supports 8-bit ODBC.
cgateiw.so — supports Unicode ODBC.
cgateu.so — supports 8-bit ODBC.
cgateur64.so — supports 8-bit ODBC for 64-bit unixODBC
The install-dir/mgr/cacheodbc.ini file is a sample ODBC initialization file.
The files for the test programs are discussed in “Testing the InterSystems ODBC Configuration”.