Error when trying to insert a record with a LocalDate field set to null into a MS SQL database (evaluating trial professional edition)

[Marcin Erdmann]

Hi,

Sorry if it's not the right support channel for this question but I could not find another one which seemed like a better fit.

I'm currently evaluating jooq's professional edition with MS SQL Server. I have a generated table class with the following field:

public final TableField<PassportsRecord, LocalDate> EXPIRY_DATE = createField("EXPIRY_DATE", org.jooq.impl.SQLDataType.LOCALDATE, this, "");

When I try to insert a record with that field set to null I get the following exception:

com.microsoft.sqlserver.jdbc.SQLServerException: Implicit conversion from data type varbinary to date is not allowed. Use the CONVERT function to run this query.

 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:232)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1672)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:460)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:405)

 at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7535)

 at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2438)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:208)

 at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:183)

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.execute(SQLServerPreparedStatement.java:387)

 at com.zaxxer.hikari.pool.ProxyPreparedStatement.execute(ProxyPreparedStatement.java:44)

 at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.execute(HikariProxyPreparedStatement.java)

 at org.jooq.tools.jdbc.DefaultPreparedStatement.execute(DefaultPreparedStatement.java:194)

 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:431)

 at org.jooq.impl.AbstractDMLQuery.execute(AbstractDMLQuery.java:335)

 at org.jooq.impl.AbstractQuery.execute(AbstractQuery.java:349)

Note that I can successfully insert records into that table as long as I use a non-null value for the aforementioned field.

I tried debugging the issue and ended up in org.jooq.impl.DefaultBinding#set(BindingSetStatementContext<U>) where binding of null values to prepared statements occurs but unfortunately the code handling it for SQLSERVER dialect is closed source.

Am I doing something wrong or is this an issue in jooq I uncovered? Please let me know if I can provide any more information that you might need to answer.

Thanks,

Marcin Erdmann

[Lukas Eder]

Hi Marcin,

Thank you very much for your message and for your detailed error report. This is most certainly a good channel for reporting these things, just like GitHub or Stack Overflow.

I believe that you may have run into this issue:

https://github.com/jOOQ/jOOQ/issues/2738

It has been fixed for jOOQ 3.10, but not for 3.9 yet. The upcoming patch version 3.9.2 will not include the fix yet, as it is rather delicate.

There are two workarounds:

- Don't use the java.time API yet (turn off the flag in the code generator)

- Implement your own custom Binding that fixes this problem

Let me know if you need any help with either approach.

I hope this helps,

Lukas

Confidentiality notice

This communication is from Energized Work, a private Ltd. company registered in England & Wales with Reg No: 06271339 having its registered office at KD Tower, Suite 8, Cotterells, Hemel Hempstead, Hertfordshire, HP1 1FW.

This electronic message contains information which may be privileged and confidential. The information is intended to be for the use of the individual(s) or entity named above. If you are not the intended recipient be aware that any disclosure, copying, distribution or use of the contents of this information is prohibited. If you have received this electronic message in error, please notify us by telephone or e-mail as above immediately.

--
You received this message because you are subscribed to the Google Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Marcin Erdmann]

Thanks for a quick reply, Lukas.

I'd rather use JSR-310 so I will see if I can implement a custom binding (as per https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings/) and ask questions here if I run into any problems.

Cheers,

Marcin

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[Marcin Erdmann]

A quick update for anybody who stumbles upon this thread. I managed to get null LocalDate to be saved in MSSQL by:

- not using javaTimeTypes generator configuration option

- writing a custom binding which extends org.jooq.impl.DefaultBinding like this: (bear in mind that the code is in Groovy):

import org.jooq.BindingSetStatementContext

import org.jooq.Converter

import org.jooq.impl.DefaultBinding

import java.sql.Date

import java.sql.SQLException

import java.time.LocalDate

class LocalDateBinding extends DefaultBinding<Date, LocalDate> {

    LocalDateBinding() {

        super(new LocalDateConverter())

    }

    @Override

    void set(BindingSetStatementContext<LocalDate> ctx) throws SQLException {

        ctx.statement().setDate(ctx.index(), ctx.convert(converter()).value())

    }

    static class LocalDateConverter implements Converter<Date, LocalDate> {

        @Override

        LocalDate from(Date date) {

            date?.toLocalDate()

        }

        @Override

        Date to(LocalDate date) {

            date ? Date.valueOf(date) : null

        }

        @Override

        Class<Date> fromType() {

            Date

        }

        @Override

        Class<LocalDate> toType() {

            LocalDate

        }

    }

}

Cheers,

Marcin