“first argument“ error when using shinyapps.io, rodbc to show sql query result in web

Question

First of all, I need to use R to get SQL query result from HANA database, which I finish by using RODBC in Rstudio.

Second of all, I need to share my code with others, which I use shinyapps.io to finish.

However, I need to use shinyapps to show my SQL query result on other computers, which I have the following error message:

error first argument is not an open rodbc channel

I used the answer from R shiny RODBC connection Failing, but it still does not work.

Here is my codes for ui.R and sever.R attached:

ui.R:

library(dplyr)
library(RODBC)
library(stringr)
library(ggplot2)
fluidPage(
  titlePanel("Basic DataTable"),
  fluidRow(
    DT::dataTableOutput("table")
  )
)

sever.R:

library(dplyr)
library(RODBC)
library(stringr)
library(ggplot2)
ch<-odbcConnect('HANARB1P',uid='****',pwd='****')
options(scipen = 200)
myOffice <- 0
StartDate <- 20170601
EndDate <- 20170610
office_clause = ""
if (myOffice != 0) {
  office_clause = paste(
    'AND "_outer"."/BIC/ZSALE_OFF" IN (',paste(myOffice, collapse=", "),')'
  )
}
function(input, output) {
  output$table <- DT::renderDataTable(DT::datatable({
  data <- sqlQuery(channel=ch,query=paste(' SELECT TOP 100
                                                   "/BIC/ZSALE_OFF" AS "SalesOffice",
                                                   "/BIC/ZHASHPAN" AS "CreditCard"
                                            FROM "SAPB1P"."/BIC/AZ_RT_A212"
                                            WHERE "CALDAY" BETWEEN',StartDate,'AND',EndDate,'
                                                  ',office_clause,'
                               '))
    data
  }))
}

How to use shinyapps.io and RODBC to show the SQL query result on the webpages for sharing?

According to the answer, I revised my code a little bit. But something weird happens again. When I use the code:

function(input, output) {
  output$table <- DT::renderDataTable(DT::datatable({
    data <- sqlQuery(channel=ch,query=paste(' SELECT TOP 50
                                                 "/BIC/ZSALE_OFF" AS "SalesOffice",
                                                     "/BIC/ZHASHPAN" AS "CreditCard"
                                              FROM "SAPB1P"."/BIC/AZ_RT_A212"
                                              WHERE "CALDAY" BETWEEN',StartDate,'AND',EndDate,'
                                                    ',office_clause,'
                                            '))
    data
  }))
}

I have the error information:

When I use the code:

shinyServer(
function(input, output) {
  data <- sqlQuery(channel=ch,query=paste(' SELECT TOP 50
                                                 "/BIC/ZSALE_OFF" AS "SalesOffice",
                                                     "/BIC/ZHASHPAN" AS "CreditCard"
                                              FROM "SAPB1P"."/BIC/AZ_RT_A212"
                                              WHERE "CALDAY" BETWEEN',StartDate,'AND',EndDate,'
                                                    ',office_clause,'
                                            '))
  output$table <- DT::renderDataTable(data)
}
)

I have the error information:

I am sure the channel works. If I just use run app to do this:

shiny::runApp('//paper/fchen4/feng.officeworks/mycode/myShiny')

It works fine. But I work in a company, I do not know if my firewall could have something to do with this error. But if I do not use SQL here, it is OK.

Answer 1

Well, have you checked that the channel is actually open? The error message can be the result of wrong credentials, unreachable server or anything else that would prevent a successful SQL connection.

I had no problems showing table content with the following code:

ui.R

library(shiny)

# Define UI for application that draws a histogram
shinyUI(fluidPage(

  # Application title
  titlePanel("Basic Data Table"),       
     fluidRow(
        dataTableOutput("table")
     )
))

server.R

library(shiny)
library(RODBC)

ch <- odbcConnect("S12")

# Define server logic to provide table output
shinyServer(
    function(input, output) {

        query_result <- sqlQuery(channel = ch, query = 'SELECT * FROM M_DATABASE')
        output$table <- renderDataTable(query_result)
    }
)

There is no reason to call DT::datatable() around the result of the SQL query as it already returns a data frame that can be fed into renderDataTable().

A few general hints:

• never put your logon data into the application code. In SCN Blog "HANA quick note – checking my connections and using them securely …" I explained how to securely store and use connection and logon data for SAP HANA systems. This also gives you a very easy way to check the connectivity to your HANA instance. Besides, just pointing to the ODBC DSN connection instead of providing all the parameters looks much cleaner.

• You don't need all the R libraries in the ui.R file as the code that uses libraries like RODBC is in the server.R file. Make sure to have the minimum required libraries in every file to make your life a lot easier.

• It doesn't hurt to break up long nested function parameter calls as I did it with your "calling-SQL-statement-convert-resultset-data-type-feed-it-into-render-function". It's a lot easier to follow what happens where and what fails where, when there are not too many commands in a single line.

This should work for you.