I’m currently running into an issue with running database queries in a gateway script that is not making any sense to me. We have a script that gets all employees, look at their time records, and emails them if they have gone too many days without logging their time.
I am getting this bizarre issue where it seems to grab the wrong data when running in the gateway, but running the queries directly in MySQL Workbench gives the results you would expect.
Do gateway scripts do any caching or does Ignition do any caching to try to limit database queries or something?
Here’s the problematic part of my script -
allEmployees = system.db.runQuery("SELECT idx FROM listusers WHERE customerId = 0 and inactive = 0 and idx NOT IN (-1, 2307) AND recieveEmail = 1") for employee in allEmployees: idx = employee # get last working day lastDayQuery = 'SELECT DATE_FORMAT(dateEntered,"%Y-%m-%d") FROM listtimeentry WHERE dateEntered < now() AND individualId = ' + str(idx) + ' order by dateEntered desc LIMIT 1' logger.info("lastDayQuery: " + str(lastDayQuery)) lastDayEntered = system.db.runScalarQuery(lastDayQuery)
If it matters, dateEnterd is a
Date datatype, though in my testing that has never seemed to be an issue when comparing it to
Just today, when this ran for a certain employee it said he last logged time 2019-12-16. But according to our database backup from 1 am this morning, running the same query gave a result of 2019-12-19. I checked with this employee and he did not delete his entries before this script ran. Does anyone have any possible idea what could be causing this?