Monday, May 5, 2008

Installing, configuring and using ODBC on ubuntu

I wanted to use an ODBC-MySQL bridge on my linux box. Here are the steps I followed.
  • First of all, unixodbc comes installed on ubuntu. Otherwise you can install the package called unixodbc.
  • I downloaded and installed the ODBC connector from the MySQL website. I downloaded the rpm package, converted it to a deb with alien and installed it with dpkg.
  • I also found and installed these packages in the ubuntu repositories: mysql-connector-odbc and libmyodbc. There is probably some redundancy in this step and the previous one, but at least I didn't have any conflict issues and everything worked in the end.
  • Now, unixodbc relies on two files: /etc/odbcinst.ini and ~/.odbc.ini. The first one holds a registry with ODBC drivers for different database systems. The installer for the MySQL driver (the one from the MySQL website) registers the driver with this file, so you don't need to do that manually. The file should look like this:
    [MySQL]
    DRIVER          = /usr/lib/libmyodbc3.so
    SETUP           = /usr/lib/libmyodbc3S.so
    UsageCount              = 1
    
    Make sure the location of the drivers is the correct one.
  • The .odbc.ini is used for declaring datasources. Actually this file declares datasources on a per-user basis. Global datasources can be configured in the file /etc/odbc.ini. An entry on these files should look like this:
    [test]
    DRIVER = MySQL
    SERVER = localhost
    DATABASE = mysql
    USER        =root 
    PASSWORD    =
    PORT        = 3306
    SOCKET = /var/run/mysqld/mysqld.sock
    
    Needless to say that the DRIVER name should must the name of the driver, as it was declared on /etc/odbcinst.ini.
  • As you can see I also had to explicitly define the mysql socket. This was because the location of the mysql socket in ubuntu is different than in other linux systems. By default, unixodbc uses the location /tmp/mysql.sock, so you can either explicitly specify the socket location as above, or just create a link:
    sudo ln -s /var/run/mysqld/mysqld.sock /tmp/mysql.sock
  • Now you can test the connection using
    isql -v test
    You should get a message like this:
    +---------------------------------------+
    | Connected!                            |
    |                                       |
    | sql-statement                         |
    | help [tablename]                      |
    | quit                                  |
    |                                       |
    +---------------------------------------+
    followed by an SQL prompt.
  • Finally this is some java code that uses this datasource:
    // There are two ways we can address a datasource: 
    // either with the DNS of a correctly configured ODBC datasource, 
    // or by providing all the database information.
         Connection conn = java.sql.DriverManager.getConnection(
                 //"jdbc:odbc:;DRIVER={MySQL};SERVER=localhost;DATABASE=mysql;",
                 "jdbc:odbc:test",
                 "root",
                 "");
       

No comments: