Using Caché as an ODBC Data Source on UNIX®

Introduction to the UNIX® ODBC Initialization File

The ODBC initialization file is used as follows:

• It provides information so that the driver manager can locate and connect to an available DSN, including the path of the ODBC client driver required for that particular connection.

• It defines the DSNs (and optionally includes login credentials for them). The ODBC client drivers use this information.

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:

   ODBCINI=/usr/cachesys/cacheodbc.ini
   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:

   SYSODBCINI=/usr/cachesys/cacheodbc.ini
   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:

    mv libodbc.so libodbc.so.old 

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]
samples=samples

[samples]
Driver          = /usr/cachesys/bin/libcacheodbc.so
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/localhost.domain.com

[Default]
Driver = /usr/cachesys/bin/libcacheodbc.so

This file includes the following variables:

• ODBC Data Sources — Lists all DSNs for the file. Each entry is of the form “DSNName=SectionHeading”, where DSNName is the name specified by the client application and the SectionHeading specifies the heading under which DSN information appears in this file.

• Driver — Specifies the location of the client driver file to use for this DSN. In this case this is the file libcacheodbc.so.

• Description — Contains an optional description of the DSN.

• Host — Specifies the IP address of the DSN in dotted decimal or dotted quad form, such as “127.0.0.1”.

• Namespace — Specifies the namespace for the DSN.

• UID — Specifies the username for logging into the DSN. By default, this is “_SYSTEM” and is not case-sensitive.

• Password — Specifies the password for the account specified by the UID entry. For the SYSTEM username, the password is “SYS” and is case-sensitive.

  Note:

  Because it is an ODBC standard to allow the storing of usernames and passwords in clear text, the sample initialization file includes the username and password required to access the sample DSN. This is meant merely as an example. A secure ODBC program prompts the user for this information and does not store it, in which case it does not appear in the initialization file at all.

• Port — Specifies the port for connecting to the DSN. The default for Caché is 1972.

• Protocol — Specifies the protocol for connecting to the DSN. For Caché, this is always TCP.

• Query Timeout — If 1, causes the ODBC client driver to ignore the value of the ODBC query timeout setting.

  The ODBC query timeout setting specifies how long a client should wait for a specific operation to finish. If an operation does not finish within the specified time, it is automatically cancelled. The ODBC API provides functions to set this timeout value programmatically. Some ODBC applications, however, hard-code this value. If you are using an ODBC application that does not allow you to set the timeout value and the timeout value is too small, you can use the Disable Query Timeout option to disable timeouts.

• Static Cursors — If 1, enables the Caché ODBC client driver’s static cursor support. If 0, then the cursor support provided by the ODBC Cursor Library will be used. In general, this flag should be off (that is, set to 0) unless you have a specific reason for not using the ODBC Cursor Library.

• Trace — Specifies whether the driver manager performs logging (“on”) or not (“off”); by default, logging is off (see the chapter on “Logging” for more information).

• TraceFile — If logging is enabled by the Trace entry, specifies the location of the driver manager log file.

• Authentication Method — Specify 0 for password authentication or 1 for Kerberos.

• Security Level — Specify this if you use Kerberos for authentication. The allowed values are as follows:

  • 1 = Kerberos

  • 2 = Kerberos with packet integrity

  • 3 = Kerberos with encryption

• Service Principal Name — Specify this if you use Kerberos for authentication. This should be the name of the service principal that represents Caché.

For more information on Kerberos, see the Caché Security Administration Guide.

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 HOWTOOpens in a new tab document on the iODBC web site (www.iodbc.orgOpens in a new tab).

   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.

3. Download the PHP source kit from http://www.php.netOpens in a new tab and un-tar it.

4. Download the Apache HTTP server source kit from http://httpd.apache.org/Opens in a new tab 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 http://127.0.0.1/info.php.

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 libcacheodbc.so 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 http://127.0.0.1/sample.php

Using the Select Test Program

The Caché 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 < 11
  

  

  It then executes the select program using a DSN named samples. This DSN is defined in the sample ODBC initialization file and points to the Caché 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.