NEED HELP - linking postgresql db and shiny in a web app

[Vijay singh]

I started making a web tool for mapping ICD-9 codes to their ICD-10 counterparts and made a db in postgresql.
while using shiny, I am encountering some problems:

1) How to pass the dataobject which has the fetched data from db to the output on the screen.
2) How to pass the user input made in the UI to the condition in query that fetches the data.

I prepared this code but it is not generating any output on screen, please help:

UI.R

library(shiny)

# Define UI for dataset viewer application
shinyUI(pageWithSidebar(
 
  # Application title
  headerPanel("ICD-9-10-Mapping"),
 
  # Sidebar with controls to select a dataset and specify the number
  # of observations to view
  sidebarPanel(
    textInput("icd9", "Type an ICD-9 Code here:",value='3950')),
 
  # Show a summary of the dataset and an HTML table with the requested
  # number of observations
  mainPanel(
    tableOutput("out"))))

server.R

library(shiny)
library(RPostgreSQL)

# Define server logic required to summarize and view the selected dataset
shinyServer(function(input, output) {
 
  # Return the requested dataset
  icd9Input <- reactive({input$icd9})
  #userin <- readLines(file("stdin"),1)
  con <- dbConnect(dbDriver("PostgreSQL"), user="postgres", password="xyz", dbname="ICD_9_10_Mapping", host = "localhost", port = 5432)
  query <- "SELECT icd_10_codes, flags FROM icd_9_10_pro WHERE icd_9_codes='3950'"
  res <- dbSendQuery(con, statement = query)
  out <- fetch(res, n = -1)
 
  # Generate a summary of the dataset
  output$out <- tableOutput("out")
   icd9 <- icd9Input()
   out(icd9)
})

[Owe Jessen]

Well, first, it should be renderTable, not tableOutput. Secondly, I think you should put the database-logic between icd0Input <- ... and out <- fetch ... into the renderTable-Function. Your object- and function-names look a bit confused, maybe you should clean it up first?
For example: you assign the data.frame from the fetch-command to 'out'. In the tableOutput... you call out(icd9), which R will interpret as a function call to out with parameter icd9. As you allready filtered on the icd9-codes in the query, you probably want to return out directly.  Are there any error messages on screen or in the R-Console of RStudio?

[Vijay singh]

Okay, I made some of the changes in the code and it like following:
While I tested the result for the input given directly in the Query- renderTable surely did helped out.
 please see the code now, It is showing the following error:

Error in as.vector(x, "character") : cannot coerce type 'closure' to vector of type 'character'

Please help me out here, If I make this tool successfully, my seniors are going to recommend the R-language officially for company use further...

UI.R

library(shiny)

shinyUI(pageWithSidebar(
 
  headerPanel("ICD-9-10-Mapping"),
  sidebarPanel(
  textInput("icd9", "Type an ICD-9 Code here:",value=3950)),
  mainPanel(
    tableOutput("out"))))

server.R

library(shiny)
library(RPostgreSQL)

shinyServer(function(input, output) {
 
    inputIcd <- reactive(input$icd9)
    dbConn <- function(inputICD,out){
      drv <- dbDriver("PostgreSQL")
      con <- dbConnect(drv, user="postgres", password="jst", dbname="ICD_9_10_Mapping", host = "localhost", port = 5432)

      query <- "SELECT icd_10_codes, flags FROM icd_9_10_pro WHERE icd_9_codes="

      stmt <- paste(query,inputIcd)
      res <- dbSendQuery(con, statement = stmt)

      out <- fetch(res, n = -1)

      out
    }

  output$out <- renderTable(dbConn(inputICD,out))
 
})

[Owe Jessen]

I think you will have to do some learning... one mistake i see now is that you define out as input to the function dbConn, but in the renderPlot there i no out to pass to the function.

[Vijay singh]

After a few more alteration in the code the code ran successfully and is now showing the expected output. Thankx a lot Owe.
But after a certain inputs from user the code is not working; as the no of connections have reached the limit of 16 connections: here is the error that comes on the console:

Error in postgresqlNewConnection(drv, ...) : 
  RS-DBI driver: (cannot allocate a new connection -- maximum of 16 connections already opened)

Can you help me with a code that allows me to manage the number of connections being made to the local host at a particular port no. for the PostgreSQL DB.

The updated code is as follows.....:

UI.R

library(shiny)
# Define UI for dataset viewer application
shinyUI(pageWithSidebar(
  # Application title
  headerPanel("ICD-9-10-Mapping"),
  # Sidebar with controls to select a dataset and specify the number
  # of observations to view
  sidebarPanel(

    textInput("icd9", "Type an ICD-9 Code here:",value=3950)),

  # Show a summary of the dataset and an HTML table with the requested
  # number of observations
  mainPanel(

    tableOutput("out1"))))

server.R

library(shiny)
library(RPostgreSQL)
# Define server logic required to summarize and view the selected dataset
shinyServer(function(input, output){
    # Return the requested dataset

    inputIcd <- reactive(input$icd9)               
    #assign(out,fetch(res, n = -1),envir=.GlobalEnv)

    dbConn <- function(inputICD,out){
      drv <- dbDriver("PostgreSQL")
      con <- dbConnect(drv, user="postgres", password="jst", dbname="ICD_9_10_Mapping", host = "localhost", port = 5432)
      query <- "SELECT icd_10_codes, flags FROM icd_9_10_pro WHERE icd_9_codes="

      #query2 <- 3950
      stmt <- paste(query,input$icd9)

      res <- dbSendQuery(con, statement = stmt)
      out <<- fetch(res, n = -1)
      }

  # Generate a summary of the dataset

  output$out1 <- renderTable(dbConn(inputICD,out))})

 Thanks for your guidance....A lot of things got cleared out in my head, through this......

[Owe Jessen]

This is something i ran into myself - RPostgrSQL allows only 16 concurrent connections, and you are opening another one with every execution of render... I now define con as a global object, that is outside of server().

[Joe Cheng]

Yes, either that or *immediately* after calling dbConnect(), call:

on.exit(dbDisconnect(con), add=TRUE)

On Mon, Jan 6, 2014 at 11:16 AM, Owe Jessen <jes...@econinfo.de> wrote:

This is something i ran into myself - RPostgrSQL allows only 16 concurrent connections, and you are opening another one with every execution of render... I now define con as a global object, that is outside of server().

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Joe Cheng]

There is a very bad SQL injection security flaw in the app as written:

stmt <- paste(query,input$icd9)

Imagine the user types in the textbox "0; DROP TABLE icd_9_10_pro; --", this would cause your database table to be deleted!

Unfortunately there is not a simple solution to this problem at the moment, as the database packages in R seem to be missing the functionality to escape values. I would replace the line above with

stmt <- paste(query, as.numeric(input$icd9))

if the code is meant to be a number. If not, you'll need to bring in the function I have posted here:

https://gist.github.com/jcheng5/8291758

and call

stmt <- paste(query, sql_quote(input$icd9))

[Vijay singh]

Hey, Thanks a lot Joe and Owe.....the program ran well...then I thought about upgrading the program to do the reversal also i.e now I'm enabling the user to search both ways ICD-9 to ICD-10 as well as ICD-10 to ICD-9. But I'm facing 2 problems here....

1. the program runs in a ambiguous way: the headings and the data on output are all mismatching. I did not change anything in the query part.
2.While I use the second radio button, for searching the vice-verse, the program is not actually switching to the second option. No output on screen shows up(blank main panel).

Thankx for looking:

UI.R

library(shiny)
shinyUI(pageWithSidebar(
          headerPanel("ICD-9-10-Mapping"),
          sidebarPanel(

            radioButtons(inputId = "code",
                               label="Select the Stereotype of Code to be Searched:",
                               choices= list("ICD-9-CM" = "i9",
                                             "ICD-10-CM" = "i10")),
              textInput("icd", "Type an ICD-9 Code here:",value=80363),
              submitButton("Update View"),
              helpText("NOTE: INSTRUCTIONS FOR INPUT:-"),
              helpText("1. All ICD-9/10 Codes have a maximum length of 6 Characters."),
              helpText("2. All ICD-9/10 Alphanumeric codes are Case-sensitive i.e. Codes starting with Alphabets should be input with the Alphabets in capitals."),
              helpText("3. e.g ICD-9 Code '0010' should be input as '0010' and NOT '10'."),
              helpText("4. e.g ICD-9 Code 'E9293' should be input as 'E9293' and NOT as 'e9293'."),
              helpText("5. e.g ICD-10 Code 'F12280' should be input as 'F12280' and NOT as 'f12280'.")),
          mainPanel(
            tabsetPanel(
              tabPanel("Corresponding ICD Code", tableOutput("out1")),
              tabPanel("ICD-9 Codes usage statistics"),#-, tableOutput("out2"), plotOutput("plot9")),
              tabPanel("ICD-10 Codes usage statistics")#, tableOutput("out3"), plotOutput("plot10")
                       ))))

server.R

library(shiny)
library(RPostgreSQL)
shinyServer(function(input, output){
    inputCode <- reactive({
                                  input$icd

                                  input$code
                                  switch(input$code,
                                            i9 = "ic9",
                                            i10 = "ic10")
                                  if(input$code == "ic9")
                                    {
                                      query <- "SELECT icd_10_codes, description, flags FROM icd_9_10_dia WHERE icd_9_codes ='"
                                    }
                                 else if(input$code == "ic10"){
                                      query <- "SELECT icd_9_codes, description, flags FROM icd_10_9_dia WHERE icd_10_codes ='"
                                    }
                                  stmt
                                  })
    dbConn <- function(inputCode,out){
      con <- dbConnect(dbDriver("PostgreSQL"), user="postgres", password="jst", dbname="ICD_9_10_Mapping", host = "localhost", port = 5432)
      on.exit(dbDisconnect(con), add=TRUE)

      invert <- "'"                     

      stmt <<- paste(query,toString(input$icd, width = 10),invert, sep = "")

      res <- dbSendQuery(con, statement = stmt)
      out <<- fetch(res, n = -1)}

      output$out1 <- renderTable(dbConn(inputCode,out))})