Hi Lukas,
It's just an execution of a preparedStatement.
You can check it out in the attached files.
Le jeu. 1 avr. 2021 à 16:04, Lukas Eder <[email protected]> a écrit :
> Walid,
>
> The website doesn't offer specifying the interpreter dialect. What you see
> there as input dialect is the parse dialect.
>
> But I can only really hypothesise what you're doing :) Why don't you show
> me your code from your TestPostgreSQL class? If I can see the *exact* code
> you tried, then I will be able to help you. If I don't see that code, then
> I can only guess what it is you're trying to do.
>
> Thanks,
> Lukas
>
> On Thu, Apr 1, 2021 at 4:40 PM Walid CHAIB <[email protected]> wrote:
>
>> Hi Lukas,
>>
>> 1- When I specify the interpreter dialect, Jooq parses the oracle
>> functions like decode , nvl ... but it doesn't parse the oracle cast
>> functions like to_char(bigint) , to_number(varchar)
>> 2- When I don't specify the interpreter dialect, Jooq parse the oracle
>> cast functions to cast( ... as ...) but it doesn't parse the predefined
>> functions like decode as you can see in example :
>> SELECT DECODE(1,1 , 'One') From dual
>> Exception in thread "main" org.postgresql.util.PSQLException: ERREUR: la
>> fonction decode(bigint, bigint, character varying) n'existe pas
>> Indice : Aucune fonction ne correspond au nom donné et aux types
>> d'arguments.
>> Vous devez ajouter des conversions explicites de type.
>> Position : 8
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
>> at
>> org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
>> at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:473)
>> at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:393)
>> at
>> org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:164)
>> at
>> org.postgresql.jdbc.PgPreparedStatement.executeQuery(PgPreparedStatement.java:114)
>> at org.jooq.impl.ParsingStatement.executeQuery(ParsingStatement.java:339)
>> at com.tbs.db.TestPostgreSQL.main(TestPostgreSQL.java:31)
>>
>> Even with the website https://www.jooq.org/translate/, It's the same
>> behaviour ...
>>
>>
>> Le jeu. 1 avr. 2021 à 15:25, Lukas Eder <[email protected]> a écrit :
>>
>>> Hi Walid,
>>>
>>> That's surprising, there isn't a direct link between interpreting (DDL)
>>> and parsing. Can you show some example code that helps reproduce what
>>> you're seeing? Perhaps there's a more complex interaction in your setup
>>> that can't be easily derived from your description.
>>>
>>> Thanks,
>>> Lukas
>>>
>>> On Thu, Apr 1, 2021 at 3:11 PM Walid CHAIB <[email protected]>
>>> wrote:
>>>
>>>> When I don't specify the interpreter dialect Joqq doesn't parse the
>>>> oracle function like nvl and decode ...
>>>>
>>>> Le jeu. 1 avr. 2021 à 13:27, Lukas Eder <[email protected]> a
>>>> écrit :
>>>>
>>>>> Hi Walid,
>>>>>
>>>>> You can safely ignore the interpreter dialect. It has nothing to do
>>>>> with your current work. You only need to work with the parse dialect. But
>>>>> that is only used to resolve ambiguous syntax (e.g. select a = b in
>>>>> PostgreSQL vs SQL Server).
>>>>>
>>>>> Now, let's rewind and look at what you're actually trying to do?
>>>>>
>>>>> Cheers,
>>>>> Lukas
>>>>>
>>>>> On Thu, Apr 1, 2021 at 1:46 PM Walid CHAIB <[email protected]>
>>>>> wrote:
>>>>>
>>>>>> Hi Lukas,
>>>>>>
>>>>>> Is there a parameter I have to set it, so I can parse cast functions
>>>>>> ( to_char(),to_number() ...) to cast( x as varchar ) , cast (x as int)
>>>>>> because when specifying
>>>>>> .withInterpreterDialect(SQLDialect.POSTGRES_12), jooq doesn't parse it,
>>>>>> and
>>>>>> when i didn't specify the dialect interpreter i get problems with parsing
>>>>>> other functions like decode ?
>>>>>>
>>>>>> --
>>>>>> 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/CAOP4XxiF9P6aaD%2Bb6pwO8mPsUe_GpCxuytzs6b1Y5UFnHefFBw%40mail.gmail.com
>>>>>> <https://groups.google.com/d/msgid/jooq-user/CAOP4XxiF9P6aaD%2Bb6pwO8mPsUe_GpCxuytzs6b1Y5UFnHefFBw%40mail.gmail.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/CAB4ELO7%3DLVVA_ByhWW3oqMfF07GjUd156GcAfjmMUZYtsYVqow%40mail.gmail.com
>>>>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO7%3DLVVA_ByhWW3oqMfF07GjUd156GcAfjmMUZYtsYVqow%40mail.gmail.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/CAOP4Xxhr1aUvCbA6ewyfTYKSrd3rcMfJpHD56EcgJXdLY4qJmg%40mail.gmail.com
>>>> <https://groups.google.com/d/msgid/jooq-user/CAOP4Xxhr1aUvCbA6ewyfTYKSrd3rcMfJpHD56EcgJXdLY4qJmg%40mail.gmail.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/CAB4ELO4G%3Dt4L3YJ%2BoS%3DLUSEiWTrr7fzgOG6VgnERQM7LQ6TfDQ%40mail.gmail.com
>>> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO4G%3Dt4L3YJ%2BoS%3DLUSEiWTrr7fzgOG6VgnERQM7LQ6TfDQ%40mail.gmail.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/CAOP4Xxh3aaoiDsn1zu_040esYhAaEWxjuf%3D5AzFTu2MtHZCkhA%40mail.gmail.com
>> <https://groups.google.com/d/msgid/jooq-user/CAOP4Xxh3aaoiDsn1zu_040esYhAaEWxjuf%3D5AzFTu2MtHZCkhA%40mail.gmail.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/CAB4ELO6ROZAqkx0NQej-CZhPA_YdjexvN%3D6QCJ9Nwbq8ZBza7A%40mail.gmail.com
> <https://groups.google.com/d/msgid/jooq-user/CAB4ELO6ROZAqkx0NQej-CZhPA_YdjexvN%3D6QCJ9Nwbq8ZBza7A%40mail.gmail.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/CAOP4Xxgyp6PcEM%2Bm1nrtiijYa8g48huv0A-_jSr80aPWxH6HQQ%40mail.gmail.com.
package com.tbs.db;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class TestPostgreSQL {
public static void main(String[] args) throws Exception {
Connection connJooq = ConnectionHelper.getProxyConnection();
String sql = "SELECT DECODE(1,1 , 'One') From dual";
PreparedStatement pStmt = connJooq.prepareStatement(sql);
ResultSet res = pStmt.executeQuery();
int i = 1;
while (res.next()) {
String columnContent = res.getString(i++);
System.out.println(columnContent);
}
}
}package com.tbs.db;
import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Matcher;
import java.util.regex.Pattern;
import java.util.stream.Collectors;
import org.apache.commons.io.FileUtils;
import org.jooq.Configuration;
import org.jooq.DSLContext;
import org.jooq.ExecuteContext;
import org.jooq.ExecuteListener;
import org.jooq.ParseListener;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamCastMode;
import org.jooq.conf.ParamType;
import org.jooq.conf.ParseUnknownFunctions;
import org.jooq.conf.Settings;
import org.jooq.conf.TransformUnneededArithmeticExpressions;
import org.jooq.impl.DSL;
import org.jooq.impl.DefaultConfiguration;
import org.jooq.impl.DefaultExecuteListener;
import org.jooq.tools.jdbc.DefaultConnection;
import com.tbs.log.TbsLog;
public class ConnectionHelper {
private static Map<Pattern, String> patterns = new LinkedHashMap<Pattern, String>();
static {
List<String> list;
String regEx;
String type;
try {
File requestsFile = new File("dict.txt");
list = FileUtils.readLines(requestsFile);
} catch (IOException e) {
throw new RuntimeException(e);
}
for (String l : list) {
int colonIndex = l.indexOf("->");
regEx = l.substring(0, colonIndex);
type = l.substring(colonIndex + 2);
patterns.put(Pattern.compile(regEx), type);
}
}
public static Connection getConnection() throws Exception {
final String url = "jdbc:postgresql://10.253.1.40:5432/ia2301";
final String user = "IA2301";
final String password = "IA2301";
Connection connection = DriverManager.getConnection(url, user, password);
return connection;
}
public static Connection getJooqConnection() throws Exception {
Settings settings = new Settings()
.withParseDialect(SQLDialect.ORACLE)
.withParseUnknownFunctions(ParseUnknownFunctions.IGNORE)
.withTransformTableListsToAnsiJoin(true)
.withTransformUnneededArithmeticExpressions(TransformUnneededArithmeticExpressions.ALWAYS)
.withTransformRownum(true)
.withParamType(ParamType.NAMED)
.withParamCastMode(ParamCastMode.ALWAYS);
Configuration jooqConfig = new DefaultConfiguration()
.set(getConnection())
.set(settings)
.set(ParseListener.onParseField(ctx -> {
if (ctx.parseKeywordIf("SYSDATE")) {
return DSL.field("localtimestamp");
}
return null;
}));
DSLContext ctx = DSL.using(jooqConfig);
return ctx.parsingConnection();
}
public static Connection getProxyConnection() throws Exception {
Connection c2 = null;
final List<Pattern> list = patterns.keySet().stream().collect(Collectors.toList());
final List<String> types = patterns.values().stream().collect(Collectors.toList());
try (Connection c1 = getJooqConnection()) {
c2 = new DefaultConnection(c1) {
@Override
public PreparedStatement prepareStatement(String sql) throws SQLException {
for (int i = 0; i < list.size(); i++) {
String type = types.get(i);
Pattern p = list.get(i);
Matcher matcher = p.matcher(sql);
while (matcher.find()) {
if ("type1".equals(type))
sql = matcher
.replaceAll(matcher.group(2) + "=" + "to_char(" + matcher.group(3) + ") ");
if ("type2".equals(type))
sql = matcher
.replaceAll(matcher.group(3) + "=" + "to_char(" + matcher.group(2) + ") ");
}
}
return super.prepareStatement(sql);
}
};
}
return c2;
}
}
