Representation of (Oracle) BINARY_DOUBLE in jOOQ

[Christoph Lucas]

Hello,

We are running an Oracle Database (version 12.2). In our application, we need to store double values. The Oracle type equivalent seems to be BINARY_DOUBLE [1]. During the build phase, we do not have the Oracle database available. So we use the same schema with the BINARY_DOUBLE column on an hsql DB and use this as basis for the code generation. In the generated code this results in a column with type SQLDataType.DOUBLE, which seems to be correct.

The problem arises when storing very small values (< E-130). When binding the value in the prepared statement, the method OraclePreparedStatement.setDouble is used, instead of OraclePreparedStatement.setBinaryDouble. That method converts the Double to a Number, which is only supported down to E-130 [2]. Therefore, an underflow exception is thrown.

What is the correct way to store a double value on an Oracle DB using jOOQ? Any help would be greatly appreciated!

Best regards,

Christoph

[1] https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#g195556

[2] https://community.oracle.com/thread/460586

[Christoph Lucas]

What I forgot to mention: We are currently using an Oracle DB, but we need the application to run on different types of databases (eg MySQL, MSSQL, etc). Using Oracle specific code would therefore not be an option.

[Lukas Eder]

Hi Christoph,

Thanks for your  message. That's a very interesting problem - I wasn't aware of any difference between PreparedStatement.setDouble() and OraclePreparedStatement.setBinaryDouble().

In order to support that binding for Oracle, but leave other database integrations untouched, a good workaround right now for you would be to implement a custom data type binding [1], which handles and abstracts over the dialect specific JDBC API usage. You would then attach that binding to all relevant columns using the code generator. That way, you can easily keep the PreparedStatement.setDouble() call on MySQL, MSSQL, etc., but have an Oracle specific implementation for Oracle.

Let me know if you need any further assistance with this approach.

In the meantime, I'll investigate if jOOQ's current behaviour is really wrong and needs to be fixed.

Thanks,

Lukas

[1]: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

--
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.

[Christoph Lucas]

Hello Lukas,

Thank you very much for your response (and sorry for my late reply). We tried it the way you described and it works perfectly. Thanks a lot!

Could you let us know (probably best here in this thread) when you have any findings regarding jOOQ's current behaviour?

Best regards,

Christoph

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/LCN5zG57FBU/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

Hi Christoph,

Yes, of course. I'll update this discussion with any findings, once I will investigate this problem further. This week, jOOQ 3.11 will be released, so I'm expecting to be able to look into this next week.

Glad to hear that the workaround worked for you!

Thanks,

Lukas

2018-06-04 16:46 GMT+02:00 Christoph Lucas <christo...@ergon.ch>:

Hello Lukas,

Thank you very much for your response (and sorry for my late reply). We tried it the way you described and it works perfectly. Thanks a lot!

Could you let us know (probably best here in this thread) when you have any findings regarding jOOQ's current behaviour?

Best regards,

Christoph

Am 14.05.2018 um 12:33 schrieb Lukas Eder <lukas...@gmail.com>:

Hi Christoph,

Thanks for your  message. That's a very interesting problem - I wasn't aware of any difference between PreparedStatement.setDouble() and OraclePreparedStatement.setBinaryDouble().

In order to support that binding for Oracle, but leave other database integrations untouched, a good workaround right now for you would be to implement a custom data type binding [1], which handles and abstracts over the dialect specific JDBC API usage. You would then attach that binding to all relevant columns using the code generator. That way, you can easily keep the PreparedStatement.setDouble() call on MySQL, MSSQL, etc., but have an Oracle specific implementation for Oracle.

Let me know if you need any further assistance with this approach.

In the meantime, I'll investigate if jOOQ's current behaviour is really wrong and needs to be fixed.

Thanks,

Lukas

[1]: https://www.jooq.org/doc/latest/manual/code-generation/custom-data-type-bindings

2018-05-14 12:18 GMT+02:00 Christoph Lucas <christoph.lucas@gmail.com>:

What I forgot to mention: We are currently using an Oracle DB, but we need the application to run on different types of databases (eg MySQL, MSSQL, etc). Using Oracle specific code would therefore not be an option.

Am Montag, 14. Mai 2018 12:12:11 UTC+2 schrieb Christoph Lucas:

Hello,

We are running an Oracle Database (version 12.2). In our application, we need to store double values. The Oracle type equivalent seems to be BINARY_DOUBLE [1]. During the build phase, we do not have the Oracle database available. So we use the same schema with the BINARY_DOUBLE column on an hsql DB and use this as basis for the code generation. In the generated code this results in a column with type SQLDataType.DOUBLE, which seems to be correct.

The problem arises when storing very small values (< E-130). When binding the value in the prepared statement, the method OraclePreparedStatement.setDouble is used, instead of OraclePreparedStatement.setBinaryDouble. That method converts the Double to a Number, which is only supported down to E-130 [2]. Therefore, an underflow exception is thrown.

What is the correct way to store a double value on an Oracle DB using jOOQ? Any help would be greatly appreciated!

Best regards,

Christoph

[1] https://docs.oracle.com/database/121/SQLRF/sql_elements001.htm#g195556

[2] https://community.oracle.com/thread/460586

-- 
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.

-- 

You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/LCN5zG57FBU/unsubscribe.

To unsubscribe from this group and all its topics, send an email to jooq-user+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

[Lukas Eder]

I've had a brief look at this problem today. I can reproduce this for BINARY_DOUBLE bindings. Indeed, calling OraclePreparedStatement.setBinaryDouble() solved the problem. ResultSet.getDouble() doesn't seem to have the problem for Double.MIN_VALUE and Double.MAX_VALUE.

The integration test also shows that the issue appears as well for procedure calls, table types (TABLE OF BINARY_DOUBLE), and object types that have BINARY_DOUBLE attributes. More details in the issue below:

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

The fix will ship with jOOQ 3.11 this week.

Thanks again for your report.

Lukas