Run multiple SQL commands on R

Question

I use RMySQL and DBI to connect between R and MySQL

library(RMySQL) library(DBI, quietly = TRUE) 

Everything works fine for one command, for example

 sql = "select * from clients" con <- dbConnect(MySQL(),user=user, password=password, dbname=dbname, host=host) rs <- dbSendQuery(con, sql) data <- fetch(rs, n=-1) huh <- dbHasCompleted(rs) dbClearResult(rs) on.exit(dbDisconnect(con)) 

However, when I want to execute several commands with ;; between them (for example, to set a parameter), it returns an error. for instance

 sql = "SET @LAST_TEN_DAY = DATE_ADD(NOW(), INTERVAL -10 DAY); select * from clients where date > @LAST_TEN_DAY" con <- dbConnect(MySQL(),user=user, password=password, dbname=dbname, host=host) rs <- dbSendQuery(con, sql) data <- fetch(rs, n=-1) huh <- dbHasCompleted(rs) dbClearResult(rs) on.exit(dbDisconnect(con)) 

Thank you very much,

Answer 1

For several teams we need to work as follows. DbSendQuery will save the parameter

 sql = "select * from clients where date > @LAST_TEN_DAY" con <- dbConnect(MySQL(),user=user, password=password, dbname=dbname, host=host) dbSendQuery(con, 'SET @LAST_TEN_DAY = DATE_ADD(NOW(), INTERVAL -10 DAY)') rs <- dbSendQuery(con, sql) data <- fetch(rs, n=-1) huh <- dbHasCompleted(rs) dbClearResult(rs) on.exit(dbDisconnect(con))