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

An external application can use InterSystems databases as ODBC data sources. This chapter describes how to create a DSN for an InterSystems database on UNIX®, which you do by editing the ODBC initialization file.

Structure of the ODBC Initialization File
The ODBC initialization file is used as follows:
The following is a sample initialization file for the InterSystems ODBC driver:
[ODBC Data Sources]
sampleodbc=sampleodbc

[sampleodbc]
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  = iris/localhost.domain.com

[Default]
Driver = /usr/cachesys/bin/libcacheodbc.so
This file includes the following variables:
For more information on Kerberos, see the Security Administration Guide.
Setting up a DSN with odbcinst
A UNIX® ODBC installation installs the program odbcinst. The location is dependent on the install but may be located under /usr/local/bin for example.
There are two template files included with a UNIX® installation located in IRIS\dev\odbc\redist\unixodbc. These are:
Edit the template files to suit your configuration. To use them, you can call odbcinst in the following ways:
From: \iris\2018.2.x\dist\dev\odbc\redist\unixodbc\odbcinst.ini_unixODBCtemplate
[InterSystems ODBC]
UsageCount=1
Driver=/home/cache/bin/libirisodbc.so           //or libcacheodbc.so
Setup=/home/cache/bin/libirisodbc.so            //or libcacheodbc.so
SQLLevel=1
FileUsage=0
DriverODBCVer=02.10
ConnectFunctions=YYN
APILevel=1
DEBUG=1
CPTimeout=<not pooled>
Setting up SSL Configuration Files
InterSystems IRIS provides two template files for SSL configuration. The files are located in <Dir>\IRIS\dev\odbc\redist\ssl. The directory also contains a readme.txt file with further information.
irisodbc.ini.template
This is a sample odbc.ini file with an entry named [SampleSSL] that defines an SSL connection. A working file would be named <irisinstalldir>/mgr/irisodbc.ini.
  [ODBC Data Sources] 
  SamplesSSL = SampleSSL 

  [SampleSSL] 
  Driver = /home/guest/iris/bin/libirisodbc35.so 
  Description = IRIS 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 
  Service Principal Name = iris/localhost.domain.com 

  Authentication Method = 2 
  Security Level = 10 
  SSL Server Name = SampleSSLConfig 
In the example above, the last three lines specify the SSL connection. The values must be defined as follows:
In this example, SampleSSLConfig is the SSL Server Name defined in the following sample file, odbcssl.ini.
odbcssl.ini
This is a sample SSL configuration file. In order for a process to initiate an SSL connection with these values:
  [SampleSSLConfig] 
  CAFile=./CA.cer 
  CertFile=./Client.cer 
  KeyFile=./Client.key 
  Password=MixOfAlphaNumericAndPuncChars! 
  KeyType=2 
  Protocols=28 
  CipherList=ALL:!aNULL:!eNULL:!EXP:!SSLv2 
  VerifyPeer=1 
  VerifyDepth=9 
This example defines the following values:
See Using SSL/TLS for detailed information on these values.
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 InterSystems sample is called cacheodbc.ini and is located in the install-dir/mgr directory.
To locate this file, the InterSystems 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 InterSystems sample initialization file to allow the driver manager to search the $HOME or /etc/odbc.ini paths. For example, go to <cachesys>/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.