Jul
18
2009




How to use the ODBC driver to Connect to MySQL database in Fedora 10

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.

odbc.ini:

# Example driver definitinions
#
#
#
# Included in the unixODBC package
#[PostgreSQL]
#Description    = ODBC for PostgreSQL
#Driver        = /usr/lib/libodbcpsql.so
#Setup        = /usr/lib/libodbcpsqlS.so
#FileUsage    = 1
#
#
# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description    = ODBC for MySQL
Driver        = /usr/lib/libmyodbc.so
Setup        = /usr/lib/libodbcmyS.so
FileUsage    = 1

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:

# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description    = ODBC for MySQL
Driver        = /usr/lib/libmyodbc3.so # <—— note the “3” missing from default example file
Setup        = /usr/lib/libodbcmyS.so
FileUsage    = 1

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:

# Driver from the MyODBC package
# Setup from the unixODBC package
[MySQL]
Description    = ODBC for MySQL
Driver        = /usr/lib/libmyodbc3.so
Setup        = /usr/lib/libodbcmyS.so
FileUsage    = 1
#
#
[ms-sql]
Description = TDS connection
Driver = /usr/lib/libtdsodbc.so
Setup = /usr/lib/libtdsS.so
UsageCount = 1
FileUsage = 1

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:

[asterisk-connector]
driver = MySQL
Database = asterisk
Server = localhost
Socket = /var/lib/mysql/mysql.sock
User = odbctest
Password = koala1

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]’.

[root@localhost ~]# isql -v asterisk-connector
+—————————————+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+—————————————+
SQL> show databases;
+—————————————————————–+
| Database                                                        |
+—————————————————————–+
| information_schema                                              |
| asterisk                                                        |
| test                                                            |
+—————————————————————–+
SQLRowCount returns 3
3 rows fetched
SQL>

If you see this message:

[MYODBCUtilReadDataSource.c][233][ERROR] Unknown attribute (Servername)

or

[MYODBCUtilReadDataSource.c][233][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:

/etc/odbcinst.ini
/etc/odbc.ini

Link to previous article on ODBC and Microsoft SQL

If you like this article tell Google!!









Written by mattb in: Asterisk,Asterisk Post,Linux,MySQL,Scripts,Xen | Tags: , , , , , , , , , , , , , , , , , , , ,

1 Comment »

RSS feed for comments on this post. TrackBack URL

Leave a comment