how to pass variables from r program to mysql function

Question

I am new to R Programming and MySQL. I have a requirement where I need to read the variables from R Programming and need to pass those as inputs to a function in MySQL. Can someone please help me in this regard?

options(echo=FALSE)
args <- commandArgs(trailingOnly=TRUE)
start.date <- as.Date(args[1])
end.date <- as.Date(args[2])
library(RMySQL)
mydb = dbConnect(mydb,xxxx)
rs <- dbSendQuery(mydb,"SELECT COMPUTE_WEEKS(start.date,end.date) FROM DUAL;")

Below is the error I am getting.

rs <- dbSendQuery(mydb, "SELECT COMPUTE_WEEKS(start.date,end.date) FROM DUAL;") Error in .local(conn, statement, ...) : could not run statement: Unknown table 'start' in field list

Need to understand how to read data from r-program and pass it as arguments to procedures/functions in My-SQL. Currently using RMysql library.

Regards Bhanu Pratap M

Tim Biegeleisen · Accepted Answer · 2016-10-10 07:29:12Z

1

If you want to include the R variables called start.date and end.date, then you can use paste to build the query string:

query <- paste0("SELECT COMPUTE_WEEKS(", start.date, ", ", end.date, ") FROM DUAL;")
mydb <- dbConnect(mydb, xxxx)
rs <- dbSendQuery(mydb, query)

answered Oct 10, 2016 at 7:29

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

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

Comments

stats_guy · Accepted Answer · 2016-10-10 07:42:40Z

1

You should first set the value of the dates in consideration as variable. Then, you can easily put them into a paste0-statement. See the code below. Moreover, you need to put quotes around dates in MySQL. This is not considred in the answer from Tim Biegeleisen.

library(RMySQL)

#Set start and end date
start <- "2016-01-01" 
end   <- "2016-02-01"

un   <- ""  # Set user name
pw   <- ""  # Set pass word
host <- ""   # set host
name <- ""  # data base name

# Conncect to data base
db   <- dbConnect(MySQL(), user = un, password = pw, host = host, dbname = name) 

# State your query  
query      <- paste0("SELECT COMPUTE_WEEKS('", start, "', '", end, "') FROM DUAL")

# Send query to data base
send_query <- dbSendQuery(db, query) 

# Unfetch query to get results in data frame
df         <- fetch(send_query, n = -1)

edited Oct 10, 2016 at 7:42

answered Oct 10, 2016 at 7:35

stats_guy

7171 gold badge10 silver badges28 bronze badges

2 Comments

Pratap Over a year ago

Thanks for the help. The above suggested methodology worked.

stats_guy Over a year ago

@Pratap Thanks for your reply. If my answer was helpful you can accept it as the correct answer. Just hover with your mouse over the check mark left to my answer and click on it. Then you also upvote my answer.

Collectives™ on Stack Overflow

how to pass variables from r program to mysql function

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related