Thanks a lot Lukas, you're a life saver :). We are currently bug hunting across our codebase for all the unresolved problems concerning the migration, I'll keep you updated for anything JOOQ related. Le mardi 19 mars 2024 à 16:20:24 UTC+1, Lukas Eder a écrit :
> I've split the issue in two. The NULLIF(?, '') solution is available from > the next 3.20.0-SNAPSHOT builds: > https://github.com/jOOQ/jOOQ/issues/16483 > > On Tue, Mar 19, 2024 at 3:16 PM Lukas Eder <[email protected]> wrote: > >> For the time being, as a workaround, you could use the experimental model >> API replacement SPI: >> >> https://www.jooq.org/doc/latest/manual/sql-building/model-api/model-api-replacement/ >> >> Just replace all Param<String> p types by nullif(p, inline("")) to get >> the above behaviour. Or, if this is just about execution, you could >> implement a JDBC proxy that overrides the behaviour of >> PreparedStatement.setString() >> >> On Tue, Mar 19, 2024 at 3:14 PM Lukas Eder <[email protected]> wrote: >> >>> I see, we currently don't do anything specific here. I've created a >>> feature request to add some flags for this purpose: >>> https://github.com/jOOQ/jOOQ/issues/16478 >>> >>> I can see two types of flags: >>> >>> 1. A flag governing how we interact with JDBC. This works only when >>> executing the query with jOOQ >>> 2. A flag changing all String ? markers into NULLIF(?, ''). This works >>> also when executing the query elsewhere >>> >>> Please let me know if you are aware of any other NULL related issues >>> when translating your SQL. I'm sure we can address a few more. >>> >>> Best Regards, >>> Lukas >>> >>> On Tue, Mar 19, 2024 at 11:03 AM Ahmed Ghanmi <[email protected]> >>> wrote: >>> >>>> Hello again and thank you for the reply. >>>> >>>> jOOQ Version >>>> >>>> org.jooq.pro-java-8:3.14.9 >>>> >>>> Database product and version >>>> >>>> Oracle Database 11g Release 11.2.0.4.0 - 64bit Production >>>> PostgreSQL 14.5 >>>> >>>> Java Version >>>> OpenJDK Runtime Environment Temurin jdk8u402-b06 >>>> >>>> OS Version >>>> OpenSUSE Leap 42.3 >>>> >>>> JDBC driver name and version (include name if unofficial driver) >>>> com.oracle.jdbc:12.2.0.1 >>>> org.postgresql:42.2.18 >>>> On Monday, March 18, 2024 at 3:57:11 PM UTC+1 [email protected] wrote: >>>> >>>>> Hi Ahmed, >>>>> >>>>> Thank you for your message. Can you please show an example with >>>>> details about what you mean, specifically? >>>>> >>>>> Best regards, >>>>> Lukas >>>>> >>>>> On Mon, Mar 18, 2024 at 3:54 PM Ahmed Ghanmi <[email protected]> >>>>> wrote: >>>>> >>>>>> Hello Lukas, >>>>>> >>>>>> In our Oracle->PG migration, we are using JOOQ's ParsingConnection to >>>>>> translate between dialects. >>>>>> >>>>>> Similar to https://github.com/jOOQ/jOOQ/issues/11757, we are facing >>>>>> issues with Oracle's NULL and empty string equivalence. >>>>>> The assumption is prelevant across the codebase as we have a lot of >>>>>> code that expects setString('', i) to be nullified. >>>>>> This is especially problematic when done on FK columns, as pg would >>>>>> complain for violating it (otherwise I wouldn't have found out about >>>>>> this >>>>>> haha). >>>>>> >>>>>> The first straight-forward solution on our side is to add empty >>>>>> checks everywhere, but I am trying to avoid this path for obvious >>>>>> reasons. >>>>>> I wonder if JOOQ could do something about this, or if there is an >>>>>> equivalent to ParseListener for binding that would allow more >>>>>> flexibility >>>>>> in that area. >>>>>> >>>>>> jooq version : 3.19.6 >>>>>> target sql dialect : POSTGRES_12 >>>>>> settings : >>>>>> Settings settings = new Settings() >>>>>> .withParseDialect(SQLDialect.ORACLE) >>>>>> .withParseUnknownFunctions(ParseUnknownFunctions.IGNORE) >>>>>> .withTransformTableListsToAnsiJoin(true) // transform (+) to left >>>>>> outer join >>>>>> >>>>>> .withTransformUnneededArithmeticExpressions(TransformUnneededArithmeticExpressions.ALWAYS) >>>>>> .withTransformRownum(Transformation.ALWAYS) >>>>>> .withParamType(ParamType.INLINED) >>>>>> .withParamCastMode(ParamCastMode.DEFAULT) >>>>>> .withRenderOptionalAsKeywordForFieldAliases(RenderOptionalKeyword.ON) >>>>>> .withRenderOptionalAsKeywordForTableAliases(RenderOptionalKeyword.ON) >>>>>> .withRenderQuotedNames(RenderQuotedNames.EXPLICIT_DEFAULT_UNQUOTED) >>>>>> .withRenderNameCase(RenderNameCase.UPPER) >>>>>> .withRenderCoalesceToEmptyStringInConcat(true); >>>>>> >>>>>> -- >>>>>> 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]. >>>>>> To view this discussion on the web visit >>>>>> https://groups.google.com/d/msgid/jooq-user/60920cfc-5e40-4cfe-abff-c8831817b304n%40googlegroups.com >>>>>> >>>>>> <https://groups.google.com/d/msgid/jooq-user/60920cfc-5e40-4cfe-abff-c8831817b304n%40googlegroups.com?utm_medium=email&utm_source=footer> >>>>>> . >>>>>> >>>>> -- >>>> 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]. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/jooq-user/42d2a791-9a72-4696-9385-f0fa3d35c03an%40googlegroups.com >>>> >>>> <https://groups.google.com/d/msgid/jooq-user/42d2a791-9a72-4696-9385-f0fa3d35c03an%40googlegroups.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>> -- 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]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/5c8e7a79-a61e-42b7-a690-04faa9e37548n%40googlegroups.com.
