R Studio - Send Query to Change Table Data

#1

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!
BR2019-05-15%2017_36_52-~_Shiny-ERP%20-%20Shiny

0 Likes

#2

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.

0 Likes

#3

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
  })
0 Likes

#4

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

0 Likes

#5

Do i need for every dbSendStatement a new connection?

0 Likes

#6

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)
0 Likes

#7

Hey, fine! It works.
thank you

0 Likes

#8

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?

0 Likes

#9

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

0 Likes