Re: [R] rodbc or unixodbc error
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]] __ R-help@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
Re: [R] rodbc or unixodbc error
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. - [EMAIL PROTECTED]:~ 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 __ R-help@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
Re: [R] rodbc or unixodbc error
PS iii) Make sure you connect with dsn, uid and pwd arguments: db - odbcConnect(beancounter, uid=edd, pwd=) db 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 -- 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 __ R-help@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
Re: [R] rodbc or unixodbc error
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 [EMAIL PROTECTED] wrote: [...] - [EMAIL PROTECTED]:~ 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@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
Re: [R] rodbc or unixodbc error
Sebastian Luque [EMAIL PROTECTED] 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 __ R-help@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
Re: [R] rodbc or unixodbc error
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 [EMAIL PROTECTED] wrote: [...] - [EMAIL PROTECTED]:~ 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@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, [EMAIL PROTECTED] 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, UKFax: +44 1865 272595 __ R-help@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
