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,
--
Sebastian Luque
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
PS iii) Make sure you connect with dsn, uid and pwd arguments:> db <- odbcConnect("beancounter", uid="edd", pwd="********") > dbRODB 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 -- 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
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: [...]> ----------------------------------------------------------------------------- > 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 > -----------------------------------------------------------------------------Except for PostgreSQL, this was the default configuration that MySQL or unixODBC must have entered here during installation.> ii) /etc/odbc.iniThis 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. -- Sebastian Luque
Sebastian Luque <sluque at mun.ca> wrote: [...]> Can the latter go in ~/.odbc.ini as I said before? I hope so, as this > would allow for much easier maintenance.Yes, I just found ODBCConfig, a tool that is supplied with unixodbc for this sort of manipulations. Cheers, -- Sebastian Luque
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:> 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: > > [...] > >> ----------------------------------------------------------------------------- >> 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 >> ----------------------------------------------------------------------------- > > Except for PostgreSQL, this was the default configuration that MySQL or > unixODBC must have entered here during installation. > > >> ii) /etc/odbc.ini > > 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. > > -- > Sebastian Luque > > ______________________________________________ > R-help at stat.math.ethz.ch mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide! http://www.R-project.org/posting-guide.html >-- Brian D. Ripley, ripley at stats.ox.ac.uk Professor of Applied Statistics, http://www.stats.ox.ac.uk/~ripley/ University of Oxford, Tel: +44 1865 272861 (self) 1 South Parks Road, +44 1865 272866 (PA) Oxford OX1 3TG, UK Fax: +44 1865 272595
Hi Sebastian. Another option you "may" want to entertain is the Rdbi packages from GRASS, http://grass.itc.it/statsgrass/r_and_dbms.html. This page begins with Postgresql but does have an option for MySQL. I'm running FreeBSD and unixODBC and never got RODBC running as it should. Also, the stats these guys thow up from a speed standpoint are fairly impressive. Again, another option for you to consider if you haven't already. Charles [[alternative HTML version deleted]]