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,

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 — CuriousBeing
– CuriousBeing, Commented Mar 15, 2016 at 11:04
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. — Duy Bui
– Duy Bui, Commented Mar 15, 2016 at 11:15
The variable will be global and can be used for any sql statement within your R code. — CuriousBeing
– CuriousBeing, Commented Mar 15, 2016 at 11:22
Give me a reproducible example of your dataset by copying the output of dput(clients) — CuriousBeing
– CuriousBeing, Commented Mar 15, 2016 at 11:23
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") — Duy Bui
– Duy Bui, Commented Mar 15, 2016 at 14:11

Duy Bui · Accepted Answer · 2016-04-05 15:08:44Z

12

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

answered Apr 5, 2016 at 15:08

Duy Bui

1,3966 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Execute multiple SQL commands at once on R

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related