Skip to main content

UNIX® ODBC Driver Managers

ODBC driver managers provide services that load ODBC client drivers and route API calls between your application and the database. On Windows the driver manager is built into the system, but most non-Windows operating systems use one of two open source driver managers, iODBC (for MacOS) or unixODBC (default for most other distributions).

Both driver managers parse configuration files, but they come with different sets of command-line tools. unixODBC includes the odbcinst tool for managing configuration paths, and the isql interactive command line to test database connections. iODBC uses iodbc-config to check compiler flags, and the iodbctest tool to verify active DSN connections.

InterSystems ODBC drivers come in versions for each driver manager. The drivers are compiled against a specific manager (iODBC or unixODBC) because they require support libraries that are linked against that driver manager.

InterSystems also offers compliant versions of the shared objects used by the InterSystems SQL Gateway. This enables you to connect from InterSystems IRIS to other ODBC client drivers.

The unixODBC Driver Manager

unixODBC (unixodbc.orgOpens in a new tab) is the default standard for most Linux distributions. If you install an ODBC utility or database driver via a Linux package manager, it will almost always default to linking against unixODBC. It follows the Microsoft Windows MDAC standard, and expects SQLWCHAR to be 2 bytes (UCS-2/UTF-16).

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. InterSystems drivers are available for both 32-bit and 64-bit versions of unixODBC. The default build now uses 64-bit integer values.

SQL Gateway Connectors for unixODBC

InterSystems offers compliant versions of the shared objects used by the InterSystems SQL Gateway, suitable for different third party drivers. This enables you to connect from InterSystems IRIS to other ODBC client drivers. These files are not installed by default if you perform a stand-alone ODBC installation.

If you are installing a third party database driver compiled with unixODBC support (for example, the MS SQL Server ODBC driver), you must back up odbcgateway.so and rename odbcgatewayur64.so to odbcgateway.so. This change must be made for every upgrade, since the upgrade will overwrite odbcgateway.so.

When using third-party shared libraries on a UNIX® system, LD_LIBRARY_PATH must be defined by setting the InterSystems IRIS LibPath parameter (see “LibPath” in the Configuration Parameter File Reference). This is a security measure to prevent unprivileged users from changing the path.

Note:
Available unixODBC Driver Files

For a list of all currently available unixODBC-compliant InterSystems driver files, see unixODBC Compliant Drivers in InterSystems Supported Platforms.

The iODBC Driver Manager

iODBC (iodbc.orgOpens in a new tab) is the native ODBC driver manager for MacOS, and is also available for many UNIX and Linux implementations. iODBC handles 4-byte Unicode (UCS-4/UTF-32) as well as 2 bytes (UCS-2/UTF-16).

You can build your own iODBC driver manager to operate under custom conditions (see iODBC Driver Manager Custom Installation).

SQL Gateway Connectors for iODBC

InterSystems offers compliant versions of the shared objects used by the InterSystems SQL Gateway, suitable for different third party drivers. This enables you to connect from InterSystems IRIS to other ODBC client drivers. These files are not installed by default if you perform a stand-alone ODBC installation.

When using third-party shared libraries on a UNIX® system, LD_LIBRARY_PATH must be defined by setting the InterSystems IRIS LibPath parameter (see “LibPath” in the Configuration Parameter File Reference). This is a security measure to prevent unprivileged users from changing the path.

Note:
Available iODBC Driver Files

For a list of all currently available iODBC-compliant InterSystems driver files, see iODBC Compliant Drivers in InterSystems Supported Platforms.

iODBC Driver Manager Custom Installation

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® or DYLD_LIBRARY_PATH on MacOS) 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.orgOpens in a new tab) and follow the instructions.

Configuring PHP with iODBC

You can use InterSystems ODBC functionality in conjunction with PHP, 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.

    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 https://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 InterSystems-specific initialization file, irisodbc.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 libirisodbc.so client driver file.

  10. Copy the custom.php file (listed below) to the Apache root directory (the directory where info.php is located), and tailor it to your machine for the location of your InterSystems installation directory.

  11. Download and install the sample.person database from https://github.com/intersystems/Samples-DataOpens in a new tab, or modify custom.php to use your preferred sample database.

  12. You can then run the custom.php program by pointing your browser to http://127.0.0.1/custom.php

custom.php listing
<?php
putenv("LD_LIBRARY_PATH=/usr/local/lib");  //This may be blocked by php security
echo $LD_LIBRARY_PATH;
//putenv("ODBCINSTINI=/path/to/odbcinst.ini"); //this location will be determined by your driver install.
//putenv("ODBCINI=/path/to/odbc.ini"); //odbc.ini contains your DSNs, location determined by your driver install.
$dsn="SAMPLES";
$user="_SYSTEM";
$password="sys";

$sql="SELECT * FROM sample.person";
if ($conn_id=odbc_connect("$dsn","","")){
  echo "connected to DSN: $dsn";
  if($result=odbc_do($conn_id, $sql)) {
    echo "executing '$sql'";
    echo "Results: ";
    odbc_result_all($result);
    echo "freeing result";
    odbc_free_result($result);
  }else{
    echo "can not execute '$sql' ";
  }
  echo "closing connection $conn_id";
  odbc_close($conn_id);
}else{
  echo "can not connect to DSN: $dsn ";
}
?>
ODBC Support for Python and Node.jsNext section
Previous sectionInterSystems ODBC Client Installation on UNIX® Systems
FeedbackOpens in a new tab