[R] RODBC and schemas

Duncan Golicher
2008-05-05T15:21:57Z

I have found that the "schema.table" syntax used in Postgresql (and
Oracle) does not work directly with RODBC.

This works

library(RODBC)
con<-odbcConnect("mydb")
d<-sqlQuery(con,"select * from meso.trees")

However this does not.

d<-sqlFetch(con,"meso.trees")
Error in odbcTableExists(channel, sqtable) :
?meso.trees?: table not found on channel

Apparently the test if (!odbcValidChannel(channel)) is returning true.
The inclusion of any non alphanumeric character leads a table name to be
considered invalid.

This also means that the useful sqlSave and sqlUpdate functions cannot
be used in the usual way for tables that are not in the public schema.

This has come up several times previously e.g.
http://finzi.psych.upenn.edu/R/Rhelp02a/archive/113715.html, but no
thread that I found led to an obvious solution. Brian Ripley pointed out
the issue with the dot syntax and suggested that "you can set the schema
and then use unqualified names." Does anyone know how this can be done
for postgresql?

Thanks for any suggestions,

Duncan

--
Dr Duncan Golicher
Conservaci?n y Restauraci?n de los bosques de Chiapas
Ecolog?a y system?tica terrestre
Conservaci?n de la Biodiversidad
El Colegio de la Frontera Sur
Carretera Panamericana y Perif?rico Sur s/n
29290 San Crist?bal de las Casas, Chiapas
967 67 49000 ext 1310

Email:  dgoliche at ecosur.mx
Skype: duncangolicher
WebLog http://duncanjg.wordpress.com/

Using Thunderbird on Ubuntu Hardy 8.04