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.
package com.tbs.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import javax.naming.NamingException;
import org.jooq.Configuration;
import org.jooq.SQLDialect;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;
public class Main {
public static void main_ora(String[] args) throws SQLException, ClassNotFoundException, NamingException {
/*
CREATE TABLE test_user (
id_u NUMBER(12,0),
name_u VARCHAR2(20 BYTE),
CONSTRAINT "PK_TEST_USER" PRIMARY KEY (id_u));
*/
Class.forName("oracle.jdbc.driver.OracleDriver");
Connection con = DriverManager.getConnection("XX", "XX", "XX");
try(PreparedStatement pstmt = con.prepareStatement("insert into test_user(id_u, name_u) values (?, ?)")){
pstmt.setLong(1, 1l);
pstmt.setString(2, "");
pstmt.executeUpdate();
pstmt.close();
// select * from test_user where id=1 --> 1,null
}
}
public static void main_pg(String[] args) throws SQLException, ClassNotFoundException, NamingException {
/*
CREATE TABLE test_user (
id_u numeric(12),
name_u varchar(20),
CONSTRAINT pk_test_user PRIMARY KEY (id_u));
*/
Class.forName("org.postgresql.Driver");
Connection con = DriverManager.getConnection("XX", "XX", "XX");
Configuration jooqConfig = new DefaultConfiguration()
.set(SQLDialect.POSTGRES_12)
.set(con)
.set(new Settings().withParseDialect(SQLDialect.ORACLE));
Connection jooqcon = DSL.using(jooqConfig).parsingConnection();
try(PreparedStatement pstmt = jooqcon.prepareStatement("insert into test_user(id_u, name_u) values (?, ?)")){
pstmt.setLong(1, 1l);
pstmt.setString(2, "");
pstmt.executeUpdate();
pstmt.close();
// select * from test_user where id=1 --> 1,''
}
}
}
