R help - Jul 2008 - Using RODBC to use SQL queries

Megan J Bellamy schrieb:
 > After that I'd like to use the odbcQuery function and the SQL
statement:
 >
 > SELECT * FROM table_name WHERE SAMPLE_YEA LIKE "1965%"
 > According to the RODBC PDF, the odbcQuery command should accept any 
valid SQL statement. Is there something wrong with my syntax? I have 
tried a few things, like attaching the tables to the data frame but with 
no luck. According to the PDF, I should be able to use this argument:
 >> odbcQuery(channel, query, rows_at_time,=attr(channel,
"rows_at_time"))
 >
 > I have also tried to use arguments like:
 >
 >> sqlColumns(channel, "SAMPLE_YEA")
 > but with no luck. Any help with this would be much appreciated! 
Thanks in advance,

Hello,

which error message do you get, what does not work?

Just a blind guess:
Maybe the problem lies within the % - Sign? Try to double it -  ...LIKE 
"1965%%"

Here an working RODBC - Example:

library(RODBC)
channel <- odbcConnect("myODBC", uid="myUser",
case="tolower")
sql <- "select x,y,z from mytable"
myresult<- (sqlQuery(channel, sql))

Best wishes,
Albin

-- 
---------------------------------------------------------------------
| Albin Blaschka, Mag. rer.nat - Salzburg, Austria
| http://www.albinblaschka.info   http://www.thinkanimal.info
| It's hard to live in the mountains, hard, but not hopeless!

You should at least start with higher-level functions like sqlFetch(). Oh, 
and reading the posting guide (you show us no examples let alone error 
message) and the tests.R examples file which comes with RODBC.

SQL syntax is DBMS-specific, so we can't comment on abstract examples (and 
it is a long time since I used Oracle).

On Wed, 23 Jul 2008, Megan J Bellamy wrote:
> Hello,
>
> I am new to the RODBC package, but I have looked over the PDF as well as a
few websites that go over the SQL language. I can connect to my database fine
using
>
>> channel<-odbcConnect("Oracle ODBC")
> # then am prompted to enter my user id and password
>
> After that I'd like to use the odbcQuery function and the SQL
statement:
>
> SELECT * FROM table_name WHERE SAMPLE_YEA LIKE "1965%"
> According to the RODBC PDF, the odbcQuery command should accept any valid
SQL statement. Is there something wrong with my syntax? I have tried a few
things, like attaching the tables to the data frame but with no luck. According
to the PDF, I should be able to use this argument:
>
>> odbcQuery(channel, query, rows_at_time,=attr(channel,
"rows_at_time"))
>
> I have also tried to use arguments like:
>
>> sqlColumns(channel, "SAMPLE_YEA")
> but with no luck. Any help with this would be much appreciated! Thanks in
advance,
>
> Megan
>
> ______________________________________________
> 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.
>
-- 
Brian D. Ripley,                  ripley at stats.ox.ac.uk
Professor of Applied Statistics,  http://www.stats.ox.ac.uk/~ripley/
University of Oxford,             Tel:  +44 1865 272861 (self)
1 South Parks Road,                     +44 1865 272866 (PA)
Oxford OX1 3TG, UK                Fax:  +44 1865 272595

Hello,

I am new to the RODBC package, but I have looked over the PDF as well as a few
websites that go over the SQL language. I can connect to my database fine using
>channel<-odbcConnect("Oracle ODBC")
# then am prompted to enter my user id and password

After that I'd like to use the odbcQuery function and the SQL statement:

SELECT * FROM table_name WHERE SAMPLE_YEA LIKE "1965%"
According to the RODBC PDF, the odbcQuery command should accept any valid SQL
statement. Is there something wrong with my syntax? I have tried a few things,
like attaching the tables to the data frame but with no luck. According to the
PDF, I should be able to use this argument:
>odbcQuery(channel, query, rows_at_time,=attr(channel,
"rows_at_time"))
I have also tried to use arguments like:
>sqlColumns(channel, "SAMPLE_YEA") 
but with no luck. Any help with this would be much appreciated! Thanks in
advance,

Megan