R help - Aug 2009 - problems with RODBC, sqlQuery

Hello, 

Im trying to get a Oracle database to R, but I'm having problems with most
of SQL sintax. Suppose that Im trying to bring a table "TABLE" with
columns
"COL1","COL2",.. from schema "SCHEMA". What am I
doing wrong?

library(RODBC)
channel<-odbcConnect("xxx",uid="xxx",pwd="xxx")
sqlQuery(channel, "select * from SCHEMA.TABLE")
[it works fine]
sqlQuery(channel, "select * from SCHEMA.TABLE where COL1>10")
[returns
[""[RODBC] ERROR: Could not SQLExecDirect"
[" (...) :invalid table name"]





-- 
View this message in context:
http://www.nabble.com/problems-with-RODBC%2C-sqlQuery-tp25066803p25066803.html
Sent from the R help mailing list archive at Nabble.com.

On Aug 20, 2009, at 12:49 PM, JoK LoQ wrote:
>
> Hello,
>
> Im trying to get a Oracle database to R, but I'm having problems  
> with most
> of SQL sintax. Suppose that Im trying to bring a table "TABLE"
with
> columns
> "COL1","COL2",.. from schema "SCHEMA". What
am I doing wrong?
>
> library(RODBC)
>
channel<-odbcConnect("xxx",uid="xxx",pwd="xxx")
> sqlQuery(channel, "select * from SCHEMA.TABLE")
> [it works fine]
> sqlQuery(channel, "select * from SCHEMA.TABLE where COL1>10")
> [returns
> [""[RODBC] ERROR: Could not SQLExecDirect"
> [" (...) :invalid table name"]


What versions of R and RODBC are you using and on what OS?

Try:

 > packageDescription("RODBC", fields = "Version")
[1] "1.2-6"

I have no problems on OSX 10.5.8 using RODBC 1.2-6 with queries such  
as you are using against Oracle 11g running on an RHEL 5 server.

There may be some issues with RODBC version 1.3-0 which is the latest  
on CRAN, if that is the version you are using.

Marc Schwartz