Execute multiple SQL commands at once on R

Question

I am using RMySQL and DBI for the connection between R and 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,

Stinker

You cant SQL and R like that. You have to create a variable that will hold the value of DATE_ADD(NOW(), INTERVAL, -10 DAY) and then use the SQL query SELECT * FROM CLIENTS WHERE DATE> your variable value – Tetragram

Hi Max, thanks for answering. May I know how to do so in R? Because if you execute an SQL command for creating a variable, I am not sure if that variable will be kept for the next SQL command execution. – Stinker

The variable will be global and can be used for any sql statement within your R code. – Tetragram

Give me a reproducible example of your dataset by copying the output of dput(clients) – Tetragram

Sure, please find a sampled data as follows structure (list(name = structure(c(3L, 1L, 4L, 2L), .Label = c("Anna", "Ciaran", "Hannah", "Roisin"), class = "factor"), date = structure(c(3L, 4L, 2L, 1L), .Label = c("2015-12-12 00:00:00", "2016-01-22 00:00:00", "2016-02-12 00:00:00", "2016-03-08 00:00:00"), class = "factor"), age = c(27, 28, 22, 19), job = structure(c(1L, 1L, 2L, 1L ), .Label = c("data analyst", "operator"), class = "factor")), .Names = c("name", "date", "age", "job"), row.names = c(NA, -4L), class = "data.frame") – Stinker

found one extra closing bracket, correct code is... list(name = structure(c(3L, 1L, 4L, 2L), .Label = c("Anna", "Ciaran", "Hannah", "Roisin"), class = "factor"), date = structure(c(3L, 4L, 2L, 1L), .Label = c("2015-12-12 00:00:00", "2016-01-22 00:00:00", "2016-02-12 00:00:00", "2016-03-08 00:00:00"), class = "factor"), age = c(27, 28, 22, 19), job = structure(c(1L, 1L, 2L, 1L ), .Label = c("data analyst", "operator"), class = "factor"), .Names = c("name", "date", "age", "job"), row.names = c(NA, -4L), class = "data.frame") – Shoveler

Answer 1

For multiple commands we need to work as follows. The 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))

Stinker