Inline settings being ignored by jooq

[blewis...@gmail.com]

I am using the latest jOOQ 3.9.5.

I went through the 7 step tutorial as provided on the main website.

Configuration: mysql running on 6033, basic credentials (this is a dummy database that I setup specifically for this task)

DB Schema is as follows 

CREATE DATABASE `library`;

USE `library`;

CREATE TABLE `author` (
  `id` int NOT NULL,
  `first_name` varchar(255) DEFAULT NULL,
  `last_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

I generated the backing code using the default settings - and the program works

import org.jooq.*;

import org.jooq.conf.ParamType;

import org.jooq.conf.Settings;

import org.jooq.conf.StatementType;

import org.jooq.impl.DSL;

import static library.Tables.*;

import static org.jooq.impl.DSL.*;

import java.sql.*;

public class JooqTutorial {

    public static void main(String[] args) {

        String username = "root";

        String password = "root";

        String url = "jdbc:mysql://127.0.0.1:6033/library";

        try (Connection conn = DriverManager.getConnection(url, username, password)) {

            Settings settings = new Settings();

            settings.setStatementType(StatementType.STATIC_STATEMENT);

            settings.setParamType(ParamType.INLINED);

            DSLContext create = DSL.using(conn, SQLDialect.MYSQL, settings);

            SelectConditionStep<Record> fred = create.select().from(AUTHOR).where(

                    AUTHOR.FIRST_NAME.eq(inline("fred"))

            );

            Result<Record> result = fred.fetch();

            for (Record r : result) {

                Integer id = r.getValue(AUTHOR.ID);

                String firstName = r.getValue(AUTHOR.FIRST_NAME);

                String lastName = r.getValue(AUTHOR.LAST_NAME);

                System.out.printf("Id:%d %s, %s\n", id, lastName, firstName);

            }

        } catch (Exception e) {

            e.printStackTrace();

        }

    }

}

However, the code is not respecting the inline setting. Instead the following statement is being generated and sent to the db

select `library`.`author`.`id`, `library`.`author`.`first_name`, `library`.`author`.`last_name` from `library`.`author` where `library`.`author`.`first_name` = ?

I know I have technically redundant settings and calls to inline, but I figure at least one of them should have resulted in the query being sent 'inline' and not as a prepared statement. I am not sure why it isn't inlining the value of 'fred' as described in the documentation.

[Lukas Eder]

Hello,

How did you get this query string? I cannot reproduce this issue. The bind variable gets correctly inlined. All of your flags are correct API usage.

Thanks,

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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[blewis...@gmail.com]

I am passing my queries through proxy sql which caches the raw query strings its forwarding to mysql.

Proxysql is seeing this as the rendered query and is then ignoring the cache.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+...@googlegroups.com.

[blewis...@gmail.com]

Sorry for the double post. I've been investigating further after understanding some of the dynamics between these two pieces of software.  The issue isn't actually with the inlined sql (I was assuming the error was in executing that statement) but in some initial setup that is being done by some part of the tool chain (not sure where)- specifically 

SET character_set_results = NULL
SET autocommits=?

These (obviously) not possible to be cache. The rest of the query is working as intended. I am not sure at what layer of the toolchain these commands are added though.

Is this from jOOQ? 

Thanks for taking the time to look.

[Lukas Eder]

That's interesting - well I can guarantee to you that jOOQ keeps its promise when you set those flags, *especially* when you set them three times. In particular, DSL.inline() will never produce a bind variable in jOOQ.

But your JDBC driver might parse the SQL string again, and generate synthetic bind variables. Oracle database has a server-side setting called CURSOR_SHARING = FORCE, which will parse all literals and replace them by bind variables prior to generating an execution plan. Perhaps that's done in your JDBC driver as well?

What's your JDBC connection string and JDBC driver version?

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[Lukas Eder]

jOOQ doesn't run any such statements for you, it doesn't even manage your connection...

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.

[blewis...@gmail.com]

You are correct - these queries are not coming from jOOQ at all - the queries that were causing the issues were generated by JDBC during the setting up of the connection. 

jOOQ itself behaves correctly, the logs were confusing so I appreciate you taking a look into this.

For the record I have jdbc version

mysql-connector-java-8.0.7-dmr ( Revision: d33a3ca56530848bebdf75cf48cf8e68e0feef98 )

There isn't a whole lot of prior work done (at least published that I could find) on doing offline cacheing with java and proxysql unfortunately.

Thanks again.