With regards to your concern - export the R object to a MySQL table (the RMySQL documentation tells you how), then run an inner join. Or if the table to query isn't that big, pull it in R and subset it with %in%. You could use system.time() to see which runs faster.
> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Waverley > Sent: Tuesday, June 05, 2007 1:49 PM > To: Chris Stubben > Cc: [email protected] > Subject: Re: [R] RMySQL question, sql with R vector or list > > 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. > ______________________________________________ [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.
