Skip to content

rodbc or unixodbc error

6 messages · Dirk Eddelbuettel, Sébastien Bihorel, Brian Ripley

Original
Sébastien Bihorel
# 
Hi,

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.

Best wishes,
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
Dirk Eddelbuettel
# 
PS  iii)  Make sure you connect with dsn, uid and pwd arguments:
RODB Connection 3
Details:
  case=nochange
  DSN=beancounter
  DATABASE=beancounter
  SERVER=basebud
  PORT=5432
  UID=edd
  PWD=
  ReadOnly=No
  Protocol=6.4
  FakeOidIndex=No
  ShowOidColumn=No
  RowVersioning=No
  ShowSystemTables=No
  ConnSettings=
  Fetch=100
  Socket=4096
  UnknownSizes=0
  MaxVarcharSize=254
  MaxLongVarcharSize=8190
  Debug=0
  CommLog=0
  Optimizer=1
  Ksqo=1
  UseDeclareFetch=0
  TextAsLongVarchar=1
  UnknownsAsLongVarchar=0
  BoolsAsChar=1
  Parse=0
  CancelAsFreeStmt=0
  ExtraSysTablePrefixes=dd_
  
  LFConversion=0
  UpdatableCursors=1
  DisallowPremature=0
  TrueIsMinus1=0
  BI=0
  ByteaAsLongVarBinary=0
  UseServerSidePrepare=0

Dirk
Sébastien Bihorel
# 
Hi Dirk,

That worked beautifully, thanks a lot! I was able to connect to a test
database, save to it (sqlSave), and query it (odbcQuery). Some comments
below for future reference.
Dirk Eddelbuettel <edd at debian.org> wrote:
[...]
Except for PostgreSQL, this was the default configuration that MySQL or
unixODBC must have entered here during installation.
This file was empty in my system, so adapting your recommendation:

,-----[ /etc/odbc.ini (lines: 1 - 7) ]
| [test]
| Driver       = /usr/lib/odbc/libmyodbc.so
| Database     = test
| Servername   = localhost
| ReadOnly     = 0
| Port	       = 3306
`-----

and followed the same template to include the rest of my databases further
down.

Can the latter go in ~/.odbc.ini as I said before? I hope so, as this
would allow for much easier maintenance.

Thanks again for the helpful reply.
Sébastien Bihorel
#
Sebastian Luque <sluque at mun.ca> wrote:
[...]
Yes, I just found ODBCConfig, a tool that is supplied with unixodbc for
this sort of manipulations.

Cheers,
Brian Ripley
# 
For future information, this is almost exactly the contents of the README 
file in the RODBC package, which contains further useful hints.

The unixODBC site (www.unixODBC.org/odbcinst.html) has a useful 
tutorial, too.
On Mon, 21 Feb 2005, Sebastian Luque wrote: