0

Im currently working on my shiny app, and what im dealing with now is viewing return message from SQL function on shiny app, after using this function.
Here is my function:

    CREATE OR REPLACE FUNCTION delivery(s_i INTEGER, n INTEGER, q INTEGER, pr DECIMAL(10,2)) RETURNS TEXT AS $$
BEGIN
    INSERT INTO inbound(inb_supplier_id, name, quantity, price) VALUES(s_i, n, q, pr);
    RETURN 'This delivery was succesfully added into data base';
END;
$$ LANGUAGE 'plpgsql';

And part of my shiny app:

tabPanel("Inbound",
                 sidebarLayout(
                     sidebarPanel(

                         radioButtons('INBVIEW', h3('Choose the data set'),
                                      choices = list('This month deliveries' = "month",
                                                     'This day deliveries' = "day")),

                         tags$br(),
                         tags$br(),

                         h3(helpText('Add new delivery')),
                         textInput('INBsup', label = 'supplier id', value = 'id'),
                         textInput('INBpro', label = 'product id', value = 'id'),
                         textInput('INBq', label = 'quantity', value = 'quantity'),
                         textInput('INBp', label = 'price', value = 'price'),
                         actionButton(inputId = "INBZATWIERDZ", label = "Add", icon = icon("refresh"))
                     ),

                     mainPanel(
                         DT::dataTableOutput(outputId = 'INBtab1'),
                         textOutput(outputId = "INBtab2")
                     )

                 )),


server <- function(input, output){

    output$INBtab1 <- DT::renderDataTable({
        INBpom <- switch(input$INBVIEW,
                       month = 1,
                       day = 2)

        if (INBpom == 1){
            dbGetQuery(con, "SELECT * FROM inbound WHERE EXTRACT(MONTH FROM delivery_date) = EXTRACT(MONTH FROM CURRENT_DATE);")
        }
        else{
            dbGetQuery(con, "SELECT * FROM inbound WHERE delivery_date = CURRENT_DATE;")
        }
    })

    observeEvent(input$INBZATWIERDZ,{
        sup <- input$INBsup
        pro <- input$INBpro
        quan <- input$INBq
        price <- input$INBp

        output$INBtab2 <- DT::renderDataTable({
            fetch(dbSendQuery(con, paste0("SELECT delivery(",sup,",",pro,",",quan,",",price,");")))
        })
    })
}

This function works of course, but unfortunately i don't know how to display this 'This delivery was succesfully added into data base' in my shiny app.
Thanks in advance!

4
  • does dbSendQuery return an object with that message? Otherwise you might try using sink() to a tempfile. Commented Feb 2, 2020 at 11:01
  • Remko Duursma! Yes, i have changed my code a bit, and it looks like this, dbSendQuery....[1,1] gives me the exact message i want to view in my app, but unfortunately doesnt work :/ Commented Feb 2, 2020 at 19:16
  • If dbSendQuery returns a text message, try displaying it with renderText in a textOutput field (not as a DT::datatable, which expects a dataframe). Commented Feb 4, 2020 at 8:32
  • Thank you very much for your time! I solved this problem yesterday, the case was that fetch(dbSendQuery) returned character type output, but R required to use as.character function... Commented Feb 5, 2020 at 10:16

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.