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 2018-06-05 9:34 GMT+02:00 Lukas Eder <[email protected]>: > 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 <[email protected]>: > >> 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 <[email protected]>: >> >> 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 <[email protected]>: >> >>> 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_elements0 >>>> 01.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 [email protected]. >>> 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.co >> m/d/topic/jooq-user/LCN5zG57FBU/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> >> >> -- >> 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 [email protected]. >> For more options, visit https://groups.google.com/d/optout. >> > > -- 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 [email protected]. For more options, visit https://groups.google.com/d/optout.
