Problem with sql data and r shiny app

shiny
,
#1

Hi. I am trying to plot a single time series in r shiny with data from a database on my local sql server. When i publish the app it fails and returns "Disconnected from the server. Reload".

Does anyone here have a clue what im doing wrong?

My code:

library(shiny)
##library(DT)
library(odbc)
library(DBI)


################# ui #####################
ui <- fluidPage(
  plotOutput("plot1", click = "plot_click"),
  verbatimTextOutput("info")
)

################# end ui #####################

################# server #####################

server <- function(input, output) {
  
  con <- dbConnect(odbc(),
                   Driver = "SQL Server",
                   Server = "DESKTOP-7129D7L",
                   Database = "Kim",
                   Trusted_Connection = "True")
  
  query <- dbGetQuery(con, "SELECT Date,FieldValue,FieldTicker
  FROM data WHERE FieldTicker='^VIX' order by date asc")
  query$Date <- as.Date(query$Date)
  
  output$plot1 <- renderPlot({
    plot(FieldValue ~ Date, query,type="l",ylab = "",xlab="")
  })
  
  output$info <- renderText({
    paste0("x=", input$plot_click$x, "\ny=", input$plot_click$y)
  })
}
################# end server #####################

shinyApp(ui,server)


2020-11-21T08:59:06.910692+00:00 shinyapps[3134369]: Running on host: 9ac1790e1901
2020-11-21T08:59:06.916323+00:00 shinyapps[3134369]: Server version: 1.8.4.1-20
2020-11-21T08:59:06.916335+00:00 shinyapps[3134369]: LANG: en_US.UTF-8
2020-11-21T08:59:06.916339+00:00 shinyapps[3134369]: R version: 3.6.3
2020-11-21T08:59:06.916341+00:00 shinyapps[3134369]: shiny version: 1.5.0
2020-11-21T08:59:06.916341+00:00 shinyapps[3134369]: httpuv version: 1.5.4
2020-11-21T08:59:06.916342+00:00 shinyapps[3134369]: rmarkdown version: (none)
2020-11-21T08:59:06.916409+00:00 shinyapps[3134369]: knitr version: (none)
2020-11-21T08:59:06.916410+00:00 shinyapps[3134369]: jsonlite version: 1.7.0
2020-11-21T08:59:06.916411+00:00 shinyapps[3134369]: RJSONIO version: (none)
2020-11-21T08:59:06.916420+00:00 shinyapps[3134369]: htmltools version: 0.5.0
2020-11-21T08:59:06.916533+00:00 shinyapps[3134369]: Using pandoc: /opt/connect/ext/pandoc2
2020-11-21T08:59:07.092709+00:00 shinyapps[3134369]: Using jsonlite for JSON processing
2020-11-21T08:59:07.095670+00:00 shinyapps[3134369]: 
2020-11-21T08:59:07.095671+00:00 shinyapps[3134369]: Starting R with process ID: '27'
2020-11-21T08:59:07.364713+00:00 shinyapps[3134369]: 
2020-11-21T08:59:07.364715+00:00 shinyapps[3134369]: Listening on http://127.0.0.1:44835
2020-11-21T08:59:16.142120+00:00 shinyapps[3134369]: Error : nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found 
2020-11-21T08:59:16.131043+00:00 shinyapps[3134369]: Warning: Error in : nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found 
2020-11-21T08:59:16.142048+00:00 shinyapps[3134369]:   66: <Anonymous>
Problem with sql data and r shiny app
#2

The connection is not configured correctly. Have in mind that when the app is deployed to shinyapps.io no longer runs on your local environment, it runs on a remote server where there is no DSN configured for your SQL server so you have to provide all connection parameters explicitly.
For more details about this, check the documentation
https://docs.rstudio.com/shinyapps.io/applications.html#accessing-databases-with-odbc

#3

I am having trouble connecting SQL with my r shiny app. It loads fine in Rstudio, but return "disconnected from server. Reload" when publishing to shinyapps.io. Does anyone know what im doing wrong?

library(shiny)
##library(DT)
library(odbc)
library(DBI)


################# ui #####################

ui <- fluidPage(
  plotOutput("plot1", click = "plot_click"),
  verbatimTextOutput("info")
)

################# end ui #####################

################# server #####################

server <- function(input, output) {
  
  con <- dbConnect(odbc(),
                   Driver = "SQL Server",
                   Server = "DESKTOP-7129D7L",
                   Database = "Kim",
                   Trusted_Connection = "True")
  
  query <- dbGetQuery(con, "SELECT Date,FieldValue,FieldTicker
  FROM data WHERE FieldTicker='^VIX' order by date asc")
  query$Date <- as.Date(query$Date)
  
  output$plot1 <- renderPlot({
    plot(FieldValue ~ Date, query,type="l",ylab = "",xlab="")
  })
  
  output$info <- renderText({
    paste0("x=", input$plot_click$x, "\ny=", input$plot_click$y)
  })
}
################# end server #####################

shinyApp(ui,server)

log:
2020-11-21T09:19:50.661181+00:00 shinyapps[3134369]: Running on host: 9ac1790e1901
2020-11-21T09:19:50.666668+00:00 shinyapps[3134369]: Server version: 1.8.4.1-20
2020-11-21T09:19:50.666678+00:00 shinyapps[3134369]: LANG: en_US.UTF-8
2020-11-21T09:19:50.666707+00:00 shinyapps[3134369]: R version: 3.6.3
2020-11-21T09:19:50.666717+00:00 shinyapps[3134369]: shiny version: 1.5.0
2020-11-21T09:19:50.666744+00:00 shinyapps[3134369]: httpuv version: 1.5.4
2020-11-21T09:19:50.666763+00:00 shinyapps[3134369]: rmarkdown version: (none)
2020-11-21T09:19:50.666774+00:00 shinyapps[3134369]: jsonlite version: 1.7.0
2020-11-21T09:19:50.666763+00:00 shinyapps[3134369]: knitr version: (none)
2020-11-21T09:19:50.666956+00:00 shinyapps[3134369]: Using pandoc: /opt/connect/ext/pandoc2
2020-11-21T09:19:50.837294+00:00 shinyapps[3134369]: Using jsonlite for JSON processing
2020-11-21T09:19:50.840233+00:00 shinyapps[3134369]:
2020-11-21T09:19:50.840234+00:00 shinyapps[3134369]: Starting R with process ID: '388'
2020-11-21T09:19:50.666793+00:00 shinyapps[3134369]: RJSONIO version: (none)
2020-11-21T09:19:50.666844+00:00 shinyapps[3134369]: htmltools version: 0.5.0
2020-11-21T09:19:51.100620+00:00 shinyapps[3134369]:
2020-11-21T09:19:51.100622+00:00 shinyapps[3134369]: Listening on http://127.0.0.1:40583
2020-11-21T09:19:52.703502+00:00 shinyapps[3134369]: Warning: Error in : nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found
2020-11-21T09:19:52.713369+00:00 shinyapps[3134369]: 66:
2020-11-21T09:19:52.713438+00:00 shinyapps[3134369]: Error : nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found

#4

How is the connection configured on your local computer? Think of the shinyapps resources running in the cloud, how could they access the database with the information being given? There must be a configuration that's specific to your local machine since you are not specifying an IP address and no username/pswd. Shinyapps wouldn't be able to automatically detect the configuration specific to your computer for this connection, it looks like you manually configured the driver and called it "SQL Server" on your computer, but this wouldn't exist in shinyapps. At least I think that's what's wrong!

To fix this you would need to configure the connection in such a way that allows shinyapps to connect, for example if you provided an IP address and username and password, but connecting to a database remotely can be tricky, for instance you may need to whitelist the IP addresses that shinyapps uses in order for the connection to work.

#5

This topic was automatically closed 54 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.