[R] rodbc or unixodbc error

[Dirk Eddelbuettel]

Sebastian,

On 21 February 2005 at 19:18, Sebastian Luque wrote:
| I'm trying to establish a connection to a MySQL database, and am using the
| rodbc package for it. This is in a GNU/Debian Linux box, with the
| corresponding Debian unstable packages. I can login to my MySQL databases
| from any shell and directory, so the problem is probably not there. Here's
| an example of what I'm doing:
| 
| R> odbcConnect("test", uid="myusername", pwd="mypassword")
| [1] -1
| Warning messages: 
| 1: [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified 
| 2: ODBC connection failed in: odbcDriverConnect(st, case = case, believeNRows = believeNRows)
| 
| 
| The error is apparently from unixodbc, and googling for it I found that
| somebody solved it by specifying a default driver in a odbc.ini file. Can
| somebody please tell whether this is the right approach, and if so, how to
| write that specification? I saw that one might do this in a ~/.odbc.ini
| (i.e. the user's config file) file.

Yes, this is under-documented and thus harder than it should be. I owe my
first working setup many, many years ago to a helpful (private) mail from
Fritz.

So here goes, I just tested it again with PostgresSQL (as I don't currently
keep MySQL running) yet it should carry over. If it fails, let's work on this
off-list.

i)  /etc/odbcinst.ini -- I think these entries may even have been created by
    a Debian package but I am not entirely sure.

-----------------------------------------------------------------------------
edd at basebud:~> cat /etc/odbcinst.ini
[PostgreSQL]
Description             = PostgreSQL ODBC driver for Linux and Windows
Driver          = /usr/lib/postgresql/lib/psqlodbc.so
Setup           = /usr/lib/odbc/libodbcpsqlS.so
Debug           = 0
CommLog         = 1

[MySQL]
Description             = MySQL driver
Driver          = /usr/lib/odbc/libmyodbc.so
Setup           = /usr/lib/odbc/libodbcmyS.so
CPTimeout               =
CPReuse         =
FileUsage               = 1
-----------------------------------------------------------------------------

    Make sure you have those files in those places -- and if you only use
    MySQL you can probably do without the first set here.

ii) /etc/odbc.ini -- Here is one such entry:

-----------------------------------------------------------------------------
[beancounter]
Description             = Beancounter DB (Postgresql)
Driver                  = Postgresql
Trace                   = Yes
TraceFile               = /tmp/beancounter_odbc.log
Database                = beancounter
Servername              = localhost
UserName                =
Password                =
Port                    = 5432
Protocol                = 6.4
ReadOnly                = No
RowVersioning           = No
ShowSystemTables        = No
ShowOidColumn           = No
FakeOidIndex            = No
ConnSettings            =
-----------------------------------------------------------------------------

    The only fields that matter may be Driver, Database, Servername and maybe
    Port.  I'm sorry that I don't have a stanza for MySQL in use. An older
    one on another computer is 

-----------------------------------------------------------------------------
[beancounter_mysql]
Driver       = /usr/lib/libmyodbc.so
Database     = beancounter
Servername   = localhost
ReadOnly     = 0
-----------------------------------------------------------------------------

    but I cannot test that one right now.


Hth, Dirk


-- 
Better to have an approximate answer to the right question than a precise 
answer to the wrong question.  --  John Tukey as quoted by John Chambers