SQL Translation : Oracle null-empty string equivalence

[Ahmed Ghanmi]

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);

[Lukas Eder]

Hi Ahmed,

Thank you for your message. Can you please show an example with details about what you mean, specifically?

Best regards,

Lukas

--
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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/60920cfc-5e40-4cfe-abff-c8831817b304n%40googlegroups.com.

[Ahmed Ghanmi]

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

[Lukas Eder]

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

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/42d2a791-9a72-4696-9385-f0fa3d35c03an%40googlegroups.com.

[Lukas Eder]

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()

[Lukas Eder]

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

[ahmed mustapha ghanmi]

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.