CODEWITHSUNDEEP
mysqlr
Pratap
Pratap

Reputation: 21

how to pass variables from r program to mysql function

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

Upvotes: 2

Views: 1481

Answers (2)

stats_guy
stats_guy

Reputation: 717

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)

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

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)

Upvotes: 1

Related Questions