Register drivers & DSNs with unixODBC

This section explains how to configure drivers & data sources for unixODBC.

Ensure unixODBC Driver Manager is installed properly

benoy@palazhi:~$ odbcinst -j
unixODBC 2.2.14
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2

To query for drivers:
benoy@palazhi:~$ odbcinst -q -d
odbcinst: SQLGetPrivateProfileString failed with .

To query for datasources:
benoy@palazhi:~$ odbcinst -q -s
odbcinst: SQLGetPrivateProfileString failed with .

To get rid of these errors, ensure the following:
benoy@palazhi:~$ env | grep 'ODBC'
LD_LIBRARY_PATH=/usr/local/unixODBC/lib:/usr/local/lib:
ODBCSYSINI=/usr/local/etc
ODBCINI=/usr/local/etc/odbc.ini

[NOTE]
If not yet defined, define and export the above env variables
[/NOTE]

Sample odbcinst.ini (for drivers)
benoy@palazhi:~$ cat /usr/local/etc/odbcinst.ini
[MySQL]
Description = MySQL driver for Linux
Driver = /usr/local/lib/libmyodbc5.so
Setup = /usr/local/lib/ libmyodbc3S-5.1.6.so
FileUsage = 1

Once this is done, drivers can be listed
benoy@palazhi:~$ odbcinst -q -d
[MySQL]

Sample odbc.ini (for data sources)
benoy@palazhi:~$ cat $ODBCINI
#
# odbc.ini configuration for MyODBC and MyODBC 3.51 Drivers
#

[ODBC Data Sources]
dsn1mysql = MyODBC 5.1.6 DSN

[dsn1mysql]
Driver = /usr/local/lib/libmyodbc5.so
Description = Connector/ODBC 5.1.6 Driver DSN
SERVER = localhost
PORT = 3306
USER = root
Password = [enter the password]
Database = mysql
OPTION = 3
SOCKET = /var/run/mysqld/mysqld.sock

[Default]
Driver = /usr/local/lib/libmyodbc5.so
Description = Connector/ODBC 5.1.6 Driver DSN
SERVER = localhost
PORT = 3306
USER = root
Password = [enter the password]
Database = mysql
OPTION = 3
SOCKET = /var/run/mysqld/mysqld.sock

[NOTE]
Values for PORT, SOCKET can be found in /etc/mysql/my.cnf if mysql is installed properly.

benoy@palazhi:~$ cat /etc/mysql/my.cnf | grep 'port\|socket'
# One can use all long options that the program supports.
# It has been reported that passwords should be enclosed with ticks/quotes
# Remember to edit /etc/mysql/debian.cnf when changing the socket location.
port = 3306
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
socket = /var/run/mysqld/mysqld.sock
port = 3306
# Using BerkeleyDB is now discouraged as its support will cease in 5.1.12.

Database is the name of the database created in the db; if mysql has been installed properly, you can use the database "mysql" to test the connection.
[/NOTE]

Once this is done, data sources can be listed.
benoy@palazhi:~$ odbcinst -q -s
[dsn1mysql]
[Default]

If the drivers, data sources & the db have been installed properly, it will be possible to connect to the database using isql.

benoy@palazhi:~$ isql dsn1mysql
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL> quit;
benoy@palazhi:~$

[NOTE]
dsn1mysql is specified in odbc.ini.
User will connect to the database "mysql" in localhost @ 3306 as the root (as can be seen from odbc.ini)
[/NOTE]