Linux unixodbc driver manager
UnixODBC is an idea whose time has come. It holds many promises for those of us who use databases in our daily work and would like to do more of that work on Linux or one of the UNIX variants. Those coming from a windows background will rapidly discover that it can be much more involved setting up a working Unix based database than they are used to. They will also discover a world of great advantages too; stability, scalability and freely available source code are values not easily dismissed.
This document is designed to help people set up and use unixODBC. My database of choice is MySQL, which I have used with great success for several years, and it is the database I will discuss setting up
ODBC is an interface by which programs and programmers can communicate with any database which has an ODBC driver. While most databases have one or more APIs in various programming languages, ODBC allows the same programming code to talk with numerous types of databases. (ODBC is not the only interface to do this, but it is very widely used and supported)
Getting Started
UnixODBC is available in source code only. This means that you download a tar file from http://www.unixODBC.org , extract it, compile it, and install it. Before you do so though you need some other things.
Prerequisets:
The Qt toolkit version 2.x sources from http://www.troll.no . Make sure it is version 2 or higher !
Download the Qt v2 sources.
Extract the sources to somewhere, usually this is done by placing the tar file in a directory like /usr/local or /opt and using the command.
tar zxvf qt-2.0.1.tar.gz
to extract the files. This will create a subdirectory qt-2.0.1 . Remember that only root can normally write to /usr/local or /opt. This is an exaple, use the name of the file you have.
Compile the sources. Assuming you have a relatively recent version of Linux with all the appropriate stuff (libg++, gcc or egcs etc) You issue 3 commands from the qt-2.0.1 directory.
./configure
make
make install
This last command is scary since it will install the new libqt into places where any old v1.4x libqt may exist. Rest assured that the full name of the library is different and it will not overwrite your existing libqt. It should choke on rewriting the symlink to libqt.so and thus not mess up your existing Qt / KDE apps. If it does rewrite this link, you will need to set it back to its original link.
For more information, see http://www.troll.no . Please don’t bother the unixODBC people for instructions on setting up Qt.
A Database — in this case MySQL from http://www.mysql.com
Again, I strongly suggest you get the sources and compile them yourself. The process is similar if not identical to that for Qt described above. By default MySQL installs it’s executable files in /usr/local/bin , it’s include files (source headers) in /usr/local/include/mysql and it’s libraries in /usr/local/lib/mysql . The actual database files are in /usr/local/var .
Before you can use MySQL, you need to run
/usr/local/bin/mysql_install_db
as root to set up the system tables. To actually start the database, run
/usr/local/bin/safe_mysqld &
MySQL comes with very extensive documentation. Refer to it for questions on compiling, installing and using MySQL. This is expecially true for permissions. If the permissions aren’t correct, no interface will work.
Installing unixODBC
As mentioned before, get the source tar file from http://www.unixodbc.org . As root, move the tar file to /op or /usr/local or where ever you want the source to reside. Untar the file and run the following commands from the command line in the unixODBC source directories:
./configure
./make
./make install
Assuming that you have all the libraries and tools that it needs, you should be breezing through this compile. Certainly after all that practice with Qt and MySQL this should be old hat. UnixODBC takes quite a while to compile, actually all of these packages do. Relax and enjoy it.
Installing a Driver
UnixODBC by itself isn’t a whole lot of good without a driver. MySQL has an ODBC driver named MyODBC but it isn’t included with the current edition of unixODBC, so you have to get it and compile it yourself. I’ll be referring to version 2.50.24, the latest as of this writing. The installation is much the same as the other packages, but you need to give configure some options. It will prompt you for the path to the MySQL sources if you don’t specify the path, but you also have to supply the —with-unixODBC= flag. The following is from the MyODBC INSTALL file:
To make configure look for unixODBC instead of iODBC, use
Where DIR is where unixODBC is installed.
And (as usual), if the unixODBC headers and libraries aren’t located
in DIR/include and DIR/lib, use
You might want to specify a prefix other than /usr/local for installation,
I, for example keep my ODBC drivers in /usr/local/odbc/lib, so I add
So here is my configure line:
./configure —with-unixODBC=/usr/local —with-mysql-sources=/usr/local/mysql
Running make and then make install puts the resulting library: libmyodbc-2.50.23.so into /usr/local/lib. WAIT A MINUTE ! Didn’t I say it was version 2.50.24 ? Yes, I did. However, unless you want to go in and change the version info in the source code, it will create the library with that name. I don’t consider this a big deal and so I didn’t mess with it.
If you omit the —with-mysql-sources flag, configure will fail. If you omit the —with-unixODBC flag, configure will complete and MyODBC will compile. However, it will not work correctly when using it with unixODBC. The problems described below occurred when I omitted this flag:
1) If the DSN (Data Source Name) you create is also the name of a database, the driver points to that database no matter what you specify the database to be.
2) If the DSN is not the name of an existing database, it will fail, not allowing you to login to the database. The trace log file will tell you that it couldn’t find a database with the name of the DSN. This is confusing if you specified a valid database name in the .odbc.ini file .
When using the —with-unixODBC flag, These problems dissappear and it works the way it should.
A note: MyODBC does not support ODBC version 3 as of version 2.50.24 . When writing programs that utilize this driver, I have had success with specifying V_OD_ODBC2 when calling SQLSetEnvAttr(. ) .
Setting up unixODBC
At long last we come to actually setting up unixODBC and using it. While most of this information exists on the unixODBC web pages and user’s guide, I found it difficult to find and follow, so I repeat it here in hopes that it will be made clearer.
UnixODBC consists of a lot of libraries, installed in /usr/local/lib , and a few executable files (binaries) installed into /usr/local/bin . These executable files are ODBCConfig , DataManager , and odbcinst .
In order to get unixODBC running, do these things in this order:
In an xterm, type ODBCConfig . This is a GUI program and must be run in an X session. At the very least you need to set up a driver to use. The drivers will be specific to one database application, like MyODBC is specific to MySQL. In addition to this, you need to specify a setup file to use for this drive. The setup files are the /usr/local/lib/libodbc*S.so libraries where * signifies the database application, so /usr/local/lib/libodbcmyS.so is the «setup file» for MyODBC. The driver (not the setup file) is /usr/local/lib/libmyodbc-2.50.23.so.
To set up the driver, run ODBCConfig as root, go to the drivers tab and click on «New». The following are the settings I use.
Name: myodbc
Description: MySQL driver.
Driver: /usr/local/lib/libmyodbc-2.50.23.so
Setup: /usr/local/lib/libodbcmyS.so
FileUsage: 1
You should have a driver set up before setting up a DSN. After having done so, you may want to set up a system DSN. You do this by selecting the «System DSN» tab, clicking on ‘New’, specifying the driver to use and filling in the required information. You will want to select the driver name you just defined as the Driver in the first screen that displays, and click OK.
Doing this as root will create and edit the /usr/local/etc/odbcinst.ini (for the driver info) and /usr/local/etc/odbc.ini (for the system DSN) files. Early versions of unixODBC would put these files in /etc, and you can still use a configure option : —sysconfdir=/etc to put those files in that location.
The process for setting up a user DSN is identical to setting up a system DSN. You simply select the «User DSN» tab in ODBCConfig and fill out the required fields. The following is how I filled out a User DSN.
Name: mysqltest
Description: myodbc
Driver: myodbc
Trace: Yes
TraceFile: mysql.log
Host: localhost
Port: 3306
Socket:
Database: test
This will create and edit a file named ~/.odbc.ini . Since the test database comes without any tables, you may want to specify mysql as the database instead, so you can see the tables when running DataManager.
Now you should be able to run DataManager and see your drivers, DSNs and tables for each DSN. See the unixODBC web site for screen shots of what it should look like.
UnixODBC is not so much an end user program, but rather an intermediary between a program and one or more databases. There is information in the unixODBC documentation for example, about setting up StarOffice to use an ODBC connection. I am in the process of writing a program that makes use of unixODBC and numerous others are as well. It may well be that in time unixODBC will be included in numerous Linux distributions and installed by default. Until that time I hope that this document helps you get this sofware up and running.