Monitor a SQL Response Table for Change

We have another vendor on a project where we have to send requests via a SQL table and then monitor their response table for status of the requests. I’d like to set this up in the most efficient and manageable way possible. I could “brute force” it by having a script that issues the requests (based on a PLC trigger) and then sits in a “loop”, periodically “waking up” to test for the response. This methodology is usually not the best form nor most efficient.

The insert into the request table will return the Unique ID of the newly inserted record. The response record will contain that Request ID as part of its response record. So what I would be looking for is a new record added that has that ID and then examine the status filed to find out if the request was successful or if there was an error.

The request record is easy enough to insert. Is there more efficient method than my “brute force” method to monitor for the response data?


In a loosely-coupled system like this, I don’t think you have an alternative to setting a value and polling for a response. In any case polling a database works perfectly well unless you need a response time in the milliseconds range.

I would put this is in a gateway Tag Change script. On the PLC trigger issue your write, then enter a loop and poll for a response, timing out if it takes too long. When you pause, remember to use something like system.util.invokeLater() as per this post.