Cannot fetch generated keys through JDBC on SQL Data Warehouse

4
votes

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:

2
sqljdbcazure-sqldw
I'll say it's an issue with use IDENTITY in the create table script, consider using PRIMARY KEY AUTOINCREMENT instead – DDS
@DDS: Ehm, no :) – Lukas Eder
@DDS You seem to think this question is about MySQL – Mark Rotteveel
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
@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

2 Answers

0
votes

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'
0
votes

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://docs.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/