0

My question is when or in what instances gets the shiny app refreshed once a SQL RODBC connection is established (or alternatively also other types of connection like RJDBC etc.)?

I connected shiny app to a database using RODBC package:

library(RODBC)
connection <- odbcConnect(dsn, uid = "", pwd = "")

Subsequently I create an object inside R to manipulate further the data:

object<-sqlQuery(connection, "SELECT ALL * FROM <the table>;")

I keep the app running online so others can see the data visualisation. Is the shiny app refreshed from the SQL source every time someone opens the app in the browser or refreshes the browser page? Or is it a bit more complex and I need to build in an observer in the data input pathway when reading data in from SQL server?

(Note: the SQL source is updated weekly, therefore, I cannot test this at the moment.)

Patrik_P
  • 3,066
  • 3
  • 22
  • 39

1 Answers1

0

I can only guess based on the code extract, but as I see it your code will never update based on reactivity. I don't see any reactivity in your code, which will automatically update the contents of object. However, it depends where your code is. If your code is inside the server object it should refresh for each session (this is user/refresh/...). However, if something reactive changes like a pressed button it will not update automatically

However you should be able to try it easily 

 object<-{cat("test");sqlQuery(connection, "SELECT ALL * FROM <the table>;")}
CAFEBABE
  • 3,983
  • 1
  • 19
  • 38
  • You made a good point @CAFEBABE the read in from the database stands before server function. So your conclusion would be the data read in is not updating as it is now I guess. The R Console output of object<-{cat("test");sqlQuery(connection, "SELECT ALL * FROM Abonnenten_GIS_NOZ;")} is 'test' and it reads the data Properly into 'object' properly. What does this tell me? The only reactivity I am looking into is when the database data change, so should also the visualisation take these changes into account. – Patrik_P Feb 11 '16 at 10:21
  • I think the easiest solution to solve your problem is the following. You turn object into a reactive value. Then you add a button refresh to you UI and observe whether the button has been pushed. If so you update the reactive object. – CAFEBABE Feb 11 '16 at 11:35
  • Thnx for the answer @CAFEBABE, yes I have thought of that as well. Can this be achieved without refresh button, simply creation of a reactive object that would be updated by opening a new session or by refreshing the page. If so can you suggest the solution? Then my Q will be answered. – Patrik_P Feb 11 '16 at 11:53
  • Then you don't even need a reactive object I think. It should be sufficient to assign `object` within the server. Just keep for testing purposes the `cat` and hit refresh. – CAFEBABE Feb 11 '16 at 12:08
  • Implemented ur suggestion, putting the object into server function and refreshing the session reconnects to the database. Now just to work out the load time using aql queries but that's another story :) thank you! @CAFEBABE – Patrik_P Feb 11 '16 at 13:12
  • I ran by chance into http://stackoverflow.com/questions/24680246/how-to-trigger-a-data-refresh-in-shiny this might provide you the missing puzzle pieces – CAFEBABE Feb 11 '16 at 22:11
  • reactivePoll seems to be a very relevant solution, i will try it out, thanks! – Patrik_P Feb 12 '16 at 09:39