aspose file tools*
The moose likes JDBC and the fly likes SQL value insert/update monitoring using JDBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "SQL value insert/update monitoring using JDBC" Watch "SQL value insert/update monitoring using JDBC" New topic
Author

SQL value insert/update monitoring using JDBC

Arun Suresh
Greenhorn

Joined: Sep 02, 2008
Posts: 18
Hi Guys

My app has to monitor certain columns of multiple tables in MySQL DB for anomalous value entry. If any wrong value is found to be entered, say score > 100, send a mail to Admin. Keeping scalability in mind, opted out of triggers. As of now thinking about running SELECT periodically in these columns and comparing with acceptable values and if failed populate a queue with warning message. From the queue the email app will pick up the messages and send email.
But was wondering if we can implement any event listeners in JDBC which can listen for anomalous entries in specific columns and then trigger the queue population. Other better options are also welcome. Please advise.

Thanks in Advance
K. Tsang
Bartender

Joined: Sep 13, 2007
Posts: 2594
    
    9

Hi Arun

Is your JDBC part of a web app or something? Or just pure for "monitoring" sake?

If it's part of an app (web or not), why not do the checking when users enter data (eg commit and rollback)? If it's more of a batch format, one question you need to answer is suppose you find some records faulty, what you going to do with them? Delete, move to error table etc? Will the original user or admin know why this record is faulty?


K. Tsang JavaRanch SCJP5 SCJD/OCM-JD OCPJP7 OCPWCD5 OCPBCD5
William P O'Sullivan
Ranch Hand

Joined: Mar 28, 2012
Posts: 859

Have a trigger automatically populate a "queue" table, then read that table to generate the emails.

The problem with your solution is that the invalid data has already been inserted.
The trigger can write the "queue" table and rollback the insert all in one operation.

WP
Martin Vajsar
Sheriff

Joined: Aug 22, 2010
Posts: 3611
    
  60

First of all, I'd try to prevent users from entering wrong values using check constraints as much as possible. Preventing the wrong data from entering the DB is much, much better than dealing with it retroactively. Even if you want to allow the anomalous values to enter the database for some reason, they could perhaps be put into separate tables and reviewed periodically by a dedicated process?

Secondly, why do you think that triggers will perform worse than repeated selects being run over the tables? I believe the opposite would probably be true. The statements to monitor for DB changes will have to somehow find just the new and updated rows (really not an easy task in concurrent environment), and then distinguish and skip rows that were already seen and reported. This kind of queries tends to be expensive and robust concurrent implementation can lead into locking issues (even on Oracle, in MySQL - which uses different concurrency mechanism - that would probably be harder still).

JDBC itself does not have tools to listen to database changes. I believe there are solutions which allow Java code to be notified of changes in the MySQL database, but firstly I believe these are trigger-based, and secondly this would imply a callback into your application, which would then probably have to re-read the data that was inserted and modified, which looks like a hell lot of an overhead to me.

I would go with triggers in some form. The queue table seems good for me, I'd just populate it from the triggers. For example, in one of our application (which runs on Oracle) we monitor changes to some 100+ tables. We have triggers over these tables which add records identifying the table, row and nature of the change (insert, update, delete) to a kind of queue table. Clients application then poll this table repeatedly. Once processed, the records are removed from the queue. We had to spend some time designing this to ensure no consistency and locking issues and the solution is highly specific to Oracle, but I believe something similar could work in MySQL.
Arun Suresh
Greenhorn

Joined: Sep 02, 2008
Posts: 18
Hi All,
Thanks for the replies. This is an independent monitoring app and cannot touch the original app that inserts/updates the data. And these values are mainly monitored not to rollback but for manual inspection by admin for any fraud. so they can be allowed to be inserted and Admin just needs to know. Later UI also need to be integrated to this.

From your advice I will look into the possibility of Triggers populating the MQ. So triggers will populate the MQ , from MQ we will check the transactions and send email and simultaneously populate another warnings table for usage of UI later.

Anyway, if any one knows any free/paid, hibernate/jdbc apps that already implements SQL value monitoring logic, it would help me from re inventing the wheel.

Thanks,
Arun




Bill Clar
Ranch Hand

Joined: Sep 21, 2006
Posts: 153

If this data is for inspection then why not run your query once, at the end of the work day? This allows you to give daily updates to the admin every morning.

I have several Crystal Reports that tell the supervisors about questionable data entered on the previous day. I can't disallow it's data entry, but the supervisors still need to know about it.



 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: SQL value insert/update monitoring using JDBC