Postgres param binding marker

Magnus Persson

unread,

Mar 20, 2021, 12:01:50 PM3/20/21

to jOOQ User Group

I'm using the jooq sql parser with the intention of outputting sql and bind values. Unfortunatly the sql coming out of jooq has either named

":param" or indexed by "?". How would I go about having jooq output sequenced index markers such as "$1", "$2" etc?

Lukas Eder

unread,

Mar 22, 2021, 8:26:23 AM3/22/21

to jOOQ User Group

Hi Magnus,

You can set Settings.paramType = ParamType.NAMED (this would produce :1, :2 if you don't actually provide any named parameters), and then Settings.renderNamedParamPrefix = "$" to replace the default prefix ":" by "$".

I hope this helps,

Lukas

On Sat, Mar 20, 2021 at 5:01 PM Magnus Persson <magnus.e...@gmail.com> wrote:

I'm using the jooq sql parser with the intention of outputting sql and bind values. Unfortunatly the sql coming out of jooq has either named
":param" or indexed by "?". How would I go about having jooq output sequenced index markers such as "$1", "$2" etc?

--
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/8405b074-cb9e-4be5-9f2d-f8bc74d2fde8n%40googlegroups.com.

Magnus Persson

unread,

Mar 22, 2021, 3:48:03 PM3/22/21

to jOOQ User Group

It almost gets me all the way :)

The input sql has named parameters, as in "SELECT c1, c2 FROM foo WHERE bar = :baz OR quz = :qux". I would like to have that rendered as "SELECT c1, c2 FROM foo WHERE bar = $1 OR quz = $2". The intention is to use jooq as an sql parser and executing it with r2dbc-postgres.

Lukas Eder

unread,

Mar 22, 2021, 4:08:21 PM3/22/21

to jooq...@googlegroups.com

Search "(?<!:):\\w+"

Replace "?"

😉

But it's a great hint. We should supprt that OOTB. At the time, I was trying to get the R2DBC folks not to diverge too far from JDBC and accept ? as well, but I failed.

To unsubscribe from this group and stop receiving emails from it, send an email to jooq-user+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com.

Lukas Eder

unread,

Mar 22, 2021, 4:17:42 PM3/22/21

to jooq...@googlegroups.com

Here we go. As I said, I think we should offer this out of the box, and integration test it: https://github.com/jOOQ/jOOQ/issues/11700. It's a rather low hanging fruit, much lower than executing jOOQ queries on R2DBC. I'll look into this later this week.

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fed9b2bc-6dfd-48fc-bb32-1408021f2b73n%40googlegroups.com.

Lukas Eder

unread,

Mar 22, 2021, 5:22:37 PM3/22/21

to jooq...@googlegroups.com

This really doesn't seem too hard! Here's a quick draft leveraging the translation capabilities from the jOOQ 3.15.0-SNAPSHOT Professional Edition to translate Teradata QUALIFY to PostgreSQL (I marked the interesting stuff in yellow):

package org.jooq.r2dbc;

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

import java.util.Map;
import java.util.TreeMap;

import org.jooq.Configuration;
import org.jooq.Param;
import org.jooq.Query;
import org.jooq.SQLDialect;
import org.jooq.conf.ParamType;
import org.jooq.impl.DefaultConfiguration;

import org.reactivestreams.Publisher;
import org.reactivestreams.Subscriber;
import org.reactivestreams.Subscription;

import io.r2dbc.postgresql.PostgresqlConnectionConfiguration;
import io.r2dbc.postgresql.PostgresqlConnectionFactory;
import io.r2dbc.spi.Batch;
import io.r2dbc.spi.Connection;
import io.r2dbc.spi.ConnectionFactory;
import io.r2dbc.spi.ConnectionFactoryMetadata;
import io.r2dbc.spi.ConnectionMetadata;
import io.r2dbc.spi.IsolationLevel;
import io.r2dbc.spi.Result;
import io.r2dbc.spi.Statement;
import io.r2dbc.spi.TransactionDefinition;
import io.r2dbc.spi.ValidationDepth;
import reactor.core.publisher.Flux;

public class X {

public static void main(String[] args) {
PostgresqlConnectionConfiguration config = PostgresqlConnectionConfiguration.builder()
.username("postgres")
.password("test")
.host("localhost")
.database("postgres")
.build();
ConnectionFactory f = new PostgresqlConnectionFactory(config);

Flux.from(new X(f).factory().create())
.flatMap(connection -> connection
.createStatement(
"select row_number() over w, table_schema, table_name "
+ "from information_schema.tables "
+ "window w as (order by table_schema, table_name) "
+ "qualify row_number() over w between :1 and :2 "
+ "order by 1, 2 ")
.bind(1, 6)
.bind(2, 10)
.execute()
)
.flatMap(it -> it.map((a, b) -> String.format("%1$5s: %2$s.%3$s", a.get(0), a.get(1), a.get(2))))
.collectList()
.block()
.stream()
.forEach(System.out::println);
}

final Configuration configuration;
final ConnectionFactory delegate;

public X(ConnectionFactory delegate) {
this.configuration = new DefaultConfiguration()
.set(SQLDialect.POSTGRES);
this.configuration
.settings()
.withRenderNamedParamPrefix("$")
.withParamType(ParamType.NAMED);
this.delegate = delegate;
}

ConnectionFactory factory() {
return new ConnectionFactory() {

@Override
public ConnectionFactoryMetadata getMetadata() {
return () -> "jOOQ";
}

@Override
public Publisher<? extends Connection> create() {
Publisher<? extends Connection> connection = delegate.create();

return subscriber -> {
connection.subscribe(new Subscriber<Connection>() {
@Override
public void onSubscribe(Subscription s) {
subscriber.onSubscribe(s);
}

@Override
public void onNext(Connection connection) {
subscriber.onNext(new Connection() {
@Override
public Publisher<Void> beginTransaction() {
return connection.beginTransaction();
}

@Override
public Publisher<Void> beginTransaction(TransactionDefinition definition) {
return connection.beginTransaction(definition);
}

@Override
public Publisher<Void> close() {
return connection.close();
}

@Override
public Publisher<Void> commitTransaction() {
return connection.commitTransaction();
}

@Override
public Publisher<Void> createSavepoint(String name) {
return connection.createSavepoint(name);
}

@Override
public boolean isAutoCommit() {
return connection.isAutoCommit();
}

@Override
public ConnectionMetadata getMetadata() {
return connection.getMetadata();
}

@Override
public IsolationLevel getTransactionIsolationLevel() {
return connection.getTransactionIsolationLevel();
}

@Override
public Publisher<Void> releaseSavepoint(String name) {
return connection.releaseSavepoint(name);
}

@Override
public Publisher<Void> rollbackTransaction() {
return connection.rollbackTransaction();
}

@Override
public Publisher<Void> rollbackTransactionToSavepoint(String name) {
return connection.rollbackTransactionToSavepoint(name);
}

@Override
public Publisher<Void> setAutoCommit(boolean autoCommit) {
return connection.setAutoCommit(autoCommit);
}

@Override
public Publisher<Void> setTransactionIsolationLevel(IsolationLevel isolationLevel) {
return connection.setTransactionIsolationLevel(isolationLevel);
}

@Override
public Publisher<Boolean> validate(ValidationDepth depth) {
return connection.validate(depth);
}

@Override
public Batch createBatch() {
// TODO
throw new UnsupportedOperationException();
}

@Override
public Statement createStatement(String input) {
return new Statement() {
Map<Integer, Param<?>> params = new TreeMap<>();

@Override
public Statement add() {
// TODO
throw new UnsupportedOperationException();
}

@Override
public Statement bind(int index, Object value) {
params.put(index, val(value));
return this;
}

@Override
public Statement bind(String name, Object value) {
// TODO
throw new UnsupportedOperationException();
}

@Override
public Statement bindNull(int index, Class<?> type) {
params.put(index, val(null, type));
return this;
}

@Override
public Statement bindNull(String name, Class<?> type) {
// TODO
throw new UnsupportedOperationException();
}

@Override
public Publisher<? extends Result> execute() {
Query query = configuration.dsl().parser().parseQuery(input, params.values().toArray());
Statement statement = connection.createStatement(query.getSQL());

int i = 0;
for (Param<?> o : query.getParams().values())
if (!o.isInline())
if (o.getValue() == null)
statement.bindNull(i++, o.getType());
else
statement.bind(i++, o.getValue());

return statement.execute();
}
};
}
});
}

@Override
public void onError(Throwable t) {
subscriber.onError(t);
}

@Override
public void onComplete() {
subscriber.onComplete();
}
});
};
}
};
}
}

The output being:

6:information_schema.administrable_role_authorizations
7:information_schema.applicable_roles
8:information_schema.attributes
9:information_schema.character_sets
10:information_schema.check_constraint_routine_usage

Lukas Eder

unread,

Mar 23, 2021, 5:06:17 PM3/23/21

to jOOQ User Group

An update on R2DBC matters. This was really much simpler than I thought. The DSLContext.parsingConnectionFactory() is now implemented for jOOQ 3.15.0-SNAPSHOT for all jOOQ editions, including the jOOQ Open Source Edition. It works just like the DSLContext.parsingConnection(), and profits from the recently implemented translation cache https://github.com/jOOQ/jOOQ/issues/8334 and batch support https://github.com/jOOQ/jOOQ/issues/5757. Named parameters are still a TODO. So, that's exciting news already for your particular use-case.

I've decided not to add a new module for this. R2DBC is a slim SPI that only depends on reactive-streams, which we already have as a dependency. I'm undecided whether R2DBC will be an optional dependency, but I think it's worth having it directly in the core jOOQ module. Now, the big task is to offer full support for R2DBC, which has been very frequently requested and ignored for a long time here:

https://github.com/jOOQ/jOOQ/issues/6298

I'm tracking R2DBC specific stuff in a new issue, as #6298 has mixed a lot of concerns and unrelated discussions:

https://github.com/jOOQ/jOOQ/issues/11700

So, this will be a top priority for jOOQ 3.15 (along with the planned parser / translator improvements and 4 new dialects). #11700 aims for full support of all the jOOQ goodies on top of R2DBC, including:

- A parsing / translating ConnectionFactory

- A MockConnectionFactory (I'm sure this will be quite useful for R2DBC in general, given that it is much harder to test / interact with than JDBC)

- A DiagnosticsConnectionFactory (not top prio)

- Execution of jOOQ ResultQuery statements as Publisher<? extends Record> (already exists, but currently blocking and not passing the TCK)

- Execution of jOOQ RowCountQuery statements as Publisher<? extends Integer> (already exists, but currently blocking and not passing the TCK)

- RowCountQuery statements that support RETURNING clauses will also offer a Publisher<? extends Record> result

- Execution of Batch API as Publisher<? extends Integer>

- Execution of UpdatableRecord calls and DAO calls as Publisher<? extends X> (to be investigated)

Not all of this might fit in 3.15, but the most important parts definitely will. With 3.15 jOOQ will finally be reactive!

Your early feedback will be highly appreciated, of course!

Cheers,

Lukas

Reply all

Reply to author

Forward