How can i check if the table has been updated in the checkFunc and if there are any changes these should be passed on the the valueFunc to pull new data. I have two columns 'date_created' and 'date_updated' in the table in addition to the normal data columns.
In valueFunc,how can I ping the database with a cheap SQL statement to check if the table of interest have been updated: i.e.: return last row => if the last row has changed, valueFunc is ran or something like that.
You also could check for SELECT MAX(date_updated) FROM mytable.
In checkFunc, you need to put this kind of check on the database.
In valueFunc you put the code to retrieve the data. Could be all the date or a subset.
Many thanks for your prompt response, most appreciated. Yes i did try the select maximum of "date_updated". I am however clueless on how to use the maximum of the "date_updated" to indicate changes to the database table. My valueFunc already has code to retrieve data "get_data(con)". Right now it is retrieving the data regardless of if there are any changes.
The doc explains that the valueFunc is executed when checkFun value changes. https://shiny.rstudio.com/reference/shiny/latest/reactivePoll.html reactivePoll will keep the last value and check if it has changed or not. So you should make a function that returns the value of last updated date.
Thank you very much for the link to the documentation, i have managed to make it work with the following code:
MBA_Online <- reactivePoll(60000, session,
checkFunc = function(){
print("Entered Check")
Sys.time()
print(Sys.time())
# gets max date from database table to determine if data has been updated
max_date <- dbGetQuery(con, "SELECT UNIX_TIMESTAMP(date_updated) FROM onlineRetail;")
return(max_date)
},
valueFunc = function(){
print("Entered Value")
Sys.time()
print(Sys.time())
get_data(con)
# return(MBA_Online)
}
)
ord <- function(MBA_Online) {
print(data)
}
# ord(isolate(pollData()))
observe(ord(MBA_Online()))
I can actually see the events change when i insert new records into the database with the "Entered Value " print. My plots are also being updated and if no updates are made on table, the valueFunc is not called, only the checkFunc:
[1] "Entered Check"
[1] "2019-06-25 15:00:01 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:04:38 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:05:39 CAT"
**[1] "Entered Value"**
[1] "2019-06-25 15:09:24 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:10:03 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:11:04 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:12:04 CAT"
[1] "Entered Check"
[1] "2019-06-25 15:13:04 CAT"
I think you should change your get_data code to only query data above last updated date. You should be able to store last updated date by storing the last queried value.
If you need more specific help please provide a REPRoducible EXample (reprex), we can't give you any advice on how to modify get_data() since you are not showing the content of the function.
Many thanks, herewith a reprex for the issue at hand:
In Global.R the code is:
con <- dbConnect(MySQL(),
user = ('root'),
password = ('ruvimboML55AMG'),
host = ('localhost'),
dbname = ('healthcare_mining'))
onStop(function(){
dbDisconnect(con)
})
get_data <- function(con) {
MBA_Online <- dbGetQuery(con, "SELECT Transaction,Item, Date, Quantity, CustomerID, Amount FROM onlineRetail WHERE Amount > 0 and Quantity > 0;")
# return(MBA_Online)
}
MBA_Online <- get_data(con = con)
An then in the server, I have the following:
``` r
MBA_Online <- reactivePoll(60000, session,
checkFunc = function(){
print("Entered Check")
Sys.time()
print(Sys.time())
# get max date from database table to determine if data has been updated
max_date <- dbGetQuery(con, "SELECT UNIX_TIMESTAMP(date_updated) FROM onlineRetail;")
return(max_date)
},
valueFunc = function(){
print("Entered Value")
Sys.time()
print(Sys.time())
get_data(con)
# return(MBA_Online)
}
)
ord <- function(MBA_Online) {
print(data)
}
# ord(isolate(pollData()))
observe(ord(MBA_Online()))
# Create transactions data reactively for access to associative rule mining
transactions <- reactive({
transactionsDS <- new.env()
transactionsDS$data <- as(split(MBA_Online()$Item, MBA_Online()$Transaction),"transactions")
})
Regards,
Chris
I think I would save max_date somewhere then use it in the query in valueFunc when update is required. If this is the right value.... It depends on how you can identify the new data in your table.
If you need to build some SQL queries with parameters, this function could help
It depends on how you can identify the new data in your table.