How to insert reactive input values ​​from a brilliant application into a MySQL database?

Question

I created an online experiment with a brilliant package for R. Let's say I have 3 reactive values called “quit”, “decision” and rating .
In addition, I launched the MySQL database on the Amazon RDS web service. MySQL Version 5.6.22 .
I managed to send non-reactive values ​​- as a timestamp - to the MySQL database. Therefore, I guess the problem is where to find the code that speaks MySQL in Server.R code. For non-reactive values, it works fine when the code is outside the (before) reactive server function. But with reactive values, I suppose it should be somewhere inside.

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 am not getting an error message. So maybe the error is in the insert into code.

Also, I'm not sure if the packages I used are ideal for this purpose. I've tried a lot. Whenever I add a reactive value, leaving it outside the SQL quote, it stops working. I'm starting to think that RMySQL does not have this feature. There is nothing in the manual about insert into .

Can anyone spot the mistake I made?

Answer 1

Finally, I can make the request 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 complete the paste request. With sprintf less comma confusion.

And yes, I really had to add isolate() . I inserted it in observe() . So it looks like this:

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

Answer 2

You have a problem:

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

Problem: No, before now ():

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

This should make the request complete. Look at prepared statements that will prevent this kind of error (very common, each one).