R Shiny - mysql connection doesn't work from server.R (works from other files)

Question

I have a Shiny app in RStudio, which works with data I retrieve from a mysql database.

The connection to mysql and the queries are in a file outside the app, so I can create dataframes.

But when I copy the connection to mysql and the sql queries into server.R of the Shiny app, the app skips the queries and gives an error that the dataframe does not exist (Error : object 'tickets' not found).

So the mysql query works from outside server.R, but not from server.R. What am I doing wrong?

Here's a sample of my server.R and ui.R :

server.R

library(shiny) library(RMySQL) library(ggplot2) #library(ggiraph) library(lubridate) ##Connect to Redmine db con <- dbConnect(MySQL(), user = '#', password = '#', host = '#', dbname='#') tickets<-dbGetQuery(con, "Select * from table") issues_speed_unique<-unique(na.omit(dbGetQuery(con,"Select * from table2"))) dbDisconnect (con) some aggregations.... shinyServer( function(input,output){ output$tickets_week<-renderPlot( ggplot(data = subset(tickets,

ui.R

library(shiny) library(ggplot2) #library(ggiraph) #library(htmltools) library(lubridate) shinyUI(fluidPage(

if you add print(tickets) under the query and run the app, does the data frame print to console? — Pete900
– Pete900, Commented Dec 9, 2016 at 15:21
@Pete900 sorry, late reply. nope, doesn't show. it seems it skips directly to what's inside shinyServer( function(input,output){ . — adlisval
– adlisval, Commented Dec 12, 2016 at 11:56
Will it work when you launch the app by browser compared to just running in RStudio? — Pete900
– Pete900, Commented Dec 12, 2016 at 13:56
@Pete900 thanks for the hint ´print(tickets)´ . It helped with debugging. Please see the solution I found. — adlisval
– adlisval, Commented Dec 12, 2016 at 16:34

adlisval · Accepted Answer · 2016-12-12 16:31:29Z

The problem was that ui.R was run before server.R, and of course the data was missing (because the data was queried in server.R- hence the error).

The solution was to put the code, both from server.R and from ui.R, in one file, called app.R. So, in app.R I wrote the code in the following order:

Load packages (library(packagename)) code
Make connection and queries code
the server code
the ui code
finish code with: shinyApp(ui = ui, server = server)

More about Shiny in a single file - app.R file: http://shiny.rstudio.com/articles/single-file.html

Collectives™ on Stack Overflow

R Shiny - mysql connection doesn't work from server.R (works from other files)

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related