JOOQ not using table aliases with columns in forUpdate().Of()

[Oleg]

Hi 

I have select like this in Jooq 3.5.0:

...

.forUpdate()
.of(Tables.CLIENT.as("clients").NUM., Tables.ACCOUNT.as("accounts").ID)
.skipLocked()
.fetch();

The generated SQL will end with 

FOR UPDATE OF "NUM", "ID" SKIP LOCKED 

instead of 

FOR UPDATE OF "clients"."NUM", "accounts"."ID" SKIP LOCKED 


Is it possible to make JOOQ use table alias in the for update statement?

[Lukas Eder]

Hello,

Indeed, only the unqualified column name is being rendered to the SQL output, no matter what kind of Field expression you're putting there. There must be some historic reason for this, related to a SQL dialect that doesn't support qualification in the FOR UPDATE .. OF clause - or that has only limited support for qualification. I'll need to investigate this, and I've registered an issue:

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

What dialect are you using? Oracle?

As a workaround, you might be able to write:

DSLContext ctx = DSL.using(configuration);

...

.of(DSL.field(ctx.render(Tables.CLIENT.as("clients").NUM)),

 DSL.field(ctx.render(Tables.CLIENT.as("clients").ID.toString())))

That would generate the SQL string first, before embedding it in the AST

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

[Oleg]

Yes, it is Oracle.

By the way, is there a way to override JOOQ behavior for such situations in the future?

I expected that DSL.inline() will help and output as is, but Jooq tries to wrap it with "" anyway - it would be great if I could fix it until bug or problem is fixed.

воскресенье, 22 марта 2015 г., 19:24:04 UTC+3 пользователь Oleg написал:

[Lukas Eder]

2015-03-23 15:06 GMT+01:00 Oleg <olkuz...@gmail.com>:

Yes, it is Oracle.

By the way, is there a way to override JOOQ behavior for such situations in the future?

In this particular case, the "dequalifying" of the column reference is hard-wired in jOOQ's internals (in org.jooq.impl.SelectQueryImpl) and cannot be overridden, although as I mentioned in my previous E-Mail, DSL.field(String) should work, as that's how you can inject "plain SQL" into jOOQ ASTs (Abstract Syntax Trees). Here's some additional info on "plain SQL" from the manual:

http://www.jooq.org/doc/latest/manual/sql-building/plain-sql/

I expected that DSL.inline() will help and output as is, but Jooq tries to wrap it with "" anyway

DSL.inline() is used for inline bind variables / constants. If they're String constants, they have to be wrapped with apostrophes.

 

- it would be great if I could fix it until bug or problem is fixed.

We'll certainly look into this pretty soon for jOOQ 3.6.0 and probably also for 3.5.4. Do note that we deliver the sources and grant the right to modification to our customers when you purchase the jOOQ Professional Edition license as some of our customers need to be able to fix issues very quickly themselves. Let me know if you are interested in more details about our commercial licensing terms.

Looking forward to hearing from you again,

Lukas

[Oleg Kuznetsov]

Hm, I actually tried .field(), .inline(), .value() - none of them worked out.

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/WDAsX3ySbeQ/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

I'm sorry for the confusion. I should have tested my own suggestion before mailing it. Indeed, it doesn't work the way I suggested. Here's another workaround that works for me:

DSLContext ctx = DSL.using(connection, SQLDialect.ORACLE, new Settings().withRenderNameStyle(RenderNameStyle.AS_IS);

ctx.select(...)

   .from(Tables.CLIENT.as("clients"))

   .forUpdate()

   .of(DSL.field(ctx.render(Tables.CLIENT.as("clients").NUM)),

       DSL.field(ctx.render(Tables.CLIENT.as("clients").ID)))

   .fetch();

This RenderNameStyle setting will prevent the double quotes from being added around table / column identifiers. More information about Settings can be found here:

http://www.jooq.org/doc/latest/manual/sql-building/dsl-context/custom-settings/

I've increased the priority of issue #4151. The current implementation is really wrong - at least for Oracle. Thanks for reporting this!

Let me know if the above helps

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+...@googlegroups.com.

[Oleg Kuznetsov]

Thank you for quick reply!

[Lukas Eder]

From our integration tests, I don't see any reason why we shouldn't allow for fully qualified columns in the FOR UPDATE OF clause. Obviously, there may be corner cases, but users can still use column references that don't use full qualification explicitly.

We'll implement this for jOOQ 3.6, without backporting - to prevent any unforeseen issues that may arise from a hidden corner case.