I have a Java script that must instantly know when a row in a table is modified. How can I do this without constantly query-ing the database. Query-ing the database each second to see if there are changes is a bad idea for me.
The database is MS Sql Server 2008 but I don't think is important.
Pure JDBC does not offer a mechanism other than polling (ie. constantly querying). You should make sure your query will be executed really fast if you after all decide to go this path.
There might be some database specific solutions. I assume that MS SQL Server would be capable of notifying other processes somehow; you just might need to employ some triggers.
By the way, I've implemented similar mechanism based on triggers to constantly poll for database changes on a handful of tables, on Oracle. These polling queries did not even make it into an AWR report (a report which lists top queries by many various criteria). I've put a good amount of time into its design, though.
Thanks for the reply,
I am thinking to execute a .exe program from a trigger placed on the table that I want to query for changes. The exe would connect to a tcp port of my webapp and tell it that the table has changed.
It is unclear what type of "web app" you are referring to. Typically, web applications are for displaying a GUI and data to a human user and allowing them to execute computing operations. Everything here is based on the human users actions.
Can you explain what will happen in the web app when it receives "some type" of call from the database? What exactly is the web app going to do when it is notified of this data change?
My main issue is not how to alert the user's browser that he must show a popup window with the alarm. My issue is more about how to make the server-side code realise that there has been a table change in the database (without constant querying).
What happens if the data changes and there is no human user currently using the web application? Is there still going to be an alert?
I highly doubt that you will be able to send a server response without a client request in an HTTP environment (even when there are current users.)
If you create a server application that will sit and listen for communication from database and place this application on the machine hosting your web server, you could maintain state information here and when a user does initiate a request the server can refer to this application. Here you would not be constantly querying the database. You would need to create the component that calls the server application and create the server application and then connect your web application to the server application.
He's giving us the slip! Quick! Grab this tiny ad!