unixODBC Integration Guide for Altibase#
Overview#
This document describes how to integrate the ODBC driver for Unix provided by Altibase with an ODBC Driver Manager for Unix.
This document is based on the ODBC Driver Manager provided at http://www.unixodbc.org/.
This document is based on the native compiler of each platform. gcc/g++ is not described separately because it requires no special precautions other than selecting the compilation bitness.
Installing unixODBC and building the environment required for its installation are the user's responsibility, so Altibase does not, in principle, provide technical support for those tasks.
The test environment of this document is as follows:
- Altibase: Altibase 6.3.1
- OS: Linux (2.6.32-504.el6.x86_64)
- unixODBC: unixODBC-2.3.2
Info
For errors and improvements related to this document, please contact the technical support portal or technical support center.
- Technical support portal: http://support.altibase.com/en
- Technical support center: +82-2-2082-1114
Info
This document is provided for informational purposes only and is subject to change without prior notice. It may contain errors, and Altibase makes no express or implied warranties of any kind, including but not limited to warranties of merchantability or fitness for a particular purpose.
The development, release, and timing of any features or functionality described for Altibase products in this document are at the sole discretion of Altibase.
Altibase may hold patents, trademarks, copyrights, or other intellectual property rights related to the contents of this document.
unixODBC Manager Installation#
To use unixODBC Manager, its binaries must be built for the target platform.
This chapter describes what is required at each step. It assumes that the native compiler is used; gcc/g++ is not described because it has no options that require special attention.
(However, compile options must be used carefully when building for 32-bit or 64-bit.)
Downloading unixODBC Manager#
unixODBC Manager can be downloaded from the following web address.
- http://www.unixodbc.org -> Click Download on the left side of the screen -> Click the Download button to download the source.
-
The downloaded package is a compressed file in the following format.
unixODBC-2.3.2.tar.gz
Installing unixODBC Manager#
To install unixODBC, perform the following steps.
-
Decompressing the unixODBC file
Decompress the unixODBC archive in an appropriate path.
Shell> mkdir install Shell> cd install Shell> gzip -dc unixODBC*.gz | tar xvf - -
Setting environment variables for compilation
-
Checking the bitness of the ODBC driver
Shell> file $ALTIBASE_HOME/lib/libaltibase_odbc* /altibase_home/lib/libaltibase_odbc-64bit-ul32.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not stripped /altibase_home/lib/libaltibase_odbc-64bit-ul64.so: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, not strippedBased on the bitness identified above, decide whether to compile unixODBC Manager as 32-bit or 64-bit. Note that ul32/ul64 in the ODBC driver file name does not indicate the bitness of the ODBC driver file.
ul32 means the driver uses SQLLEN=4byte (32bit), and ul64 means the driver uses SQLLEN=8byte (64bit). The bitness of the file is determined by "ELF 64-bit" or "ELF 32-bit" in the output of the command above.
The bitness of unixODBC and the ODBC driver must match.
If unixODBC is compiled as 64-bit, the Altibase ODBC driver must also be 64-bit; if unixODBC is 32-bit, use the 32-bit-only Altibase ODBC driver. -
Setting the size of the SQLLEN and SQLULEN types
The SQLLEN and SQLULEN types are used to declare parameter variable types of unixODBC functions. Whether SQLLEN and SQLULEN are 32-bit or 64-bit is determined by the size of SIZEOF_LONG_INT.By default, SQLLEN/SQLULEN is declared as 32-bit on a 32-bit OS and as 64-bit on a 64-bit OS. To use SQLLEN/SQLULEN as 32-bit (4 bytes) on a 64-bit OS, declare the following compilation environment variable.
export CFLAGS=-DBUILD_LEGACY_64_BIT_MODE=1When BUILD_LEGACY_64_BIT_MODE=1 is set as above, SQLLEN is compiled to use a 32-bit (4-byte) size.
Always use 32-bit for the SQLLEN/SQLULEN type size.
It is strongly recommended to use export CFLAGS=-DBUILD_LEGACY_64_BIT_MODE=1 so that SQLLEN is 4 bytes (32 bits). When SQLLEN/SQLULEN is used as 64-bit (8 bytes), errors have been observed in passing values between unixODBC and Altibase.
As a result, when the SQLColAttribute function of unixODBC is used from PHP/Python, the column type identifier passed as an argument may be calculated incorrectly and values may not be retrieved correctly. Therefore, use SQLLEN=4byte (32bit).
In addition, the sqltypes.h provided by Altibase defines SQLLEN as 32-bit by default unless another option is given at compile time, so an application that uses the Altibase sqltypes.h passes SQLLEN to the unixODBC manager as 32-bit.
However, unless a separate option is given, the unixODBC manager uses SQLLEN as 64-bit. The resulting SQLLEN length mismatch between the application and the unixODBC manager can cause the ODBC functions SQLFetch() and SQLMoreResults() to intermittently return incorrect values.
To prevent this as well, it is recommended to use only 32-bit SQLLEN for unixODBC.
-
Selecting the unixODBC compilation bitness
unixODBC can be compiled as either 32-bit or 64-bit through compilation environment variables. On a 64-bit OS, it is compiled as 64-bit by default. To compile unixODBC as a 32-bit program on a 64-bit OS, set the following.export CFLAGS=<32/64bit compile option> export LDFLAGS=<32/64bit compile option> export CC=<C compiler> export CXX=<C++ compiler>The environment variables to set for each platform are as follows.
Item AIX HP-UX SUN CC /usr/vac/bin/xlc /opt/aCC/bin/aCC /opt/SUNWspro/bin/cc CXX /usr/vacpp/bin/xlC_r /opt/aCC/bin/aCC /opt/SUNWspro/bin/CC CFLAGS
LDFLAGS-q64 (64bit) +DD64 (64bit) -xarch=v9 (64bit) LD /opt/SUNWspro/bin/CC 64-bit compilation Set OBJECT_MODE=64 in the environment variables. Add "+DD64 -DBUILD_REAL_64_BIT_MODE" to CFLAGS. Set "/usr/lib/64" and "/usr/ucblib/sparcv9" in LD_LIBRARY_PATH_64. Compiler paths may vary depending on where the user installed them, so the user must check the installed paths on the target machine.
The CFLAGS and LDFLAGS options must also be specified according to the 32/64-bit compile option. (Otherwise, an ELF class error may occur.) -
Example of compilation environment variables for compiling unixODBC as 32-bit on a 64-bit Linux OS
export CFLAGS="-m32 -DBUILD_LEGACY_64_BIT_MODE=1" export LDFLAGS=-m32 export CXXFLAGS=-m32After declaring the variables as above and going through the subsequent compilation steps, unixODBC is compiled with a SQLLEN size of 4 bytes (32 bits) and 32-bit executables.
-
-
Configuration for make
Run configure. In this step, the installation directory and several options required for the make process are set.
Shell> ./configure --prefix=/home/unixODBC --disable-gui --enable-threads=yes checking for a BSD-compatible install..../install-sh -c checking whether build environment is sane... yes checking for gawk... gawk checking whether make sets $(MAKE)... yes …….The configuration step checks the overall environment required for compilation, so if an error occurs, the user must install any required binaries manually.
unixODBC is installed in the path specified by the prefix argument. For example, if prefix=/home/unixODBC, unixODBC is finally installed in /home/unixODBC after the install step.
Info
prefix: Path where unixODBC is installed
enable-gui: Whether to build the GUI ODBC Administrator
enable-threads: The default is "yes" if thread-support is found on your machine. All modern Linuxes will have pthreads support in glibc so it is probably best to leave this to default.
If the enable-threads option is set to no, the following error may occur when testing the connection with isql, so set enable-threads=yes.
$ ./isql Altiodbc
./isql: symbol lookup error: /home/altibase_home/lib/libaltibase_odbc-64bit-ul64.so: undefined symbol: pthread_sigmask -
Compiling
-
Running make
Shell> make source='lstDelete.c' object='lstDelete.lo' libtool=yes \DEPDIR=.deps depmode=none /bin/bash ../depcomp \ -mt -c -o lstDelete.lo lstDelete.c …… make[1]: Leaving directory `/home/unixODBC-2.3.2/samples' make[1]: Entering directory `/home/unixODBC-2.3.2' make[1]: Nothing to be done for `all-am'. make[1]: Leaving directory `/home/unixODBC-2.3.2'On AIX, compilation errors have been observed that are presumed to depend on the patch level of the operating system.
-
Running make install
When make is finished, run make install to install the compiled binaries in the installation directory. Because they are installed in the prefix directory specified in the configuration step, you must have access permission to that directory beforehand.Shell> make install ….. mkdir -p /home/unixODBC/etc/ODBCDataSources /home/unixODBC/bin/odbc_config --header > /home/unixODBC/include/unixodbc_conf.h make[2]: Leaving directory `/home/unixODBC-2.3.2' make[1]: Leaving directory `/home/unixODBC-2.3.2' -
Verifying the installation
After installation, check that the library is loaded correctly.Shell> cd /home/unixODBC/bin Shell> ./dltest $ALTIBASE_HOME/lib/libaltibase_odbc-64bit-ul32.so SUCCESS: Loaded /home/altibase_home/lib/libaltibase_odbc-64bit-ul32.soThe dltest executable is located under bin in the unixODBC installation directory. If unixODBC Manager has been installed correctly as above, dltest on the Unix ODBC driver provided by Altibase must succeed.
-
Integrating unixODBC Manager#
This chapter describes how to integrate unixODBC Manager with the Altibase ODBC driver.
Setting ODBC.INI#
Set the following in the environment variables of the user account.
export ODBCINI=/home/unixODBC/etc/odbc.ini
This setting specifies the file to be used as the configuration file that holds the connection information for the ODBC driver.
Write the following in /home/unixODBC/etc/odbc.ini and save it.
- Example of odbc.ini
[ODBC Data Sources]
Altiodbc = Altibase ODBC Driver
[Altiodbc]
Driver = /altibase_home/lib/libaltibase_odbc-64bit-ul32.so
Description = Sample Altibase DSN
UserName = SYS
Password = MANAGER
ServerType = Altibase
Server = 127.0.0.1
User = SYS
Port = 20300
Database = mydb
FetchBufferSize = 64
ReadOnly = no
LongDataCompat=ON
TraceFile = /tmp/odbc.trace
Trace = 1
- Main settings
| Setting Item | Setting Value | Description |
|---|---|---|
| Driver | libaltibase_odbc-64bit-ul64.so |
Drivers are provided per bit, and what matters is whether ul64 or ul32 is used. Some ODBC Managers define SQLLEN as 4 bytes or 8 bytes at compile time, which can cause errors starting from the connection stage. Therefore, check how SQLLEN was defined when the ODBC Manager was compiled. (This is not a 32/64-bit distinction.) |
| libaltibase_odbc-64bit-ul32.so | As above, check how SQLLEN was defined when the ODBC Manager was compiled. | |
| libaltibase_odbc.so |
libaltibase_odbc.so is the ODBC driver used with a 32-bit unixODBC driver manager.
It is included in the Altibase 32-bit client package. Strictly speaking, the user must check the bit of the file before using it. (Check with file <FileName>.) If unixODBC is compiled and used as 32-bit, this driver must be specified. |
|
| LongDataCompat | ON | Set to ON when using LOB data. |
Check the SQLLEN length information described above as follows.
shell> /home/unixODBC/bin] ./odbcinst -j
unixODBC 2.3.2
DRIVERS............: /home/unixODBC/etc/odbcinst.ini
....................
SQLULEN Size.......: 4 ( 8 if 64bit )
SQLLEN Size........: 4 ( 8 if 64bit )
SQLSETPOSIROW Size.: 2
The ODBC driver to use depends on the SQLLEN size defined in unixODBC.
If SQLLEN = (4byte/32bit), use libaltibase_odbc-64bit-ul32.so as the ODBC driver.
If SQLLEN = (8byte/64bit), use libaltibase_odbc-64bit-ul64.so.
Testing the ODBC Connection#
When the settings above are complete, run the following in the directory where unixODBC Manager is installed.
Shell> cd /home/unixODBC/bin
Shell> ./isql Altiodbc
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>
If the connection prompt appears as above, the connection to the DB has been established successfully.
On AIX, the connection occasionally fails even after a correct installation, for example because the libodbcinst.so file is not found in LIBRARY_PATH or for other unusual reasons.
Shell> ls unixODBC-2.../odbcinst/.libs/libodbcinst.so.1
Check whether the libodbcinst.so.1 file exists in the compiled source directory. If the corresponding libodbcinst.so does not exist, the problem can be resolved by creating a symbolic link named libodbcinst.so to libodbcinst.so.1 and placing it in LD_LIBRARY_PATH.
On SUN, the lib path of unixODBC Manager must be set in LD_LIBRARY_PATH_64 to connect.
Setting ODBC Trace Logs#
Logging the ODBC functions called by unixODBC to a file is useful when analyzing the cause of a problem. To configure unixODBC to write trace logs, do the following.
-
Setting the ODBCINST.INI file
[ODBC] TraceFile = /home/unixODBC/bin/trace.log Trace = YesAdd the settings above to odbcinst.ini in the same directory as the odbc.ini file. The DSN name must be [ODBC]. TraceFile specifies the location and name of the file to which the log is written. Trace = Yes/No turns logging on or off.
-
Example of unixODBC trace output
[ODBC][19094][1473224991.140071][__handles.c][460] Exit:[SQL_SUCCESS] Environment = 0x1b3a760 [ODBC][19094][1473224991.140170][SQLAllocHandle.c][375] Entry: Handle Type = 2 Input Handle = 0x1b3a760 [ODBC][19094][1473224991.140216][SQLAllocHandle.c][493] Exit:[SQL_SUCCESS] Output Handle = 0x1b3b090 ..................................Opening trace.log shows content like the example above.