Hello,

is it possible to record (programatically) all executed SQL statements (incl. bind variables) for a transaction?

Why: we're currently developing a master data editor for a billing application. The editor works on a staging application (mimicking the production deployment). If the changes work in the staging environment, we want to replicate the changes to the production environment (using the recorded SQL). The database schema ist identical of course.

Is this possible? Or a good idea at all?

Could we used detached object instead (by saveOrUpdate() them to a different SessionFactory pointing to the production environment)? We would prefer to record the SQL though (-> no need for a production database link from the staging area....)

Thanx for any hint,
Dirk

The JDBC driver level might be the best place to record SQL.

This doesn't sound very portable. How about the detached object solution? Is something like this possible?

Regards,
Dirk

What about p6spy?

Never used it. I would prefer a (more or less) clean solution though.

What about using two sessionFactories? Is it possible to saveOrUpdate() detached objects from one source to another?

Regards,
Dirk

Why not using two session factories (one per database) and JTA for disttributed transaction. Your changes will committed only if both databases accept them...

We dont need (nor want) one 2PC transaction context. Changes to the first dataset are tested (could take some time), and after succesfull testing the change would be replicated to production.

What we need is the change-set (in SQL or Object-Form). The most elegant solution (well, for me) would be to store the changed objects from the staging run, and later apply them to the production environment. SQL would be even simpler (just pipe the diff to sqlplus later on).

We need incremental changes, avoiding the replacement of the whole production data set.

Cheers,
Dirk

You should reconsider that, using a altered version of p6spy will fit all your needs including session.connection() usage.


Well probably not. When saving a object and using the detached graph, Hibernate (by it's unsavzed-value) expect it to already be saved, thus an update on the second db will be done (using the same mapping).

In all cases, it looks very dangerous to perform such synchronisation at the SQL level...

Although you say your schemas are identical, you have no guarantee it will alway be the case at all time in production - you don't control your dba ;-) The same remark applies for the data... So you must be prepared!

Anyway, if you need it at that level, why don't you consider the synchronisation mechanism provided by the database itself ?

If this is not a viable alternative, I would implement such behavior using queued commands (the command pattern):
- before doing anything, my business layer would construct a command that represents the work to be performed;
- it then executes the command on the staging database;
- if it works, the command is queued for replication on the production system;
- then you have a queue manager that receives the commands, interpret them and applies the changes. It could do so by using Hibernate with a separate SessionFactory or plain JDBC if needed...

You then have the ability to catch any unexpected situations at a higher level than SQL statements.

This approach looks cleaner and safer to me.

(was my two cent contribution)

It is the simplest and portable way, if you have single client on database then implemet wrapper for jdbc driver to log updates.