Thanks Chris.
I think this should work. I have one more question regarding this. Is that
possible to write some PL/SQL scripts integrated inside R, it is the same
token like I have asked in my previous question. In this way, native R data
structures can be passed to the MYSQL data base directly to interrogate
dynamically, rather than statically like using paste. One concern using
paste to construct the SQL command is this: what about if the ID list in
your sample becomes very large, is this a problem to construct this way?
I will try to follow your advice but I hope someone on the mailing list can
teach me how to integrate R data structure with MYSQL like PL/SQL.
Thanks much.
Bruce
On 6/5/07, Chris Stubben <[EMAIL PROTECTED]> wrote:
>
>
> > I am trying to write a RMySQL sql script inside R such that part of the
> SQL
> > would be R list or vector. For example, I want to select * from Atable
> > where ID would equal to a members of R list or vector of "1, 2,
> 3". Here
> > the ID list was generated inside R and then try to feed to mysql to call
> to
> > get additional data sets.
> >
>
>
> You could pass a comma-separated list of IDs to the sql IN operator
>
>
> ## in MySQL
>
> CREATE table tmp (id int, name char(1));
> insert into tmp values (1, "A"), (2, "B"), (3, "C"), (4, "D"), (5, "E");
>
>
>
> ### in R
>
>
> library(RMySQL)
>
> con <- dbConnect("MySQL", dbname="test" )
>
>
> id.in<-function(ids)
> {
> dbGetQuery(con, paste("select * from tmp
> where id IN (", paste(ids,collapse=","), ")") )
> }
>
>
>
> id.in(2:4)
> id name
> 1 2 B
> 2 3 C
> 3 4 D
>
>
> ## simple lists also work
>
> id.in(list(1,4,5))
> id name
> 1 1 A
> 2 4 D
> 3 5 E
>
>
> Chris
>
> ______________________________________________
> [email protected] 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.
>
--
Waverley @ Palo Alto
[[alternative HTML version deleted]]
______________________________________________
[email protected] 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.
