I need to write a jdbc compliant driver wrapper whose purpose is to log all SQL statements that modify data. What is the easiest way to differentiate those statements that modify data from those that only read data? I guess that only way is to parse SQL code on the fly, any libraries that can do that?
views:
46answers:
3
+1
A:
I think a pretty good start is looking for queries starting with INSERT, UPDATE or DELETE. Make sure to trim leading whitespace before testing the strings.
If you want to include schema altering statements, include commands such as CREATE, ALTER, DROP, and TRUNCATE.
The specifics of the above approach will depend on the database you are using. E.g., there may be batch commands in one string, separated by a semicolon. You will need to parse the string to look for instances such as this.
RedFilter
2010-09-14 13:52:09
Don't forget MERGE on modern SQL DBMS.
Jonathan Leffler
2010-09-14 13:53:47
In some RDBMS systems you can have modifying statements that do not being with insert, update, or delete. (For example, in SQL Server any of these could be embedded within a cte, and so start with a "WITH" statement.) It's a complex problem, and findint a third-party tool for your system might be the safest bet.
Philip Kelley
2010-09-14 13:55:45
+1
A:
You could probably find some full blown sql parsers such as this one, but if your wrapper will intercept only single statements then you might (not enough detail) consider SELECT statements as read only and everything else as statements that modify data.
Unreason
2010-09-14 13:57:19
"Many people have attempted to write a full SQL grammar with parser generate tool and failed." they say. Seems to be more complex than I initially thought..
Dan
2010-09-15 01:56:31
@Dan, yes, especially due to extensions to the standard. For example you can find grammar for SQL here http://savage.net.au/SQL/ and it is in BNF for which you can find parsers and do nice things; however if you aim for full conformance then you are most likely talking about full conformance with some existing RDBMS (or even worse, multiple RDBMSes), and that has many more versions and is much more undocumented :)
Unreason
2010-09-15 16:06:09
A:
If this is not a general purpose wrapper I would just log every call to the executeXXX() methods, except calls to executeQuery(), and then just call the appropiate method in client code.
If you want it to be general purpose and would like to avoid parsing SQL, you can investigate the getUpdateCount()method, and the return values of the executeXXX() methods. That would imply logging after the statement was executed, and I do not think it would be 100% correct.
gpeche
2010-09-14 15:39:09