0

I am currently working on a shiny application for work that is partially based off of this tutorial by Niels van der Velden. I am running into issues with the following code chunk:

observeEvent(input$submit_edit, priority = 20, {
SQL_df <- dbReadTable(pool, "responses_df")row_selection <- SQL_df[input$responses_table_row_last_clicked, "row_id"]dbExecute(pool, sprintf('UPDATE "responses_df" SET "name" = ?, "sex" = ?, "age" = ?,"comment" = ? WHERE "row_id" = ("%s")', row_selection),param = list(input$name,input$sex,input$age,input$comment))removeModal() 
})

The code is supposed to execute an SQL query which updates the database to reflect changes made in a dialog box. When used with RSQLite, this code works just fine. However, when using RPostgres, I receive the following error:

Warning: Error in : Failed to prepare query: ERROR:  syntax error at or near ","
LINE 1: UPDATE "responses_df" SET "name" = ?, "sex" = ?, "age" = ...
                                            ^

I have tried replacing the ? parameters with $ ones in the hope that this was a simple syntax difference, but that leads to the following error:

Warning: Error in : Failed to prepare query: ERROR:  syntax error at or near "$"
LINE 1: UPDATE "responses_df" SET "name" = $, "sex" = $, "age" = ...
                                           ^

Edit: Fixed names for consistency.

PRATHEESH PC
  • 1,461
  • 1
  • 3
  • 15
Jon LLoyd
  • 1
  • 4

1 Answers1

0

Okay, so after a few hours of trial-and-error, I've finally realized the adjustments that needed to be made:

  1. Change ? to SEQUENTIAL $ in the code (etc. ("name" = ? and "sex" = ? become "name" = $1 and "sex" = $2
  2. Remove the double equals sign from WHERE "row_id" and just use a single =.
  3. Change the double quotes around %s to single and remove parentheses. Note: You will have to use the \ escape character to do this.

I've included the corrected code below: observeEvent(input$submit_edit, priority = 20, {

SQL_df <- dbReadTable(pool, "responses_df")

row_selection <- SQL_df[input$responses_table_row_last_clicked, "row_id"]

dbExecute(pool, sprintf('UPDATE "responses_df" SET "name" = $1, "sex" = $2, "age" = $3,"comment" = $4 WHERE "row_id" = \'%s\'', row_selection),
param = list(input$name,input$sex,input$age,input$comment))

removeModal() 
})

In effect, it really was just an issue of differing syntax. It seems Postgres is just different enough to annoy me. Makes sense, as I'm not overly fond of SQL in the first place. Still, I hope this helps anyone else who may approach this issue.

Jon LLoyd
  • 1
  • 4