This site is now 100% read-only, and retired.

UnixODBC CLI Install and Configuration

Posted by mpapet on Wed 7 Feb 2007 at 12:55

For those of you that may not know what unixodbc does, "ODBC is an open specification for providing application developers with a predictable API with which to access Data Sources. Data Sources include SQL Servers and any Data Source with an ODBC Driver." They include a text file driver as an example of a non-SQL source. Two examples are Asterisk and OpenOffice.org.

Unixodbc allows cross-platform use of databases with many bridges available in many popular programming languages.

Installing and configuring isn't necessarily as simple as "apt-get install unixodbc". This HOWTO was written on Etch and may vary if you are using a different version.

Step 1: Download Packages

Running: apt-get install unixodbc libmyodbc odbc-postgresql \ odbcinst1debian1

will get you the ODBC binaries, database drivers for MySQL and PostgreSQL and a Debian helper application for ODBC respectively.

Step 2: Add odbcinst.ini Records

As the root user, check for two empty text files /etc/odbc.ini and /etc/odbcinst.ini. If they aren't there, then create them. (eg. "touch /etc/odbcinst.ini")

Create a directory for the odbcinst.ini scripts:

username@host:~$ mkdir  /home/username/odbc

Step 3: Adding ODBC Instances

In order to minimize entry-error, I use text files to load ODBC instances. Odbcinst's error messages are a bit cryptic so I control errors using a separate file for each database type.

As root create a file name pgsql and paste the following into it.

[PostgreSQL]
Description     = PostgreSQL driver for Linux & Win32
Driver          = /usr/lib/odbc/psqlodbca.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
FileUsage       = 1

If you are on another version double-check your library names.

Name the file pgsql so the following command should work.

odbcinst -i -d -f /home/username/odbc/pgsql

You can create another file for MySQL with the following contents:

[MySQL]
Description     = MySQL driver for Linux & Win32
Driver          = /usr/lib/odbc/libmyodbc3_r-3.51.11.so
Setup           = /usr/lib/odbc/libodbcmyS.so
FileUsage       = 1

To use it run, as root: "odbcinst -i -d -f /home/username/odbc/mysql".

With PostgreSQL and MySQL done, lets create a sample ODBC connection!

Step 4: Create ODBC Connection

Create another text file and copy the text below into it. I called it asterisk. Copy the contents of this file into odbc.ini. (Note, in theory "odbcinst -i -s -f /path/to/file" writes the contents of the named file into /etc/odbc.ini. I could not get it to do so and there were no error messages) So, copy and paste the following into odbc.ini too

[asterisk]
Description = MySQL Asterisk
Driver      = MySQL
SERVER      = localhost
USER        = username
PASSWORD    = password
PORT        = 3306
DATABASE    = asterisk
Option      = 3

Make sure your database permissions are configured to allow the login/connection before testing.

Step 5 Test Connection:

As the root enter run:

 isql asterisk

This should put you into the asterisk database!

The unixodbc site was very helpful to me. Check the manuals page for useful how-to's and additional driver configuration information.

 

 


Re: UnixODBC CLI Install and Configuration
Posted by dkg (216.254.xx.xx) on Sat 10 Feb 2007 at 18:53
[ View Weblogs ]
Thanks for the useful tips!

FWIW, in debian the odbc-postrgresql package contains template files for use with the odbc utilities:

[0 dkg@marmoset ~]$ dpkg -L odbc-postgresql | grep ini
/usr/share/doc/odbc-postgresql/examples/odbc.ini.template
/usr/share/psqlodbc/odbcinst.ini.template
[0 dkg@marmoset ~]$ cat /usr/share/psqlodbc/odbcinst.ini.template
[PostgreSQL ANSI]
Description     = PostgreSQL ODBC driver (ANSI version)
Driver          = /usr/lib/odbc/psqlodbca.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
Debug           = 0
CommLog         = 1

[PostgreSQL Unicode]
Description     = PostgreSQL ODBC driver (Unicode version)
Driver          = /usr/lib/odbc/psqlodbcw.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
Debug           = 0
CommLog         = 1
[0 dkg@marmoset ~]$ 
So you shouldn't need to create your own from scratch when doing the install.

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by nigel_horne (212.159.xx.xx) on Tue 27 Feb 2007 at 15:33
Fails with the cyptic error message "[ISQL]ERROR: Could not SQLConnect". It's cryptic because it doesn't tell you why it's failed to connect, where to look to find out why it's failed to connect, and what you can do about it.

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by suspended user mpapet (208.179.xx.xx) on Tue 27 Feb 2007 at 16:07
There's something wrong with your .ini file.

I agree that there's not enough error condition displayed on connection failures. So, how about offering a bounty to have it coded by the maintainer?

The package might have limitations, but hey, it's free and works very nicely once you sort out connection issues.

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (212.18.xx.xx) on Sun 20 May 2007 at 11:55
Firstly, you can use isql -v <DSN> which at least in my case squirted back the MySQL error.

Secondly, you need to make sure that mysql is listening where you think it is; I absent-mindedly told ODBC to look in /var/run/mysql/mysql.sock, which isn't where it was. If you're specifying a port number make sure that skip-networking is turned off.

Just a couple of thoughts.

-- cheesey

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (85.58.xx.xx) on Tue 20 May 2008 at 14:30
Similar problem here. Running isql -v prosodia-db tells:

[IM002][unixODBC][Driver Manager]Data source name not found, and no default driver specified.

My odbc.ini is as follows:

[prosodia-db]
Description = MySQL Prosodia
Driver = MySQL
SERVER = localhost
USER = username
PASSWORD = password
PORT = 3306
DATABASE = prosodiadb
Option = 3

I can log in mysql using the username, password and database in odbc.ini (but the database has no tables, as I'm just testing it).

According to the MySQL documentation, "The error (2002) Can't connect to ... normally means that there is no MySQL server running on the system or that you are using an incorrect Unix socket filename or TCP/IP port number when trying to connect to the server." using ps -e tells both mysqld and mysqld_safe are running. I don't know how to check the socket filename ODBC is looking for, but the port seems correct...

Any ideas? Thanks in advance!

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (85.58.xx.xx) on Tue 20 May 2008 at 14:41
I solved it! odbc.ini lacked a line:

ReadOnly = No

Now it connects!

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by puneet_p (207.117.xx.xx) on Mon 19 Apr 2010 at 16:52
Will you kindly help us where you have added ReadOnly : No, coz we are having odbc.ini file in 2 places i.e
1. /usr/local/etc/odbc.ini and
2. /etc/unixODBC/odbc.ini

We've added this line in both the files and still we are getting this error:
Error
IM002 (0) [unixODBC][Driver Manager]Data source name not found, and no default driver specified

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by puneet_p (207.117.xx.xx) on Mon 19 Apr 2010 at 17:12
Adding one more thing into it, We are successfully able to connect with the sqlserver with the tsql command, but when we start our app, we get this error

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (122.144.xx.xx) on Thu 1 Apr 2010 at 16:15
Using 'SERVER = 192.168.0.6'
i.e ip address instead of 'localhost' solved my problem.
You can try using that if 'isql -v dsnname' shows socket errors.
-Sultan

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by ronin42 (219.110.xx.xx) on Sat 10 Mar 2007 at 01:25
[ View Weblogs ]
ej
The line to remind one to make sure their database permissions are correct to allow access is good reminder but for those of us who are not sure where or how to do that a link or explanation would be helpfull.
Not being snippy just a bit frustrated as I can login as root but not as user.
I have added myself to the mysql group and created the account within mysql through root but can't get in on the command line.
I have tried using the gui tool but it fails and says I am missing libmyodbc3_r-3.51.11.so. I google that lib and it brought me to this page.

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (208.179.xx.xx) on Mon 12 Mar 2007 at 15:20
The way forward in mysql is something like this on the command line:

mysql -u root

That should put you into mysql where you need to do some other things, like assign a password to root, create a non-root mysql account (with password) that interacts with the application database.

use google and the following search phrases, "mysql add user" "mysql root password" mysql docs should be on top of the list.

What you need strays gets into basic mysql administration which is outside the scope of setting up ODBC.

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (2001:0xx:0xx:0xxx:0xxx:0xxx:xx) on Wed 10 Nov 2010 at 21:10
Although this is old, the reason "odbcinst -i -s -f /path/to/file" does not work is because you do not specify if it's a system or user dsn.

To install as a System DSN:
odbcinst -i -s -l -f /path/to/file
To install as a User DSN:
odbcinst -i -s -h -f /path/to/file

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by jessport (46.117.xx.xx) on Sun 20 Nov 2011 at 05:39
Hello,

This is a very useful article, I do have one comment.
When working with Oracle, one must export the TWO_TASK variable to point to the desired TNS.

Jess Portnoy
Here is a longer explanation:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ ID:89412348059

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (187.143.xx.xx) on Thu 12 Jul 2012 at 22:08
Hello this a great manual
I can´t writte very well,
but i need to share this information
translate please
"pude resolver el problema que marca al no encontrar la libreria libmyodbc3_r-3.51.11.so esto lo resolvi haciendo una copia del archivo libmyodbc.so y renombrandolo a libmyodbc3_r-3.51.11.so,
despues de eso cree una base de datos en MySQL llamada asterisk
y por fin quedo creado mi ODBC,
espero que esto les ayude con su problema"

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (213.143.xx.xx) on Wed 17 Dec 2014 at 19:11
Greaaaaattttt !!!

I resolved my problem as Anonymous (187.143.xx.xx) did it !!!
Muchas gracias por solución al problema, realmente funcionó. Me ayudó sí...felicitaciones y un saludo

[ Parent ]

Re: UnixODBC CLI Install and Configuration
Posted by Anonymous (208.110.xx.xx) on Thu 11 Feb 2016 at 22:44
It's worth noting that sometime between the time that this guide was written, and the present day (2016), the format for odbc.ini has changed. It should now look like this:

[asterisk-connector]
Description=MySQL connection to 'asterisk' database
Driver=MySQL
SERVER = 127.0.0.1
USER = asterisk
PASSWORD = welcome
PORT = 3306
DATABASE = asterisk

Note the use of all caps and the shortened version of "username". After changing from the format suggested here to this present format, it actually works on my Debian Jessie server.

[ Parent ]