SQL update statement in SQLServer

[tod...@googlemail.com]

Hi,
at the end of a very fast-pacing year, I end up using JOOQ with SQLServer for a customer project. Of course, I bought a license in order to enable Lukas to buy gifts for his beloved ones :-)
But first of all, I've to thank Lukas for those awesome piece of software: it has made my day-to-day work -and because I'm a freelancer, my whole life- so much easier than before. I remember with  horror back to the days with ... Hibernate only :-)  Since 6 month I'm in the lucky position to do my coding without JavaScript/Typescript frontend framework, REST API and JPA. Just Vaadin and JOOQ ... and thats great ! But I digress.....

I'm currious why the UPDATE statement generated by JOOQ are so much more complicated with SQLServer than with using PostgreSQL ?

A simple UPDATE abc WHERE xyz = 123 becomes to:

DECLARE @RESULT TABLE
    (
        [id]                INT,
        [employee_username] VARCHAR(100),
        [leistung_id]       INT,
        [date]              DATE,
        [duration]          NUMERIC(5, 2),
        [created_at] datetime2(3),
        [created_by] VARCHAR(100)
    );
UPDATE
    [timetrack]
SET
    [timetrack].[employee_username] = 'XXX',
    [timetrack].[leistung_id] = 9,
    [timetrack].[date] = CAST('2021-12-21' AS DATE),
    [timetrack].[duration] = 7,
    [timetrack].[created_at] = CAST('2021-12-22 08:33:38.43859' AS datetime2),
    [timetrack].[created_by] = 'XXX' output [inserted].[id],
    [inserted].[employee_username],
    [inserted].[leistung_id],
    [inserted].[date],
    [inserted].[duration],
    [inserted].[created_at],
    [inserted].[created_by]
INTO
    @result
WHERE
    [timetrack].[id] = 1;
MERGE
INTO
    @result [r]
USING
    (
    (   SELECT
            [timetrack].[id],
            [timetrack].[employee_username] [alias_99784968],
            [timetrack].[leistung_id] [alias_90450706],
            [timetrack].[date] [alias_29558215],
            [timetrack].[duration] [alias_56189729],
            [timetrack].[created_at] [alias_31341003],
            [timetrack].[created_by] [alias_31342119]
        FROM
            [timetrack])) [s]
ON [r].[id] = [s].[id]
WHEN MATCHED
THEN
UPDATE
SET
    [employee_username] = [s].[alias_99784968],
    [leistung_id] = [s].[alias_90450706],
    [date] = [s].[alias_29558215],
    [duration] = [s].[alias_56189729],
    [created_at] = [s].[alias_31341003],
    [created_by] = [s].[alias_31342119];
SELECT
    [id],
    [employee_username],
    [leistung_id],
    [date],
    [duration],
    [created_at],
    [created_by]
FROM
    @result [r];

Can you explain those strategy or link me to an explanation ?

Merry christmas and happy new year 🎄

kind regards

Dominik

[Lukas Eder]

Hi Dominik,

Thanks for your nice words. I'm glad to hear that jOOQ helps you be so effective.

You're probably not really running "a simple UPDATE abc WHERE xyz = 123". You're probably asking jOOQ to return a few things that cannot be returned otherwise in SQL Server - unless you know a better syntax? If you're thinking of the OUTPUT clause, that's useful, but won't return all trigger generated data, such as PostgreSQL's UPDATE .. RETURNING. You can turn off this particular syntax by specifying Settings.fetchTriggerValuesAfterSQLServerOutput = false, or you avoid the RETURNING syntax itself. This includes setting Settings.returnAllOnUpdatableRecord = false (which is the default)

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+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/fd45a5c8-c0ef-4281-9601-5a41b558b979n%40googlegroups.com.

[Rob Sargent]

> On Dec 21, 2021, at 11:55 PM, 'tod...@googlemail.com' via jOOQ User Group <jooq...@googlegroups.com> wrote:
>
> Hi,
> at the end of a very fast-pacing year, I end up using JOOQ with SQLServer for a customer project. Of course, I bought a license in order to enable Lukas to buy gifts for his beloved ones :-)
> But first of all, I've to thank Lukas for those awesome piece of software: it has made my day-to-day work -and because I'm a freelancer, my whole life- so much easier than before. I remember with horror back to the days with ... Hibernate only :-) Since 6 month I'm in the lucky position to do my coding without JavaScript/Typescript frontend framework, REST API and JPA. Just Vaadin and JOOQ ... and thats great ! But I digress.....

Please, digress. Have you, in essence gone to a two-tiered model? I’m interesting in how you integrated jOOQ with Vaadin. Off-list if you prefer.

[Simon Martinelli]

I heavily use Vaadin with jOOQ

Here is a small example how this looks like:

https://github.com/72services/vaadin-jooq-demo

[Rob Sargent]

> On Dec 29, 2021, at 5:23 AM, Simon Martinelli <simon.ma...@gmail.com> wrote:
>
> 

> I heavily use Vaadin with jOOQ
>
> Here is a small example how this looks like:
> https://github.com/72services/vaadin-jooq-demo
>

Ah. I see you incorporate Spring Boot. I was hoping you had sidestepped that as well.

[Simon Martinelli]

Why should I?

Spring Boot brings a lot of convenience and Vaadin is perfectly integrated. 

--
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/425B5F3E-8F46-4ECB-8665-106ED94B5126%40gmail.com.

[Rob Sargent]

On Dec 29, 2021, at 9:09 AM, Simon Martinelli <simon.ma...@gmail.com> wrote:

Why should I?

Spring Boot brings a lot of convenience and Vaadin is perfectly integrated. 

I find Spring-things very opinionated (like me![1]) and was hoping for a very minimal stack.  I’ll have to look at your example and thanks for that.

[1] I’m definitely not a fan of convention over configuration.  The rules/assumption elude me.

On Wed, Dec 29, 2021 at 6:03 PM Rob Sargent <robjs...@gmail.com> wrote:

> On Dec 29, 2021, at 5:23 AM, Simon Martinelli <simon.ma...@gmail.com> wrote:
>
> 
> I heavily use Vaadin with jOOQ
>
> Here is a small example how this looks like:
> https://github.com/72services/vaadin-jooq-demo
>



Ah. I see you incorporate Spring Boot. I was hoping you had sidestepped that as well.


--
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/425B5F3E-8F46-4ECB-8665-106ED94B5126%40gmail.com.

--
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/CAGfT6e%2BFQb%2BiEr8ihMg3QC5EBANet3j2k%3DG9z9eFpzu8XMBtow%40mail.gmail.com.

[Simon Martinelli]

In that case, you may want to check out https://vaadin.com/learn/tutorials/embedded-jetty-server-in-vaadin-flow

To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/3FBAE383-B11A-4A2D-A6E9-1BB53AA8C2F6%40gmail.com.

[Rob Sargent]

On Dec 29, 2021, at 10:25 AM, Simon Martinelli <simon.ma...@gmail.com> wrote:

In that case, you may want to check out https://vaadin.com/learn/tutorials/embedded-jetty-server-in-vaadin-flow

Simon,
Thank you, very much.

(Lukas, 

Apologies for hijacking this thread.  This should be the end of the hijack.  Happy 2022)

[Lukas Eder]

(Lukas, 

Apologies for hijacking this thread.  This should be the end of the hijack.  Happy 2022)

Always happy to see jOOQ users connect!

Cheers,

Lukas