Pass Parameter into RMySQL dbSendQuery

Question

I'm having difficulties passing a value into dbSendQuery. Below is my code:

Parameter:

date_param <- '2014_02_02'

Query:

  pull <- dbSendQuery(test_db, "select distinct product_id, group 
                                 from test_table 
                                 where date between date_sub(",date_param,", interval 1 year) and ",date_param," ;")

pulled_data <- fetch(pull, n=-1)

The error I get is:

Error in is(object, Cl) : 
  internal error in RS_DBI_getResultSet: could not find resultSet in connection

Is the issue with my syntax?

Thanks.

Answer 1

I know that this is a very old question, so I am hopeful that you have already resolved your issue. As @MrFlick suggested, paste builds a string and allows you to pass it as a single parameter. However, in so doing, you have to do some semantics to make sure dates are quoted properly. Since I do not have the table you are discussing (or information about what data is in it), I will build a simple query to show what is happening.

At present, what you are likely submitting to the database is something akin to:

date_param <- "2014_02_02"

## currently submitting
paste0("select distinct product_id, group from test_table  where date between  date_sub(", 
  date_param, ", interval 1 year)  and ", date_param, " ;")  

#> [1] "select distinct product_id, group from test_table  where date between  date_sub(2014_02_02, interval 1 year)  and 2014_02_02 ;"


## analogous query
paste0("select  date_sub(", date_param, ", interval 1 year) as start , ", date_param, 
  " as end;")
#> [1] "select  date_sub(2014_02_02, interval 1 year) as start , 2014_02_02 as end;"


## need to quote the dates
paste0("select  date_sub('", date_param, "', interval 1 year) as start , '", 
  date_param, "' as end;")
#> [1] "select  date_sub('2014_02_02', interval 1 year) as start , '2014_02_02' as end;"

If you copy and paste the last query into a SQL Editor, it should work the way that you expect (notice the quotes around the dates).

The benefit of using paste (or paste0 - no separator) is that you can capture exactly the query that is being sent and test it yourself in a SQL Editor. The downside is the irritating quoting behavior and risk to SQL Injection (as well as a lack of type safety). The better solution is to use variable substitution, which differs a bit based on the implementation (RMySQL and RMariaDB use ?, RPostgreSQL and RPostgres use $1,$2,$3,etc.). See the DBI Spec for more info - you can also sometimes get a performance gain by preparing the query ahead-of-time.

Note also that the RMySQL package is being phased out - RMariaDB is compatible with both MySQL and MariaDB and is where future development looks to be taking place. (From my exploration, it looks like the RMySQL package does not have great support for prepared queries like this)

library(RMariaDB)

date_param <- "2014_02_02"

conn <- dbConnect(MariaDB(), ...)

query <- "select date_sub(?, interval 1 year) as start, date(?) as end;"

rs <- dbSendQuery(conn, query, list(date_param, date_param))
dbFetch(rs)
#>        start        end
#> 1 2013-02-02 2014-02-02
dbClearResult(rs)
#> [1] TRUE

Answer 2

You need to combine your SQL statement into one string. Right now you are passing the pieces as separate parameters. Try using paste() to make your stateent.

pull <- dbSendQuery(test_db, paste("select distinct product_id, group 
                             from test_table 
                             where date between date_sub(",date_param,", interval 1 year) and ",date_param," ;"))