0

I am trying to log a “change summary” from each INSERT/UPDATE MySQL/SQL Server query that executes in a Java program. For example, let’s say I have the following query:

Connection con = ...
PreparedStatement ps = con.prepareStatement(“INSERT INTO cars (color, brand) VALUES (?, ?)”);
ps.setString(1, “red”);
ps.setString(2, “toyota”);
ps.executeUpdate();

I want to build a “change set“ from this query so I know that one row was inserted into the cars table with the values color=red and brand=toyota.

Ideally, I would like MySQL/SQL Server to tell me this information as that would be the most accurate. I want to avoid using a Java SQL parser because I may have queries with “IF EXISTS BEGIN ELSE END”, in which case I would want to know what was the final query that was inserted/updated.

I only want to track INSERT/UPDATE queries. Is this possible?

DemCodeLines
  • 1,870
  • 8
  • 41
  • 60
  • 1
    Why don't you just log the statement and its parameters? They have all the information you need. – GMB Mar 17 '20 at 00:09
  • Easy enough to log from a method e.g. insertCar -> log relevant information – Scary Wombat Mar 17 '20 at 00:12
  • I’m looking for a central change instead of changing the 100+ places where queries are being executed. – DemCodeLines Mar 17 '20 at 00:16
  • How are you planning on using the change set? Is the MySQL/SQL Server because you want this to be implementation agnostic? Or are you after a nice solution and don't care which is used? – danblack Mar 17 '20 at 01:04
  • I need it for documentation and logging purposes. I mentioned both MySQL and SQL Server because I'm looking for something that follows the general specification and is compatible with each SQL implementation. – DemCodeLines Mar 17 '20 at 01:19
  • You can refer to https://stackoverflow.com/questions/27060563/how-to-enable-logging-for-sql-statements-when-using-jdbc – Andy Nghi Mar 18 '20 at 00:17

1 Answers1

0

What ORM do you use? If you don't use one, now could be the time to start - you give the impression that you have all these prepared statement scattered throughout the code, which is something that needs improving anyway.

Using something like Hibernate means you can just activate its logging and keep the query/parameter data. It might also make you focus your data later a bit more (if it's a bit haphazardly structured right now).

If you're not willing to switch to using an ORM consider creating your own class, perhaps called LoggingPreparedStatement, that is identical to normal PreparedStatement (subclass or wrapper of PreparedStatement such that it uses all the same method names etc so it's a drop in replacement) and logs whatever you want. Use find/replace across the code base to switch to using it.

As an alternative to doing it on the client side, you can get the database to do It. For SQL server it has change tracking, don't know what there is for MySQL but it'll be something proprietary. For something consistent, most DB have triggers that have some mechanism of identifying old and new data and you can stash this in a history table(s) to see what was changed and when. Triggers that keep history have a regularity to their code that means they can be programmatically generated from a list of the table columns and datatypes, so you can query the db for the column names (most db have some virtual tables that tell you info about the real tables) etc and generate your triggers in code and (re)apply them whenever schema changes. The advantage of using triggers is that they really easily identify the data that was changed. The disadvantage is that this is all they can see so if you want your trigger to know more you have to add that info to the table or the session so the trigger can access it - stuff like who ran the query, what the query was. If you're not willing to add useless columns to a table (and indeed, why should you) you can rename all your tables and provide a set of views that select from the new names and are named the old names. These new views can expose extra columns that your client side can update and the views themselves can have INSTEAD OF triggers that update the real tables. Doesn't help for selections though because deleting data doesn't need any data from the client, so the whole thing is a mess. If you were going that wholesale on your DB you'd just switch to using stored procedures for your data modifications and embark on a massive job to change your client side calls. An alternative that is also well leveraged for SQL Server is the CONTEXT_INFO variable, a 128byte variable block of binary data that lives for the life of your connection/session or it's newer upgrade SESSION_CONTEXT, a 256kb set of key value pairs. If you're building something at the client side that logs the user, query and parameter data and you're also building a trigger that logs the data change you could use these variables, programmatically set at the start of each data modification statement, to give your trigger something more involved than "what is the current time" to identify which triggered dataset relates to which query logged. Generating a guid in the client and passing it to the db in some globally readable way that means the database trigger can see it and log it in the history table , tying the client side log of the statement and parameters to the server side set of logged row changes

Caius Jard
  • 72,509
  • 5
  • 49
  • 80