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!

tadejow
  • 1
  • 1
  • does `dbSendQuery` return an object with that message? Otherwise you might try using `sink()` to a tempfile. – Remko Duursma Feb 02 '20 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 :/ – tadejow Feb 02 '20 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). – Remko Duursma Feb 04 '20 at 08: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... – tadejow Feb 05 '20 at 10:16

0 Answers0