[R] RODBC and schemas

[Duncan Golicher]

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