How to insert reactive input values from a shiny app into a MySQL database?

Question

I created an online experiment with the shiny package for R. Let's say I have 3 reactive values called "toss", "decision" and "rating".
Additionally, I launched a MySQL database on Amazon web service RDS. The version is MySQL 5.6.22.
I successfully managed to to send non-reactive values- like the timestamp- to MySQL database. So I assume the problem is where to locate the code talking to MySQL within the Server.R code. For non-reactive values it works perfectly when the code is outside (before) the reactive server-function. But with reactive values I suppose it should be somewhere within.

I tried this code:

Server.R  
   library(shiny)
   library(RMySQL)
   library(DBI)
    con <- dbConnect(MySQL(), dbname="db", username="myname", password="mypassword", host="myhost.com", port=xxxx)
   function(input, output, session){
       sql <- reactive({
                paste("insert into scenario1 (toss, dec, rat, timestamp) 
                     values (",input$toss,",",input$decision,",",input$rating,"now())")
       })
       result<-reactive({dbSendQuery(con, sql())})
   }

This way, I do not get an error message. So maybe the error is within the insert into-code.

Additionally, I'm not sure whether the packages that I used are ideal for this purpose. I tried out a lot of things. Whenever I add a reactive value by leaving it out of the SQL-quote it stops working. I'm starting to think that RMySQL is missing that feature. There is nothing about insert into in the manual.

Is anyone able to detect the mistake I made?

When do you want that sql run? I think you'll need to wrap your dbSendQuery in an `isolate()`. — cory, May 13 '15 at 11:31
@cory Thanks for your suggestion. Would I simply add `isolate(sql())` to my code? By doing this it still doesn't work. So I assume it is not that simple. I want that sql to run at the end of the shiny session. — schindst, May 15 '15 at 13:01

score 1 · Accepted Answer · answered Nov 05 '15 at 15:12

Finally, I could make the query run with this code:

writingMarks <- function(input){ 
    con <- dbConnect(MySQL(), dbname="db", username="myname", password="mypassword", 
           host="myhost.com", port=xxxx)   
    result <- sprintf<-("insert into scenario1 (toss, dec, timestamp) values (%s,%s,%s)",
                input$toss, input$decision, "now()")
    dbSendQuery(con, result)
}

I could not make the paste query run. With sprintf there is less confusion with the commas.

And, yes I indeed had to add an isolate(). I inserted it into an observe(). So it looks like this:

observe({
    if (input$condition==1){
      isolate({
        writingMarks(input)
      })
    }
    return()
  })

score 0 · Answer 2 · answered Jul 19 '15 at 19:43

You have a problem in:

paste("insert into scenario1 (toss, dec, rat, timestamp)
values (",input$toss,",",input$decision,",",input$rating,"now())")

The issue is: No , before now():

paste("insert into scenario1 (toss, dec, rat, timestamp)
values (",input$toss,",",input$decision,",",input$rating,",now())")

That should make the query run. Look into prepared statements, that will prevent this kind of (very common everybody makes them) concatenation mistakes.

How to insert reactive input values from a shiny app into a MySQL database?

2 Answers2