在R上一次执行多个SQL命令

I am using RMySQL and DBI for the connection between R and MySQL

我使用RMySQL和DBI来连接R和MySQL

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

Everything is working fine for one command, such as

对于一个命令,一切都工作正常,例如

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 multiple commands with ";" between them (such as to set a parameter), it returns error. For example

但是,当我想用​​“;”执行多个命令时它们之间(例如设置参数),它返回错误。例如

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))

Many thanks,

1 个解决方案

#1

For multiple commands we need to work as follows. The dbSendQuery will save the parameter

对于多个命令,我们需要按如下方式工作。 dbSendQuery将保存参数

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))