We are trying to test parsing with meta lookups. However, we had two issues
:
- jOOQ was attempting to fetch metadata with every connection even when the
DSLContext is reused. Are we missing something in the settings?
- it seems that jOOQ isn't identifying tables/columns unless the
identifiers were quoted (examples in the java file provided). It would be
helpful if it could apply some transformations on the results of the lookup
(similar to identifier styling rules RenderQuotedNames RenderNameCase).
--------
jOOQ Version
org.jooq.pro-java-8:3.19.6
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
--
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/bb3c8f6e-f508-477b-ba9d-752190434618n%40googlegroups.com.
package com.tbs.db;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamType;
import org.jooq.conf.ParseWithMetaLookups;
import org.jooq.conf.RenderQuotedNames;
import org.jooq.conf.Settings;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;
public class ParseWithMetaLookupsExamples {
/*
CREATE TABLE public.person (
id_per numeric(12) NOT NULL DEFAULT NULL::numeric,
name_per varchar(100) NULL DEFAULT NULL::character varying,
fname_per varchar(40) NULL DEFAULT NULL::character varying,
CONSTRAINT pk_person PRIMARY KEY (id_per))
)
*/
public static void main_(String[] args) throws Exception {
try(Connection pgConn = DriverManager.getConnection("jdbc:postgresql://10.253.1.40:5432/aghanmi", "aghanmi", "aghanmi")){
Configuration jooqConf = new DefaultConfiguration()
.set(SQLDialect.POSTGRES_12)
.set(pgConn)
.set(new Settings()
.withParseDialect(SQLDialect.ORACLE)
.withParseWithMetaLookups(ParseWithMetaLookups.IGNORE_ON_FAILURE)
.withParamType(ParamType.INLINED)
);
DSLContext ctx = DSL.using(jooqConf);
for(int i = 0;i<5;i++) {
// with logging enabled, we'll see that a meta lookup ran before each query
try(Connection con = ctx.parsingConnection(); PreparedStatement pstmt = con.prepareStatement("select * from person where id_per=?")){
pstmt.setString(1, String.valueOf(i));
try(ResultSet rs = pstmt.executeQuery()){
rs.getFetchSize();
}
}
}
}
}
public static void main(String[] args) throws Exception {
try(Connection pgConn = DriverManager.getConnection("jdbc:postgresql://10.253.1.40:5432/aghanmi", "aghanmi", "aghanmi")){
Configuration jooqConf = new DefaultConfiguration()
.set(SQLDialect.POSTGRES_12)
.set(pgConn)
.set(new Settings()
.withParseDialect(SQLDialect.ORACLE)
.withParseWithMetaLookups(ParseWithMetaLookups.THROW_ON_FAILURE)
.withParamType(ParamType.INLINED)
);
DSLContext ctx = DSL.using(jooqConf);
// this will throw Unknown table identifier: [1:15] select * from [*]person where id_per=?
try(Connection con = ctx.parsingConnection(); PreparedStatement pstmt = con.prepareStatement("select * from person where id_per=?")){
pstmt.setLong(1, 1);
try(ResultSet rs = pstmt.executeQuery()){
rs.getFetchSize();
}
} catch(Exception e) {
e.printStackTrace();
}
// other examples :
// "select * from \"public\".\"person\" where \"id_per\"=?" -> works
// "select * from \"person\" where \"id_per\"=?" -> Unknown table identifier: [1:15] select * from [*]"person" where "id_per"=?, it would be recognized given .withParseSearchPath(new ParseSearchSchema().withSchema("public"))
// "select * from PERSON where ID_PER=?" -> Unknown table identifier: [1:15] select * from [*]PERSON where ID_PER=?
// "select * from public.person where id_per=?" -> Unknown table identifier: [1:15] select * from [*]public.person where id_per=?
}
}
}
