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


Problem :

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?



Solution :

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()
  })

Mysql Tutorials

Mysql Howto..

MySQL Apostrophe- How to escape and return correct results with MATCH/AGAINST

how to use quote in mysql syntax?

how can i rename a database in mysql? [duplicate]

how to get the rowcount from the query itself (i.e. without mysql_num_rows() or FOUND_ROWS())

How to move one fields data from another field within same mysql database table?

How To have Dynamic SQL in MySQL Stored Procedure

How to find if entire week(s) past from creation on mySQL?

How to generate JSON in realtime/dynamically from a MySQL database

How to write mysql query to search database?

How to get the average rating for on before some day and after some days in mysql?

How should MySQL be installed in a Django application?

How to dump temporary MySQL table into a file?

How to get all values in one column in mysql

How to provide autonumbering to a field in mysql?

How to convert .sql file to tables in mysql db

how to retrive data from a table using mysql which has a parent child relation ship?

How to get this to work “SELECT COUNT(x) AS amount … WHERE amount > 0”?

How to get information with the same variables, different types from mysql? [closed]

MySQL in AWS EC2 - How does it scale?

How to access variable in subquery?

How to fetch all rows from table if like operator value match using mysql?

How to build the MySQL query to search keyword having “$”?

How to escape '\n' in Perl for a MySQL query?

How can I alter a table to drop a column if it exists?

MYSQL How to convert '2013-8-21' to date format

How to Order By with NULL in middle with MySQL

How to make handler plugin to mysql in CentOS

MySQL - How to use GROUP BY / ORDER BY with “nested” dataset?

How to query for strings based on their length

Solr / lucene search - how easy to use - which one?