Logging with SQLExceptionLoggerListener
Applies to ❌ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
In addition to the LoggerListener, which adds DEBUG logging to your development environments, there's an additional SQLExceptionLoggerListener, which is enabled by default to add additional exception context to your logs.
Many RDBMS are notoriously stingy with debug log information when something goes wrong. For example:
-- Depending on the dialect, use DECIMAL or NUMBER, instead CREATE TABLE t (n1 numeric(3) NOT NULL, n2 numeric(3) NOT NULL); INSERT INTO t (n1, n2) VALUES (123, null); INSERT INTO t (n1, n2) VALUES (1234, 123);
Clearly, this is wrong for 2 reasons:
- The first row tries to insert
NULLinto theNOT NULLcolumnn2. - The second row tries to insert a number with precision 4 into a
NUMERIC(3)column.
But what do RDBMS report?
-- Db2
SQL Error [23502]: Assignment of a NULL value to a NOT NULL column "TBSPACEID=2, TABLEID=4, COLNO=1" is not allowed..
SQLCODE=-407, SQLSTATE=23502, DRIVER=4.29.24
SQL Error [22003]: Overflow occurred during numeric data type conversion..
SQLCODE=-413, SQLSTATE=22003, DRIVER=4.29.24
-- MySQL:
SQL Error [1048] [23000]: Column 'n2' cannot be null
SQL Error [1264] [22001]: Data truncation: Out of range value for column 'n1' at row 1
-- Oracle:
SQL Error [1400] [23000]: ORA-01400: cannot insert NULL into ("TEST"."T"."N2")
SQL Error [1438] [22003]: ORA-01438: value larger than specified precision allowed for this column
-- PostgreSQL:
SQL Error [23502]: ERROR: null value in column "n2" of relation "t" violates not-null constraint
Detail: Failing row contains (123, null).
SQL Error [22003]: ERROR: numeric field overflow
Detail: A field with precision 3, scale 0 must round to an absolute value less than 10^3.
-- SQL Server:
SQL Error [515] [23000]: Cannot insert the value NULL into column 'n2', table 'test.dbo.t';
column does not allow nulls. INSERT fails.
SQL Error [8115] [S0008]: Arithmetic overflow error converting int to data type numeric.
While in this example, MySQL shows all the useful information, the other always omit the column name in at least one error message. Things get worse for multi row INSERT statements, where most RDBMS only report the error of one row, making things harder to debug in bulk insertion scenarios (e.g. when you import data).
This is how jOOQ's SQLExceptionLoggerListener will come in handy. Using this multi row INSERT statement:
create.insertInto(T)
.columns(T.N1, T.N2)
.values(new BigDecimal("123"), null)
.values(new BigDecimal("1234"), new BigDecimal("123"))
.execute();
The following logs will be produced:
NOT NULL column "public"."t"."n2" cannot have an explicit NULL value in row 1 with values: (123, null) Column "public"."t"."n1" of type numeric(3) cannot accept number of precision 4: 1234 in row 2 with values: (1234, 123)
The errors shouldn't be seen as formal validation, just as auxiliary debug information. Some statements cannot produce these errors, including UPDATE statements based on expressions, for example. Only INSERT statement and UPDATE statement where the value to be set is a bind value can be logged.
This logger relies on type information being available to query meta data, meaning that using the code generator will be a prerequisite.
If you wish to use your own logger (e.g. avoiding printing out sensitive data), you can deactivate jOOQ's logger using your custom settings and implement your own execute listener logger.
Feedback
Do you have any feedback about this page? We'd love to hear it!