CODEWITHSUNDEEP
sqlrshinyrodbcshinyapps
Feng Chen
Feng Chen

Reputation: 2253

shinyapps.io does not work when my shiny use RODBC to link a SQL database

On my local computer, I use shiny to design a web page to show the analysis result. The data is extracted from the company's SQL database using RODBC to link the database to R. The code is like this:

library(shiny)
library(shinydashboard)
library(DT)
library(RODBC)
library(stringr)
library(dplyr)
DNS <- '***'
uid <- '***'
pwd <- '***'
convertMenuItem <- function(mi,tabName) {
  mi$children[[1]]$attribs['data-toggle']="tab"
  mi$children[[1]]$attribs['data-value'] = tabName
  mi
}
sidebar <- dashboardSidebar(
  sidebarMenu(
    convertMenuItem(menuItem("Query1",tabName="Query1",icon=icon("table"),
                             dateRangeInput('Date1','Date Range',start = Sys.Date()-1, end = Sys.Date()-1,
                                            separator=" - ",format="dd/mm/yy"),
                             textInput('Office1','Office ID','1980'),
                             submitButton("Submit")), tabName = "Query1"),
    convertMenuItem(menuItem("Query2",tabName="Query2",icon=icon("table"),
                             dateRangeInput('Date2','Date Range',start = Sys.Date()-1, end = Sys.Date()-1,
                                            separator=" - ",format="dd/mm/yy"),
                             textInput('Office2','Office ID','1980'),
                             submitButton("Submit")), tabName = "Query2"),
  )
)
body <- dashboardBody(
  tabItems(
    tabItem(tabName="Query1",
            helpText('********************************'),
            fluidRow(
              column(12,DT::dataTableOutput('table1'))
              )
            ),
    tabItem(tabName = "Query2",h2("Widgets tab content"))
   )
)
dashboardheader <- dashboardHeader(
  title = 'LOSS PREVENTION'
) 
ui <- dashboardPage(
  skin='purple',
  dashboardheader,
  sidebar,
  body
)
server <- function(input, output) {
  output$table1 <- DT::renderDataTable({
    ch<-odbcConnect(DNS,uid=uid,pwd=pwd)
    a <- sqlQuery(ch,paste(' ***'))
    odbcClose(ch)
    DT::datatable(a,options = list(scrollX=T))
  })
}
shinyApp(ui, server)

Then, I have my account on shinyapps.io. And use rsconnect to deploy this programm. And the deployment is successful.

But when I use https://myAccount.shinyapps.io/myshiny/ to access my code. I have the following error:

2018-05-10T00:57:38.473259+00:00 shinyapps[340325]: Warning in RODBC::odbcDriverConnect("DSN=****;UID=****;PWD=****") :
2018-05-10T00:57:38.473262+00:00 shinyapps[340325]:   [RODBC] ERROR: state IM002, code 0, message [unixODBC][Driver Manager]Data source name not found, and no default driver specified

But, if there is no RODBC and SQL database involved in my code, the code works fine.

So, the problem is because shinyapps.io cannot access my company's SQL database. How can I deal with it?

Upvotes: 1

Views: 443

Answers (1)

Ralf Stubner
Ralf Stubner

Reputation: 26823

The app works on your computer because the Data Source Name (DSN) has been configured there. It is not configured on shinyapps.io. According to this help article you can use for example

odbcDriverConnect('Driver=FreeTDS;TDS_Version=7.0;Server=<server>;Port=<port>;Database=<db>;Uid=<uid>;Pwd=<pw>;Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;')

A more complete treatment can be found in the documentation.

Upvotes: 3

Related Questions