A few weeks back I covered connecting ODBC to MS-SQL, but today we are going to do the same thing with MySQL. The process is very similar. One might ask, why use ODBC driver in the first place? The reason is simple, there are many flavors of SQL and the ODBC driver allows one standard to connect them. For that reason many software developers use ODBC for connecting their applications to SQL.
For my purposes, the Asterisk PBX platform can use ODBC to call ‘dial plan’ functions from the database, but that is another article! This post assumes you have a working MySQL server and at least one table with data to pull from.
Goal of this Post:
– Install ODBC driver for MySQL
– configure odbc.ini
– configure odbcinst.ini
– verify connectivity with isql
To start we need to install the proper packages. Use yum or the ‘Add/Remove Packages’ tool to install unixODBC and mysql-connector-odbc packages. Once this is ready open the ‘odbcinst.ini’. Comment out the Postgre SQL stuff with ‘#’ and uncomment the MySQL example.
IMPORTANT: One thing to note, ‘libmyodbc.so’ does not ship on Fedora 10. If you do a search, you will find a driver called ‘/usr/lib/libmyodbc3.so’ If you use Fedora 10 make sure to replace ‘Driver = /usr/lib/libmyodbc.so’ with ‘Driver = /usr/lib/libmyodbc3.so’. Otherwise isql will complain, ‘it cannot find the driver’ when you attempt to connect. Remember, there are many versions of SQL and many different ODBC drivers to connect them.
For Fedora 10 systems your ‘odbcinst.ini’ should look like this:
The ‘odbcinst.ini file lets the user specify as many different drivers as he wants. Say, you had a Microsoft MS-SQL server that stored voicemail for Asterisk. That same Asterisk server could query for it’s dial plan from an internal MySQL database with a different ODBC driver.
Below is a example of an Asterisk phone system that stores voicemail on MS-SQL and queries MySQL for dial plan.
Example of /etc/odbcinst.ini with MS-SQL ODBC connection added:
Next, we need to configure the ‘/etc/odbc.ini’. This file contains parameters for ODBC connections – such as, user name, password, database and host. Each application that requires a odbc connection can have it’s own entry or share one.
Below I have defined a database for Asterisk to connect on the localhost.
Example of /etc/odbc.ini:
Our final task is to utilize the ‘isql’ command to connect to our MySQL database. isql will call on the specified profile stated in the ‘/etc/odbc.ini’ file. In the case below we are calling on ‘[asterisk -connector]’.
If you see this message:
[MYODBCUtilReadDataSource.c][ERROR] Unknown attribute (Servername)
[MYODBCUtilReadDataSource.c][ERROR] Unknown attribute (Username)
These errors will pop up when you use the wrong directives in odbc.ini. For instance a google search will show examples like, ‘UserName = <mysql user>, when the mysql ODBC driver is expecting, ‘User = <mysql user>. Likewise, ‘ServerName’ should be “Server”. It may be that other versions of this driver expect different names… not sure, but something to think about if you see a similar error.
I hope this quick tutorial helps someone. I’m posting my configuration files as well: