Hey Steve, thanks for the response. I've seen you everywhere on this forum and have followed your advice countless times which actually helped me complete this project in the first place!
you dont say which flavour SQL nor which contrib node you are using
I'm using node-red-contrib-mssql-plus for my SQL queries from a Microsoft SQL server.
That said, how does the data get into that table? Could the thing that puts data in the table fire an event (like MQTT)?
Data enters the table via an antiquated python program made years ago by an ex-employee. My IT team hasn't been able to push any updates to that program since. Otherwise I agree, an MQTT publish alongside the database insert would be ideal. If that's truly the best solution then I may have to contract this to the original coder.
If you are using mySQL or MSSQL (and others) you could run some app/script ON TRIGGER to signal to node-red something has changed
I'll look into opportunities using the ON TRIGGER functionality. We've recently lost our database expert so that will be uncharted territories for me, but it sounds like a good alternative. I've done a quick google search on a "MSSQL TRIGGER ON INSERT into Node Red" and all the solutions I've seen require middleware to accomplish this. I also didn't find any nodes that can accomplish this unfortunately. I was hoping to keep the coding between the SQL server and Node red, potentially using MQTT to pass info since that's already set up.
Lastly, is it REALLY necessary to check every 0.25s? that is asking a bit too much IMO (if you get an outage, that will end up spamming and potentially crashing node-red) - surely 5 sec or 2 secs would be hardly noticeable?
The data updates are based off of users scanning bar codes, often in quick succession. We've had instances where several entries and been inserted in less than 1s, causing some to be missed. The quick fix was to up the poll frequency.
I'm positive it's possible to grab additional recent entries less frequently and perform the tasks on each one, but not without overhauling a fair bit of coding. That led me to this post, wondering how terrible is my current strategy and if it's worth it or even vital to put the time into updating my strategies before it's replicated onto future projects.
So far Node-RED has been able to handle the frequency. The MSSQL nodes themselves HAVE crashed a couple times where I had to re-initiate the connection.
From what I can tell, the ideal solution seems to lie between Node-RED and the SQL server which means it's unfortunately no longer a question for this forum unless you have other ideas.
Thank you again
