How to ODBC verbinding

How to setup a mySQL (DSN) datasbase source in CentOS with myODBC and unixODBC command line.
Tue, 05/22/2007 - 2:58pm — Jeff

Setup guide is using the same hardware and CentOS install we used in our prior how to on Zenoss. I use the Zenoss database on the localhost but this can easly be changed for setting up a ODBC connection on a remote host machines useing any database.

This entire install process is done as root, so login to your server and lets get started.
Install myODBC and unixODBC as a dependency.
yum install mysql-connector-odbc

Dependencies Resolved
Package Arch Version Repository Size
mysql-connector-odbc i386 3.51.12-2.2 base 142 k
Installing for dependencies:
unixODBC i386 2.2.11-7.1 base 832 k

Transaction Summary
Install 2 Package(s)
Update 0 Package(s)
Remove 0 Package(s)

Total download size: 973 k
Is this ok [y/N]: y

Print your config infomation.
odbcinst -j
unixODBC 2.2.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
USER DATA SOURCES..: /root/.odbc.ini

Edit /etc/odbc.inst and /etc/odbcinst.ini
Below are my examples.
vi /etc/odbcinst.ini
Description = ODBC for MySQL
Driver = /usr/lib/
Setup = /usr/lib/
UsageCount = 2

[MySQL ODBC 3.51 Driver]
Description = ODBC 3.51 for MySQL
DRIVER = /usr/lib/
SETUP = /usr/lib/
UsageCount = 2

vi /etc/odbc.inst
Driver = /usr/lib/
SERVER = localhost
PORT = 3306
DATABASE = events
USER = root

Note: The PASSWORD: is the one we setup during our Zenoss installation and is REQUIRED because Splunk uses it to authenticate to the database.

Once you create your files and save them here is how you do the install.
Install driver
odbcinst -i -d -f /etc/odbcinst.ini

Install system DSN
odbcinst -i -s -l -f /etc/odbc.ini

Test your DSN install by lisinting your installed data sources.
odbcinst -s -q

Test that you can connect to the database via the ODBC.
isql -v zenoss root BlAh
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
SQL> quit

You are done. Here are some quick and dirty ways to fix some problem I had during the install.
I did not have libtool installed properly.
Install libtool
yum install libtool

Before the include I added
/opt/splunk/lib <---- I later commented this out.

Load the new
ldconfig (you will not recive any confermation)

I also had a problem acidently loading user DSN so you can remove the user odbc.ini file or copy the one from /etc to the profile.
rm /home/name/.odbc.ini

How to remove a driver.
odbcinst -u -d -n zenoss
zenoss has been deleted (if it existed at all) because its usage count became zero.

How to remove a datasource.
odbcinst -u -s -l -n zenoss
odbcinst: DSN removed (if it existed at all). ODBC_BOTH_DSN was used as the search path.

Remeber if you change a odbc peramerter you need to install it again.
odbcinst -i -s -l -f /etc/odbc.ini

My next article will be how to setup Splunk to use the Zenoss myODBC connection as a datasource.

Was this article helpful?

mood_bad Dislike 3
mood Like 0
visibility Views: 15007
Related articles: