MySQL Dialect Error on Increment

[Maximilian Manndorff]

Hello,

I ran into an issue with MySQL today. I'm trying to increment several columns at once using jooq and have the following statement.

 DSL.using(connection, SQLDialect.MYSQL)
        .update(TABLE)
        .set(
                DSL.row(
                        TABLE.COL1,
                        TABLE.COL2,
                        TABLE.COL3,
                        TABLE.COL4,
                        TABLE.COL5),
                DSL.row(
                        SUBSCRIPTION.COL1.add(1),
                        SUBSCRIPTION.COL2.add(1),
                        DSL.val(0),
                        DSL.val(new Timestamp(System.currentTimeMillis())),
                        DSL.val("message")))
        .where(TABLE.KEY1.eq(var.getKey1()))
        .and(TABLE.KEY2.eq(var.getKey2()))
        .and(TABLE.KEY3.eq(var.getKey3()))
        .execute();

I've changed the column and table names for the purposes of this example, but everything is the same. This compiles and runs just fine, but I'm getting the following runtime exception

org.jooq.exception.DataAccessException: SQL [update `DB`.`TABLE` set (`COL1`, `COL2`, `COL3`, `COL4`, `COL5`) = ((`DB`.`TABLE`.`COL1` + ?), (`DB`.`TABLE`.`COL2` + ?), ?, ?, ?) where (`DB`.`TABLE`.`KEY1` = ? and `DB`.`TABLE`.`KEY2` = ? and `DB`.`TABLE`.`KEY3` = ?)]; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(`COL1`, `COL2`, `COL3' at line 1 

When I try to run a test query myself I can reproduce this behavior. MySQL does not seem to like the parentheses around the set columns. I.e. this will reproduce the error

 update `DB`.`TABLE` set (`COL1`) = (`DB`.`TABLE`.`COL1` + 1)

While this works just fine

update `DB`.`TABLE` set `COL1` = (`DB`.`TABLE`.`COL1` + 1)

I'm a little confused why MySQL is complaining, I do not think the additional parentheses should matter.

 

[Bill O'Neil]

I haven't seen that syntax used before with MySQL. Have you tried the other overloading?

.set(TABLE.COL1, SUBSCRIPTION.COL1.add(1))

.set(TABLE.COL2, SUBSCRIPTION.COL2.add(1))
...

I personally find this syntax a little easier to read. See if this works for you.

[Lukas Eder]

Hi Maximilian,

Thanks for your message. jOOQ supports the UPDATE .. ROW = ROW syntax for those databases that also have native support. This is documented in the API as well, see:

https://www.jooq.org/javadoc/latest/org/jooq/UpdateSetFirstStep.html#set-org.jooq.Row5-org.jooq.Row5-

It contains the following support annotation:

@Support(value={DB2,H2,HANA,HSQLDB,INGRES,ORACLE,POSTGRES})

Unfortunately, MySQL doesn't have native support for row value expression updates like the other databases (even if row value expressions are supported in predicates). There's a pending feature request to emulate this syntax for other databases by transforming the row expressions into individual single-column assignments:

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

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

[Maximilian Manndorff]

Thank you both and sorry for the delayed response. I’ve verified that the syntax Bill suggested works with MySQL. I must have missed the section in the linked documentation. While adding this syntax for MySQL would be nice, I do agree that it is probably low priority compared to other feature requests.

---

From: jooq...@googlegroups.com <jooq...@googlegroups.com> on behalf of Lukas Eder <lukas...@gmail.com>
Sent: Monday, December 11, 2017 12:12:22 AM
To: jooq...@googlegroups.com
Subject: Re: MySQL Dialect Error on Increment

 

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/dZaZBaFHqbI/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.