Dear list - I am having a problem using RODBC to access records from tables in a PostgreSQL database. There is no problem establishing the connection using chnl <- odbcConnect (dsn= ... etc. The DSN seems to be properly set up using the PostgreSQL Unicode ODBC driver, and sqlTables(chnl) works OK and produces a list of tables in the database (they are all in a schema called 'source'). But> seiz.df <- sqlFetch(chnl, 'source.MAIN')fails with the error message Error in odbcTableExists(channel, sqtable) : 'source.main': table not found on channel This occurs even though the table 'MAIN' is listed in schema 'source' in the output of sqlTables(chnl). I am using PostgreSQL version 9.0 and the database is installed on a local server (localhost) on a PC running Windows 7 and R2.12.1. I have tried version 8.4 of PostgreSQL with the same results. The local setup is for development purposes and the final database will eventually be installed on a remote server. A further bit of info: I am able to access another PostgreSQL database on a remote server for a different project with no problems. However, the server is running version 7.4 of PostgreSQL. Any help would be much appreciated. Many thanks, Bob Burn [[alternative HTML version deleted]]
Are you using RPostgreSql package? I use this quite effectivly to interact with my database. HTH Stephen On Jan 23, 2011, at 10:48 AM, Robert W. Burn wrote:> Dear list - > > I am having a problem using RODBC to access records from tables in a > PostgreSQL database. > There is no problem establishing the connection using chnl <- > odbcConnect (dsn= ... etc. > The DSN seems to be properly set up using the PostgreSQL Unicode > ODBC driver, and > sqlTables(chnl) works OK and produces a list of tables in the > database (they are all in a schema called 'source'). But >> seiz.df <- sqlFetch(chnl, 'source.MAIN') > fails with the error message > Error in odbcTableExists(channel, sqtable) : > 'source.main': table not found on channel > This occurs even though the table 'MAIN' is listed in schema > 'source' in the output of sqlTables(chnl). > > I am using PostgreSQL version 9.0 and the database is installed on a > local server (localhost) on a PC running Windows 7 and R2.12.1. > I have tried version 8.4 of PostgreSQL with the same results. > The local setup is for development purposes and the final database > will eventually be installed on a remote server. > > A further bit of info: I am able to access another PostgreSQL > database on a remote server for a different project with no > problems. However, the server is running version 7.4 of PostgreSQL. > > Any help would be much appreciated. > > Many thanks, > Bob Burn > > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code.
Robert - If sqlTables() works, then R already knows the name of your database. Do you get a different result if you try>> seiz.df <- sqlFetch(chnl, 'MAIN')- Phil Spector Statistical Computing Facility Department of Statistics UC Berkeley spector at stat.berkeley.edu On Sun, 23 Jan 2011, Robert W. Burn wrote:> Dear list - > > I am having a problem using RODBC to access records from tables in a PostgreSQL database. > There is no problem establishing the connection using chnl <- odbcConnect (dsn= ... etc. > The DSN seems to be properly set up using the PostgreSQL Unicode ODBC driver, and > sqlTables(chnl) works OK and produces a list of tables in the database (they are all in a schema called 'source'). But >> seiz.df <- sqlFetch(chnl, 'source.MAIN') > fails with the error message > Error in odbcTableExists(channel, sqtable) : > 'source.main': table not found on channel > This occurs even though the table 'MAIN' is listed in schema 'source' in the output of sqlTables(chnl). > > I am using PostgreSQL version 9.0 and the database is installed on a local server (localhost) on a PC running Windows 7 and R2.12.1. > I have tried version 8.4 of PostgreSQL with the same results. > The local setup is for development purposes and the final database will eventually be installed on a remote server. > > A further bit of info: I am able to access another PostgreSQL database on a remote server for a different project with no problems. However, the server is running version 7.4 of PostgreSQL. > > Any help would be much appreciated. > > Many thanks, > Bob Burn > > > > > [[alternative HTML version deleted]] > > ______________________________________________ > R-help at r-project.org mailing list > https://stat.ethz.ch/mailman/listinfo/r-help > PLEASE do read the posting guide http://www.R-project.org/posting-guide.html > and provide commented, minimal, self-contained, reproducible code. >
I think this is a problem with quotes. If you look good, you see: seiz.df <- sqlFetch(chnl, 'source.MAIN') ... 'source.main': table not found on channel You asked "MAIN", but your db can't find "main". If you use seiz.df <- sqlFetch(chnl, '\"source\".\"MAIN\"') , you problem should be gone. Bart -- View this message in context: http://r.789695.n4.nabble.com/Problem-reading-PostgreSQL-data-with-RODBC-tp3232706p3233977.html Sent from the R help mailing list archive at Nabble.com.