I have a Shinydashboard with dynamic "messages", "notifications" and "tasks" stored in MySQL database. I need to be able to delete and insert new records in the respective "messages", "notifications" and "tasks" tables in MySQL.
I have created the following input interface to update the "messages" table:
using the following code:
tabItem(tabName = "content",
h2("Messages, Notifications and Tasks"),
fluidRow(
box(title = "Message Details",
textInput("from","Message From"),
textInput("message", "Message Content"),
textInput("time", "Enter date and/or message time"),
numericInput("ID", "Enter message ID Number", value = ''),
actionButton("newMsg", "New Message"),
actionButton("deleteMsg", "Delete Message")
)
)
)
My connection to MySQL Server is as follows:
con <- dbConnect(MySQL(),
user = ('root'),
password = ('ruvimboML55AMG'),
host = ('localhost'),
dbname = ('healthcare_mining'))
I have the following function for inserting records into the "messages" table through the above interface in Global Environment:
Function to insert a record, receives the 'Table name', 'Column Names', and 'VALUES'
insertData <- function(table, values) {
#connect to data base
query <- sprintf(
"INSERT INTO %s (%s) VALUES (%s,'%s');",
table,
values)
#submit the query
dbSendQuery(con, query)
}
On the server side, my code for execution after clicking the "newMsg" button is as follows:
Insert message
observeEvent(input$newMsg,{
req(input$from, input$message)
insertData("messagesTable", paste("from", input$from, "message", input$message, "time", input$time, "ID", input$ID, sep = "="))
})
The "ID" field is AUTO_INCREMENT and therefore takes a "NULL" value.
The "deleteMsg" code is working perfectly. I however get the following error message when i try to insert new messages into the MySQL "messagesTable":
Warning: Error in sprintf: too few arguments
Can any one assist on this?
Regards,
Chris