0

I wrote a simple shiny app, with data that comes from a database. Here is my code:

library(RODBCext)
library(RODBC)
library(shiny)

ui <- shinyUI(

  pageWithSidebar(

    headerPanel("Hide Side Bar example"),
    sidebarPanel(
      textInput("SensorName", "Enter Sensorname below"),
      submitButton(text="Submit")
    ),
    mainPanel(
      tabsetPanel(
        tabPanel("Data", tableOutput("tbTable"))
      )

    )
  )
)
server <- function(input, output)    
{ # NOTE THE BRACE HERE

  #connect to database 
  dbhandle = odbcDriverConnect('driver={SQL Server};server=databuilt.database.windows.net;port=;database=;uid=;pwd=')

  #build query

  query = "SELECT DateTime, Value, SensorName FROM dbo.Larissa WHERE (SensorName LIKE 'Inblaast%' OR SensorName LIKE 'Buitentemp. - 48%') ORDER BY DateTime ASC"

  #store results
  res <- sqlExecute(channel = dbhandle, 
                    query = query,
                    fetch = TRUE,
                    stringsAsFactors = FALSE) 

  #close the connection
  #odbcClose(dbhandle)
  #return results
  res
  test<- head(res)
  output$tbTable <- 
    renderTable(
      test
    )
  odbcClose(dbhandle)  
} 

shinyApp(ui = ui, server = server)

When I run this shiny app in my local environment, it works perfectly. When I publish it to shinyapps.io and run it, I get this error: Disconnect from the server and when I check error by rsconnect::showLogs(account=''), I get:

2018-09-27T11:12:07.518296+00:00 shinyapps[472567]: 
2018-09-27T11:12:07.313806+00:00 shinyapps[472567]: R version: 3.5.1
2018-09-27T11:12:07.526435+00:00 shinyapps[472567]: 
2018-09-27T11:12:07.313808+00:00 shinyapps[472567]: shiny version: 1.1.0
2018-09-27T11:12:07.313809+00:00 shinyapps[472567]: httpuv version: 1.4.5
2018-09-27T11:12:07.526437+00:00 shinyapps[472567]: Listening on http://127.0.0.1:37039
2018-09-27T11:12:07.313809+00:00 shinyapps[472567]: rmarkdown version: NA
2018-09-27T11:12:07.313810+00:00 shinyapps[472567]: knitr version: NA
2018-09-27T11:12:07.313824+00:00 shinyapps[472567]: jsonlite version: 1.5
2018-09-27T11:12:07.313825+00:00 shinyapps[472567]: RJSONIO version: NA
2018-09-27T11:12:07.313830+00:00 shinyapps[472567]: htmltools version: 0.3.6
2018-09-27T11:12:09.775718+00:00 shinyapps[472567]: Warning in odbcDriverConnect("driver={SQL Server};server=databuilt.database.windows.net;port=;database=;uid=;pwd=") :
2018-09-27T11:12:09.775722+00:00 shinyapps[472567]:   [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found
2018-09-27T11:12:09.776217+00:00 shinyapps[472567]: Warning in odbcDriverConnect("driver={SQL Server};server=databuilt.database.windows.net;port=;database=;uid=;pwd=") :
2018-09-27T11:12:09.776219+00:00 shinyapps[472567]:   ODBC connection failed
2018-09-27T11:12:09.777796+00:00 shinyapps[472567]: Warning: Error in sqlExecute: odbcValidChannel(channel) is not TRUE
2018-09-27T11:12:09.790861+00:00 shinyapps[472567]:   63: <Anonymous>
2018-09-27T11:12:09.791070+00:00 shinyapps[472567]: Error in sqlExecute(channel = dbhandle, query = query, fetch = TRUE, stringsAsFactors = FALSE) : 
2018-09-27T11:12:09.791072+00:00 shinyapps[472567]:   odbcValidChannel(channel) is not TRUE
2018-09-28T08:55:22.115455+00:00 shinyapps[472567]: Server version: 1.6.8-6
2018-09-28T08:55:22.115480+00:00 shinyapps[472567]: R version: 3.5.1
2018-09-28T08:55:22.115487+00:00 shinyapps[472567]: httpuv version: 1.4.5
2018-09-28T08:55:22.115487+00:00 shinyapps[472567]: rmarkdown version: NA
2018-09-28T08:55:22.350938+00:00 shinyapps[472567]: Starting R with process ID: '20'
2018-09-28T08:55:22.115534+00:00 shinyapps[472567]: htmltools version: 0.3.6
2018-09-28T08:55:22.115479+00:00 shinyapps[472567]: LANG: en_US.UTF-8
2018-09-28T08:55:22.115506+00:00 shinyapps[472567]: RJSONIO version: NA
2018-09-28T08:55:22.115500+00:00 shinyapps[472567]: knitr version: NA
2018-09-28T08:55:22.350937+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.115486+00:00 shinyapps[472567]: shiny version: 1.1.0
2018-09-28T08:55:22.344620+00:00 shinyapps[472567]: Using jsonlite for JSON processing
2018-09-28T08:55:22.115501+00:00 shinyapps[472567]: jsonlite version: 1.5
2018-09-28T08:55:22.115658+00:00 shinyapps[472567]: Using pandoc at /opt/connect/ext/pandoc2
2018-09-28T08:55:22.377816+00:00 shinyapps[472567]: Loading required package: RODBC
2018-09-28T08:55:22.412749+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.412752+00:00 shinyapps[472567]: Attaching package: ‘RODBCext’
2018-09-28T08:55:22.412753+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.413593+00:00 shinyapps[472567]: The following objects are masked from ‘package:RODBC’:
2018-09-28T08:55:22.413594+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.413594+00:00 shinyapps[472567]:     odbcFetchRows, sqlFetchMore
2018-09-28T08:55:22.413595+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.423327+00:00 shinyapps[472567]: 
2018-09-28T08:55:22.423329+00:00 shinyapps[472567]: Listening on http://127.0.0.1:32933
2018-09-28T08:55:26.652028+00:00 shinyapps[472567]: Warning in odbcDriverConnect("driver={SQL Server};server=databuilt.database.windows.net;port=;database=;uid=;pwd=") :
2018-09-28T08:55:26.652030+00:00 shinyapps[472567]:   [RODBC] ERROR: state 01000, code 0, message [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found
2018-09-28T08:55:26.652451+00:00 shinyapps[472567]: Warning in odbcDriverConnect("driver={SQL Server};server=databuilt.database.windows.net;port=;database=;uid=;pwd=") :
2018-09-28T08:55:26.652453+00:00 shinyapps[472567]:   ODBC connection failed
2018-09-28T08:55:26.653941+00:00 shinyapps[472567]: Warning: Error in sqlExecute: odbcValidChannel(channel) is not TRUE
2018-09-28T08:55:26.674395+00:00 shinyapps[472567]:   63: <Anonymous>
2018-09-28T08:55:26.674643+00:00 shinyapps[472567]: Error in sqlExecute(channel = dbhandle, query = query, fetch = TRUE, stringsAsFactors = FALSE) : 
2018-09-28T08:55:26.674644+00:00 shinyapps[472567]:   odbcValidChannel(channel) is not TRUE

I am really confused, this is a shiny server issue or something else?

Vishesh Shrivastav
  • 2,079
  • 2
  • 16
  • 34
Pegah
  • 1
  • 1
  • 2
  • 3
    linux cant find your `SQL Server`, you need the driver https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017 for it.I suspect that shiny team doesnt have that on their boxes, hence you get errors – Pork Chop Sep 28 '18 at 09:12
  • I installed ODBC Driver for SQL Server in my windows system, but still does not work – Pegah Sep 29 '18 at 08:10
  • 1
    Again, it doesnt matter if you have it installed on your machine, you need this on the linux boxes where shinyapps are, which you dont – Pork Chop Sep 30 '18 at 21:54
  • There is any alternative way for this problem? – Pegah Oct 07 '18 at 19:02
  • yes, you can always spin your own AWS instance and have it configured yourself. Heres tutorial on how to setup on digital ocean, but the process is the same for AWS https://www.digitalocean.com/community/tutorials/how-to-set-up-shiny-server-on-ubuntu-16-04 – Pork Chop Oct 08 '18 at 06:58

0 Answers0