Hi Dominik, Vaadin and jOOQ is also my preferred stack :-)
I'm not a jOOQ expert but I assume that you are using a UpdatableRecordImpl and call the method store(). This method will does either insert or update and will return the values that may be generated by the database (for example with a trigger). I assume that the generated statement is the best way to do this with SQL Server. But let's see what Lukas will answer ;-) Merry christimas Simon On Wednesday, December 22, 2021 at 8:55:18 AM UTC+1 [email protected] 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..... > 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 > -- 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 [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/jooq-user/1b9dad57-ea40-4cd8-91a7-64f5169df4c5n%40googlegroups.com.
