logging SQL expressions from within HSQLDB

Question

I use HSQLDB in my application. Now i need to log every single sql statement which was executed. I do not want to handle SQL-logging myself. Is there a standart way of doing this from within HSQLDB?

Answer 1

HSQLDB 2.2.x supports SQL logging. Suppose your database is named test and you connect using the JDBC URL jdbc:hsqldb:file:test

1. test.log is the data change log used internally by HSQLDB. It does not contain SELECT statements. It is created and deleted by HSQLDB. This is not what you are looking for.

2. test.sql.log is the log that contains all the SQL statements with time and session info, together with any arguments for prepared statements. This log is created if you use:

   SET DATABASE EVENT LOG SQL LEVEL 3

It contains entries such as these:

2012-02-08 22:19:36.484 DETAIL 4 CALL USER() 
2012-02-08 22:19:36.484 DETAIL 4 call database_version() 
2012-02-08 22:19:36.484 DETAIL 4 COMMIT 
2012-02-08 22:19:36.500 DETAIL 4 INSERT INTO Customer VALUES(0,'Laura','Steel','429 Seventh Av.','Dallas') 

You can use the hsqldb.sqllog=3 on the URL, e.g. jdbc:hsqldb:file:test;hsqldb.sqllog=3

3. test.sql.app.log is the log that contains entries for internal persistence operations. This is not related to the executed SQL statements.

See the Guide here and check the command and property syntax at the end of the chapter:

http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_monitoring_operation

Answer 2

Not HSQLDB specific, but if your client is using JDBC perhaps easiest way to log SQL statements is to use JDBC driver wrapper. There are plenty implementations to choose from.