Instead of connecting directly to various databases, one can use the ODBC functions of programming languages such as PHP or Perl to connect to a database in a database-independant manner. This allows one to write code that can be run on top of any database that has an ODBC driver. In the case of UnixODBC, the databases that are supported by it include MySQL, Informix and Oracle.
Before installing UnixODBC, you might want to install any and all of the databases that you wish to use UnixODBC with -- it makes the install easier, if you have the databases already installed. However, contrary to common sense, you should not attempt to install or build the database connectors until you've installed UnixODB so after you've installed the databases and before you install their ODBC connectors, install UnixODBC.
This document specifically describes how to install the MySQL and Informix connectors, both of which may be used through UnixODBC, in subsequent sections.
Also, you should check to see if UnixODBC is available through the package manager of your operating system. If it is, you should install it that way because it is much easier than building it from scratch. Any version of UnixODBC should work with the database connectors described herein. Under CentOS/RedHat, you can use yum to check if UnixODBC is already installed:
su
yum list installed | grep ODBC
You can check if it is available and install it if it is:
su
yum search unixODBC
yum install unixODBC.i686
Under Ubuntu, you can use apt to check if UnixODBC is already installed:
su
apt-show-versions | grep ODBC
You can check if it is available and install it if it is:
su
apt-cache search unixodbc
apt-get install unixodbc
If you do decide that you want to build UnixODBC from source, either because it isn't available on your system or you want the latest bug fixes, etc., that haven't made it to the official OS release yet, begin by downloading the latest UnixODBC tar file from http://www.unixodbc.org/. However, beware that later versions of UnixODBC include some crazy stuff (for a database driver, that is) that you may want to disable (e.g. there is a KDE GUI that requires a graphics toolkit to build). The 2.2.11 version appears to work quite well and it does not seem to be as left-leaning as the later versions. If it works with your database connector, you may want to use it. Also, the 2.3.1 version seems to build and work well, so it is another good choice.
Untar the chosen tar file in the top level source directory (e.g. /rpm/UnixODBC):
tar -xvzf unixODBC-a.b.yy.tar.gz
It will create a new directory for that version of UnixODBC. Switch to that directory and build UnixODBC:
cd unixODBC-a.b.yy
./configure --prefix=/usr/local/unixODBC --enable-ltdl-convenience
make
Note that the "--enable-ltdl-convenience" flag is not necessary for versions of UnixODBC >= 2.2.14 (the problem with ltdl has been fixed in that version's configure file). If you have one of those versions, you'd use:
./configure --prefix=/usr/local/unixODBC
When you get the error about Qt headers, if you don't want to download and install yet another graphics toolkit to build UnixODBC (one has to ask oneself w.t.f. a database driver needs a GUI for, anyway), the following build should work for version 2.2.12 and later:
cd unixODBC-a.b.yy
./configure --prefix=/usr/local/unixODBC --disable-gui
make
Switch to super-duper user and install UnixODBC:
su
make install
The install may not inform the loader about where it put the dynamic link libraries that are necessary to access databases through UnixODBC. You may have to fix this situation manually. One fix is to add symlinks for the UnixODBC modules, in an obvious location such as /usr/lib. Another fix is to change ld.so.conf or ld.so.conf.d to point the loader at the directory where UnixODBC can be found.
If you prefer the first approach, this command can take care of things:
find /usr/local/unixODBC/lib -name \*.so -exec ln -s \{\} /usr/lib \;
We prefer to add a file to /etc/ld.so.conf.d, which points the loader at the UnixODBC modules, like this:
/etc/ld.so.conf.d/UnixODBC.conf:
/usr/local/UnixODBC/lib
Run the loader configuration command to inform the loader about the new dynamic link library:
su
/sbin/ldconfig
If there are any databases that will be used by ODBC, they need to be configured by adding a section to the ODBC initialization file. If UnixODBC was installed in /usr/local/unixODBC (as we show above), the file is /usr/local/unixODBC/etc/odbc.ini. For installations done by a package manager (e.g. RedHat or CentOS), the file may be in /etc/odbc.ini.
Here is a sample connection for a MySQL student tracking database:
[StudentTracking]
Description = Student tracking database
Trace = Off
TraceFile = stderr
Driver = MySQL
SERVER = localhost
PORT =
SOCKET =
USER = mytrack
PASSWORD = SecretSquirrel
DATABASE = StudentTracking
Note that, for some reason, it would appear that the user name and password must be set in the DSN definition. This being the case, the USER name and PASSWORD should match the name set when the database was created and permissions assigned. Of course, the database name must also match the name given to the database. The permissions on odbc.ini can be:
-rw-r--r-- root root
However, since the password to the database is in this file so it may be better to use something like this:
-rw-r----- root mysql
But, note that if you do this, you will need to include all of the users of ODBC in the mysql group. Typically, this will include apache, since httpd now runs under the apache userid and PHP programs are typical users of ODBC to access databases for web pages.
Also note that you may need to supply the socket path if you are using a non-standard socket in the database's initialization file. Furthermore, some versions of MySQL, for example, don't work all that well with TCP so, once again, you may need to supply a socket path to force the use of the socket instead of TCP.
To open this sample database in PHP you would do something like this:
odbc_connect("StudentTracking", "mytrack", "SecretSquirrel");
Here is another sample showing an Informix connection to your collections database (but see Installing the Informix ODBC Connector, in these notes, for more information, since getting Informix to work is very tricky):
[CollectOh]
Description=Collections Database
Driver=Informix
Server=localhost
Port=1498
Database=/my/database/path/collectdb
CLIENT_LOCALE=en_us.8859-1
DB_LOCALE=en_us.8859-1
TRANSLATIONDLL=/usr/share/informix/lib/esql/igo4a304.so
In this case, the user can supply the username and password to the ODBC connect function when they connect to the database so it has been left out of the DSN. To open this sample database in PHP you would do something like this:
odbc_connect("CollectOh", "theman", "payme");
Finally, it may be possible to open a DSN-less connection something like this (once again for MySQL in PHP):
odbc_connect(
"Driver={MySQL};" .
"CommLinks=tcpip(Host=localhost);" .
"DatabaseName=StudentTracking;" .
"uid=mytrack;pwd=SecretSquirrel",
"mytrack", "SecretSquirrel");