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
Duncan Golicher writes: [snip]> 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?[snip] The SQL-Syntax for this is: SET search_path = schemaname, anotherschema, pg_catalog; So, you have to pass this to postgres beforehand... Remark: pg_catalog is necessary, it is needed for general, built-in functions of postgres Hope that helps, Greetings, Albin -- | Albin Blaschka, Mag.rer.nat. | Etrichstrasse 26, A-5020 Salzburg, Austria | * www.albinblaschka.info * www.thinkanimal.info * | - It's hard to live in the mountains, hard but not hopeless!
Many thanks Albin,
Problem solved. This works fine for me now.
library(RODBC)
con<-odbcConnect("mydb")
odbcQuery(con,"SET search_path =meso, pg_catalog;")
d<-sqlFetch(con,"trees")
sqlSave(d,"trees2")
Duncan
Albin Blaschka wrote:> Duncan Golicher writes:
>
> [snip]
>> 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?
> [snip]
>
> The SQL-Syntax for this is:
> SET search_path = schemaname, anotherschema, pg_catalog;
>
> So, you have to pass this to postgres beforehand...
> Remark: pg_catalog is necessary, it is needed for general, built-in
> functions of postgres
>
> Hope that helps,
>
> Greetings, Albin
>
>
--
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