R Studio - Send Query to Change Table Data

[anon81940577]

Hello,

I am a R-beginner and have the following question: how can i update the data from the column "priceUoM"?
Do i need "dbSendQuery" or "dbSendStatement"?
I have attached a picture.

Thank you!
BR

[andresrcs]

dbSendStatement() is more appropriate for UPDATE sql commands.

The dbSendStatement() method only submits and synchronously executes the SQL data manipulation statement (e.g., UPDATE , DELETE , INSERT INTO , DROP TABLE , ...) to the database engine.

[anon81940577]

Perfect - thank you!
Got it. but now i have another issue:
I want to update two columns - it sends the sql statement to the table, when i click the button, but it doesnt show the correct values in the app itself.

observeEvent(
  input$actionButton_1001,
  {
    con <- DBI::dbConnect(dbDriver("SQLite"),"EIS_2.sqlite")
    df_sql <- DBI::dbReadTable(con, "Material")
    
    sql_update_price <- paste0("UPDATE Material SET priceUoM = 'EUR/kg';")
    sql_update_price2 <- paste0("UPDATE Material SET pricePerUnit = pricePerUnit / 100;")
  
    DBI::dbSendStatement(conn = con, statement = sql_update_price)
    DBI::dbSendStatement(conn = con, statement = sql_update_price2)
    
    DBI::dbDisconnect(con)
    
    
    output$dataTableOutput_1001 <- renderDataTable({
      df_sql
    },options = list(scrollX = TRUE))
    
    output$rpivotTableOutput_1001 <- renderRpivotTable(
      rpivotTable(data = df_sql, height = "100px")
    )
    global_df <<- df_sql
  })

[andresrcs]

You are reading the content of the table before updating it, that is why you get the previous values instead of the updated ones.

[anon81940577]

Do i need for every dbSendStatement a new connection?

[andresrcs]

No, just change the order of your commands

con <- DBI::dbConnect(dbDriver("SQLite"),"EIS_2.sqlite")   
    sql_update_price <- paste0("UPDATE Material SET priceUoM = 'EUR/kg';")
    sql_update_price2 <- paste0("UPDATE Material SET pricePerUnit = pricePerUnit / 100;")
  
    DBI::dbSendStatement(conn = con, statement = sql_update_price)
    DBI::dbSendStatement(conn = con, statement = sql_update_price2)
    # Read the content after updating the table not before
    df_sql <- DBI::dbReadTable(con, "Material")
    DBI::dbDisconnect(con)

[anon81940577]

Hey, fine! It works.
thank you

[anon81940577]

Last question:
As you see, i have values in cent and i want them to be in euro, because of this i set the priceUom from "EUR_Cent" to "EUR".
And then i divide the values by 100 to get them in euros. but the problem is, that everytime i click the button it now divides them by 100.. is there any posibility to just divided them once here in R?

[andresrcs]

I think you only need to do this once in your database, why are you including this in the code for your button?