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

[Feng Chen]

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

}

Could anyone please help me out here? How to use shinyapps.io and RODBC to show the SQL query result on the webpages for sharing?

[Joshua Spiewak]

You cannot use DSN's with shinyapps.io, you must use either an IP address or a hostname which is accessible from our IP addresses.
See the documentation for accessing your local database.

[Feng Chen]

Thanks a lot. This is my first time to do such work. I am always got confused.

For example, if I cannot use DSN, should I use odbcDriverConnect? I do not really understand the parameters in it.

Could you please be more specific?

1. If I get the IP to our local SQL sever, how can I use it?

2. If I need to use odbcDriverConnect, what parameters should I use and how to set them?

Thanks a lot

[Joshua Spiewak]

Yes, you would use odbcDriverConnect and supply a connection string.

shinyapps.io has the unixodbc system package installed, with MySQL, PostgreSQL and FreeTDS drivers configured.

Consult your database's documentation for an appropriate IP/hostname based connection string, but here are a couple examples I had lying around:

odbcDriverConnect(connection = "Driver=FreeTDS;TDS_Version=7.2;Server=mydns.database.windows.net;Port=1433;Database=mydatabase;Uid=myuserid;Pwd=mypass;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;")

odbcDriverConnect('driver=MySQL;server=xxxx;database=dbname;uid=username;pwd=password;')