Available in versions: Dev (3.20) | Latest (3.19) | 3.18

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 NULL into the NOT NULL column n2.
  • 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!

The jOOQ Logo