Cannot fetch generated keys through JDBC on SQL Data Warehouse

Question

I have a table like this in my Azure SQL Data Warehouse database:

CREATE TABLE t_identity (
  id INTEGER IDENTITY (1, 1) NOT NULL,
  val INTEGER
)

Now, using JDBC, I want to insert a row and fetch the generated identity value. This would work in SQL Server and most other databases:

try (Connection c = DriverManager.getConnection(url, properties);
    Statement s = c.createStatement()) {

    s.executeUpdate("insert into t_identity (val) values (1)",
        Statement.RETURN_GENERATED_KEYS);
    try (ResultSet rs = s.getGeneratedKeys()) {
        while (rs.next())
            System.out.println(rs.getObject(1));
    }
}

But on SQL Data Warehouse, it doesn't work:

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: 'SCOPE_IDENTITY' is not a recognized built-in function name.
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:264)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1585)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.doExecuteStatement(SQLServerStatement.java:876)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement$StmtExecCmd.doExecute(SQLServerStatement.java:776)
    at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7385)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:2750)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:235)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:210)
    at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeUpdate(SQLServerStatement.java:2060)
    at SQLServer.main(SQLServer.java:65)

The other methods (e.g. executeUpdate(String, String[])) don't work either, as they delegate to the above one.

I understand that SQL Data Warehouse doesn't support the SCOPE_IDENTITY() and similar functions, which seem to be used behind the scenes by the mssql-jdbc driver:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>6.5.2.jre8-preview</version>
</dependency>

But is there a workaround?

Notes:

The output clause is also not available: https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql
I've also filed a bug on Github for mssql-jdbc

I'll say it's an issue with use `IDENTITY` in the create table script, consider using `PRIMARY KEY AUTOINCREMENT` instead — DDS, May 28 '18 at 08:54
Not uncommon use case in DW is, that there is *only one ETL job* population the table. In this case there is no real profit from identity or sequence generated ID's. The job can controll the key assignment. Just .02, I don't know you exact use case... — Marmite Bomber, May 28 '18 at 09:56
@MarmiteBomber: Sure, but then why support `IDENTITY` columns in the first case? My use-case is to implement SQL Data Warehouse in jooq.org, so I can't really speculate about any possible use-cases here... — Lukas Eder, May 28 '18 at 10:16

score 0 · Answer 1 · answered May 28 '18 at 16:29

0

The workaround is to add to your insert query, then select the id.

insert into t_identity
(val)
select 'my value'
where not exists
(
select 1
from t_identity
where val = 'my value'
)

select id
from t_dan_identity
where val = 'my value'

answered May 28 '18 at 16:29

Dan Bracuk

20,699
4
26
43

That seems risky given that SQL Data Warehouse doesn't even support any unique constraints, nor does it seem to produce identity values in a consistent, consecutive order... – Lukas Eder May 29 '18 at 08:06
The lack of unique constraints inspired me to use uniqueidentifier instead of integer identity for my ID fields. But, given the limitations of the software you have been told to use, you might not have other options. – Dan Bracuk May 29 '18 at 12:29

score 0 · Answer 2 · answered May 29 '18 at 11:40

Can you describe your business use case in more detail? If you're using JOOQ, I think you might have a mismatch in technologies.

Azure SQL Data Warehouse is not intended for transactional workloads. If JOOQ is just maintaining a few reference rows then I don't see a problem, but if it is your main data loading strategy you're not going to get a good result.

Here's some reading that might help:

Use cases and anti-patterns: https://blogs.msdn.microsoft.com/sqlcat/2017/09/05/azure-sql-data-warehouse-workload-patterns-and-anti-patterns/

Solution models (scroll to diagrams): https://azure.microsoft.com/en-us/services/sql-data-warehouse/

Data loading best practices: https://learn.microsoft.com/en-us/azure/sql-data-warehouse/guidance-for-loading-data

Data loading patterns and strategies: https://blogs.msdn.microsoft.com/sqlcat/2017/05/17/azure-sql-data-warehouse-loading-patterns-and-strategies/

I'm not using jOOQ, I'm making jOOQ ;-) – Lukas Eder May 29 '18 at 12:13 — Lukas Eder, May 29 '18 at 12:13

Cannot fetch generated keys through JDBC on SQL Data Warehouse

Notes:

2 Answers2