R - Can I pass a command line argument using Rscript to a query using RMySQL dbConnect?

Question

I am trying to pass a site id argument as an integer from Rscript on the command line to a SQL statement inside dbConnect using RMySQL. However I am getting an error "Error in mysqlQuickSQL(conn, statement, ...) : unused arguments ("3", " AND name = 'HVAC #1 Supply Temp' ORDER BY created DESC LIMIT 15;")"

user@debian:~$ Rscript deltaTsql.R 3

is what I am running from the command prompt.

My script is

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

args <- commandArgs(TRUE)
print(length(args))
as.integer(args[1])

con <- dbConnect(MySQL(), user="user", password="password", dbname="dbname", host="host")

r1.dat <- dbGetQuery(con, "SELECT `site_id`,`name`,`value`,`created` FROM `table` WHERE `site_id` = ", args[1], " AND `name` = 'HVAC #1 Supply Temp' ORDER BY `created` DESC LIMIT 15;")
r2.dat <- dbGetQuery(con, "SELECT `site_id`,`name`,`value`,`created` FROM `table` WHERE `site_id` =", args[1], " AND `name` = 'HVAC #1 Return Temp' ORDER BY `created` DESC LIMIT 15;")

r <- merge(r1.dat,r2.dat,by=c("created","site_id"))

r$supplytemp <- (r$value.x*(9/5)+32)
r$returntemp <- (r$value.y*(9/5)+32)

r$deltaT <- (r$returntemp-r$supplytemp)
deltaT <- r$deltaT

deltaTcheck <- function(deltaT) {
if (deltaT>25) {
return(1)
}
else if (deltaT<10) {
return(-1)
}
else {
return(0)
}
}

deltaTout <- vapply(deltaT, deltaTcheck, numeric(1))
deltaTjson <- toJSON(deltaTout)
deltaTjson

I want to be able to pass the same argument to two separate SQL statements so that I can grab both separately before merging them on the site id. Any help or feedback would be greatly appreciated. Thanks

MrFlick · Accepted Answer · 2014-12-22 21:20:56Z

1

You need to paste() together your SQL statement into a single character value

r1.dat <- dbGetQuery(con, paste0("SELECT `site_id`,`name`,`value`,`created` 
    FROM `table` 
    WHERE `site_id` = ", args[1], " AND `name` = 'HVAC #1 Supply Temp' 
    ORDER BY `created` DESC LIMIT 15;"))

answered Dec 22, 2014 at 21:20

MrFlick

209k19 gold badges300 silver badges324 bronze badges

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

1 Comment

hadley Over a year ago

It would be safer to use a parameterised query

Collectives™ on Stack Overflow

R - Can I pass a command line argument using Rscript to a query using RMySQL dbConnect?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related