Binding null Boolean value at insert in Oracle 12c database

[Cati96]

Hello,

I coming with a problem regarding inserting a null Boolean value in Oracle 12c edition database. Everytime when I try to do this i get this error:

Exception in thread "main" org.jdbi.v3.core.statement.UnableToCreateStatementException: Exception while binding named parameter 'myBooleanParameter' [statement:"INSERT INTO <TABLE_NAME>(<column_names>) VALUES (<column_values>) ", rewritten:"INSERT INTO  ******************* "}]}]
	at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:75)
	at org.jdbi.v3.core.statement.ArgumentBinder.bind(ArgumentBinder.java:31)
	at org.jdbi.v3.core.statement.SqlStatement.internalExecute(SqlStatement.java:1551)
	at org.jdbi.v3.core.result.ResultProducers.lambda$getGeneratedKeys$4(ResultProducers.java:107)
	at org.jdbi.v3.core.result.ResultIterable.lambda$of$0(ResultIterable.java:53)
	at org.jdbi.v3.core.result.ResultIterable.one(ResultIterable.java:98)
	.............***My call hierarchy****........

Caused by: java.sql.SQLException: Invalid column type: 16
	at oracle.jdbc.driver.OracleStatement.getInternalType(OracleStatement.java:4031)
	at oracle.jdbc.driver.OraclePreparedStatement.setNullCritical(OraclePreparedStatement.java:4182)
	at oracle.jdbc.driver.OraclePreparedStatement.setNull(OraclePreparedStatement.java:4166)
	at oracle.jdbc.driver.OraclePreparedStatementWrapper.setNull(OraclePreparedStatementWrapper.java:1008)
	at org.jdbi.v3.core.argument.NullArgument.apply(NullArgument.java:48)
	at org.jdbi.v3.core.statement.ArgumentBinder.bindNamed(ArgumentBinder.java:73)
	... 8 more

From the research and debug made by me I think that the problem comes when it's detected that the Boolean value is null and the value set to PreparedStatement is something like this: statement.setNull(position, Types.Boolean) instead of statement.setNull(position, Types.NUMERIC) or statement.setNull(position, Types.TINYINT). I created a small test that confirms me this fact: if I'm setting null as Numeric or Tyniint, the insert it works.

As you see in my insert statement, somehow I'm generating this statement dynamically for multiple bean classes and at binding time I'm binding the bean once, not each bean's field separately so writing by myself stmt.bindNull(fieldName, Types.NUMERIC) it's not a solution for me.

You think you can fix this issue in a future release please? Thanks for your time.

[stevenschlansker]

This happens when Jdbi cannot determine the (erased) type that is bound. Unless you provide us type information (like bindByType or using SqlObject methods) all we know is that a null was bound but not what type it is. In that case, we assume the "untyped" null: https://github.com/jdbi/jdbi/blob/master/core/src/main/java/org/jdbi/v3/core/argument/Arguments.java#L115

I note that this isn't documented, which is one obvious improvement we could make.

I'll also note that "column type 16" is in fact Boolean: https://docs.oracle.com/javase/8/docs/api/java/sql/Types.html#BOOLEAN so the type did get determined correctly.

That likely means this is (another!) Oracle driver deficiency. And sure enough, someone else ran into the same issue: https://stackoverflow.com/questions/27132648/why-does-oracles-jdbc-driver-not-support-oracles-boolean-type

I'm constantly amazed at how much Oracle can charge for a totally broken database. Use Postgres! ;)

We can consider adding a workaround to the jdbi Oracle extension: https://github.com/jdbi/jdbi3-oracle12

[qualidafial]

If Oracle JDBC driver is treating booleans as bits, you could try overriding the argument used for booleans:

jdbi.registerArgument(new AbstractArgumentFactory<Boolean>(Types.TINYINT) {
    @Override
    protected Argument build(T value, ConfigRegistry config) {
        return (pos, stmt, ctx) -> stmt.setInt(pos, value ? 1 : 0)
    }
});

If that fixes your problem we could add that override to the jdbi3-oracle12 plugin. The oracle plugin is maintained here to keep it separate from our main builds. Oracle's maven repository was causing us troubles all the time so we split it out.