getGeneratedKeys does not work with sequence

[sergeykad]

Driver version or jar name

6.4.0.jre8

SQL Server version

2012 Express

Table schema

ALTER TABLE [dbo].[results] ADD CONSTRAINT [DF_results_result_id] DEFAULT (NEXT VALUE FOR [results_result_id_seq]) FOR [result_id]
GO

Problem description

PreparedStatement.getGeneratedKeys() does not work with PK which uses sequence.
The generated key is always returned as zero.

[peterbae]

Hi @sergeykad, thanks for reporting this issue. I created a sequence, and a table with a primary key and tried to reproduce the issue, the the getGeneratedKeys method seems to work for me. Could you provide the dbo.results table schema, the sequence, and your repro code for this problem?

[sergeykad]

Hi @peterbae, thanks for the quick reply. It may take me some time to create an example, but according to the documentation, only IDENTITY type is supported.
https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys

[peterbae]

Hi @sergeykad, were you able to find the time to create a small repro case for this problem? Thanks in advance.

[sergeykad]

@peterbae Sorry, but I probably will be unable to do it before April.
Can you comment on the link that I provided earlier?

[peterbae]

Hi @sergeykad. In SQL Server, I believe you have to use the IDENTITY keyword to have a primary key that increments itself (which is also the case I tested myself). The page you linked explains that getGeneratedKeys only works against a table with an IDENTITY key, so that the method knows which column to keep track of (which is correct). Perhaps I'm missing something here - could you show me the table definition + the sequence that you created in SQL Server to increment your primary key column (without the IDENTITY keyword, I presume)? Thanks in advance.

[sergeykad]

Hi @peterbae , you can use the following SQL to create the table:

CREATE SEQUENCE [results_result_id_seq] START WITH 1;

CREATE TABLE [results](
[result_id] [bigint]  PRIMARY KEY,
[data] [nvarchar](max) NOT NULL);

ALTER TABLE [results] ADD  CONSTRAINT [DF_results_result_id]  DEFAULT (NEXT VALUE FOR 
 [results_result_id_seq]) FOR [result_id];

Run the following SQL to check that PK is generated correctly INSERT INTO results (data) VALUES ('my data');

[peterbae]

Hi @sergeykad, thanks a lot for your quick reply. I looked through the JDBC specs and you're correct in that our getGeneratedKeys method should return a ResultSet with a column for each generated value (including all the automatically generated values and not just from the IDENTITY row).

I actually recall looking into a similar issue reported before, and it turns out we already have an issue open that addresses a similar case (#245). We will keep the interaction with sequence in mind when we fix that problem, and will keep this issue open until all the related issues have been solved. Thanks for you help!

[mattiamoretta]

Hi all, having the same issue.. Do we roughly now when this fix will be tackled?

Thanks a lot!

Mattia

[ulvii]

Hi @mattiamoretta ,
Based on our initial investigations, the issues are happening because of the limitations on the server-side. We are looking into whether anything can be done in the driver as a workaround. We will update the thread with our findings, please stay tuned.

[rene-ye]

Hi @mattiamoretta, this issue isn't being worked on by the team. It requires a server side change regarding how generated keys are returned, and there's nothing we can do in the driver as of now. The team has explored workaround options such as adding OUTPUT clauses to user sql, but these workarounds were deemed unreliable and bad bandage fixes. As of now, the team will attempt to start a discussion with the SQL Server team to see what we can do from there.

[hfazai]

I have the same issue. getGeneratedKeys() returns null for column that has been set with NEXT VALUE of a sequence.
Does anyone found a solution or workarround for that ?

[gabrielgt3k]

Have same issue here... is there any forecast to fix?

[praskutti]

As a workaround, why dont you get the NEXT VALUE FOR Sequence as a first step and use that in the INSERT statement ? This might create some RACE conditions I believe, but if you control the INSERTs, then you can use this a temporary workaround.

[sergeykad]

@praskutti This indeed creates a race condition and you usually want to avoid these. My solution was to execute SELECT after INSERT, but it is not optimal from the performance perspective.
I am also skeptical about how temporal this solution will be. It will be soon 3 years since the issue was opened.

[sproket]

How is this issue still open after 4 years!? Can you guys just follow the spec already!