R help - Feb 2011 - Insert variable in RMySQL-Statement

Dear list,
I'm working with a MySQL-Table and would like to extract selected ids that
are stored in a vector:
Ids <- c(1,2,3)

How do I insert this variable in a for loop like this:

For (a in values) {
	Temp <- dbGetQuery(con,
		"select * from db
			Where id = <ID>";)
}

My problem is to split the select-statement and insert the current id. How can I
handle this problem?

Best regards,
Arne

Maybe this solution will help you.
Ptit Bleu.

library(DBI)
library(RMySQL)
drv<-dbDriver("MySQL")
con<-dbConnect(drv, username=my_name, password=my_pwd, dbname=my_db,
host="localhost")

for (a in values) { 
    requete<-paste("select * from db where id=",a,sep="")
    rs<-dbSendQuery(con, requete)
    Temp<-fetch(rs, n=-1)
}

dbDisconnect(con)

-- 
View this message in context:
http://r.789695.n4.nabble.com/Insert-variable-in-RMySQL-Statement-tp3296870p3297147.html
Sent from the R help mailing list archive at Nabble.com.

Arne,

there might be faster or more elegant solutions, but this is a possibility:

for ( a in values ) { 
       Temp <- dbGetQuery(con, paste("select * from db where id =
",a,sep="")));
       print(Temp);
}

There must be also a way to make use of the "in" - operator: 

   "select * from db where id in (1,2,3);"

but I can't figure it out at the moment.

Best

Hugo

On Wednesday 09 February 2011 10:09:34 Arne H. Schulz
wrote:
> Dear list,
> I'm working with a MySQL-Table and would like to extract selected ids
that are stored in a vector:
> Ids <- c(1,2,3)
> 
> How do I insert this variable in a for loop like this:
> 
> For (a in values) {
> 	Temp <- dbGetQuery(con,
> 		"select * from db
> 			Where id = <ID>";)
> }
> 
> My problem is to split the select-statement and insert the current id. How
can I handle this problem?
> 
> Best regards,
> Arne
> 
> ______________________________________________
> 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.
>