Spring Can Spring JDBC insert rows into a Snowflake table when autoincrement is used?

My code to insert a row into my Snowflake table is throwing an exception since the null value that SimpleJdbcInsert puts into the INSERT sql violates the NOT NULL constraint on the table. I'm using Spring Boot 2.2.5, Spring 5.2.4, and Snowflake JDBC driver 3.12.6. Knowing that the ROW_ID column is an autoincrement column, shouldn't the generated INSERT sql statement omit this field?

Here's the info:

DDL:

create or replace TABLE TEST (
    ROW_ID NUMBER(38,0) NOT NULL autoincrement,
    COL1 VARCHAR(50),
    constraint PK_ROW_ID primary key (ROW_ID)
);

Code:

    private void insertRecord() {
        SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
                .withTableName("TEST");

        Map<String, Object> parameters = new HashMap<>();
        parameters.put("COL1", "foobar");

        int i = simpleJdbcInsert.execute(parameters);
    }

Exception/Log:

c.c.test.SnowflakeTestApplication       :141 : Creating data source snowflake ...
c.c.test.SnowflakeTestApplication       :157 : Data source created successfully
c.c.test.SnowflakeTestApplication       :61 : Started SnowflakeTestApplication in 2.327 seconds (JVM running for 4.622)
o.s.jdbc.core.simple.SimpleJdbcInsert   :308 : JdbcInsert not compiled before execution - invoking compile
o.s.jdbc.datasource.DataSourceUtils     :115 : Fetching JDBC Connection from DataSource
o.s.j.datasource.SimpleDriverDataSource :142 : Creating new JDBC Driver Connection to [jdbc:snowflake://xxxxxx.us-east-1.snowflakecomputing.com/?db=DEMO_DB&schema=PUBLIC&role=U_ENG_DW_DDL&warehouse=DEV_ADHOC_WH]
o.s.j.c.m.TableMetaDataProviderFactory  :76 : Using GenericTableMetaDataProvider
o.s.j.c.metadata.TableMetaDataProvider  :154 : GetGeneratedKeys is not supported
o.s.j.c.metadata.TableMetaDataProvider  :392 : Retrieving meta-data for DEMO_DB/PUBLIC/TEST
o.s.j.c.metadata.TableMetaDataProvider  :418 : Retrieved meta-data: ROW_ID -5 false
o.s.j.c.metadata.TableMetaDataProvider  :418 : Retrieved meta-data: COL1 12 true
o.s.jdbc.core.simple.SimpleJdbcInsert   :281 : Compiled insert object: insert string is [INSERT INTO TEST (ROW_ID, COL1) VALUES(?, ?)]
o.s.jdbc.core.simple.SimpleJdbcInsert   :264 : JdbcInsert for table [TEST] compiled
o.s.jdbc.core.simple.SimpleJdbcInsert   :356 : The following parameters are used for insert INSERT INTO TEST (ROW_ID, COL1) VALUES(?, ?) with: [null, foobar]
o.s.jdbc.core.JdbcTemplate              :860 : Executing prepared SQL update
o.s.jdbc.core.JdbcTemplate              :609 : Executing prepared SQL statement [INSERT INTO TEST (ROW_ID, COL1) VALUES(?, ?)]
o.s.jdbc.datasource.DataSourceUtils     :115 : Fetching JDBC Connection from DataSource
o.s.j.datasource.SimpleDriverDataSource :142 : Creating new JDBC Driver Connection to [jdbc:snowflake://xxxxxx.us-east-1.snowflakecomputing.com/?db=DEMO_DB&schema=PUBLIC&role=U_ENG_DW_DDL&warehouse=DEV_ADHOC_WH]
o.s.jdbc.support.SQLErrorCodesFactory   :203 : Looking up default SQLErrorCodes for DataSource [org.springframework.jdbc.datasource.SimpleDriverDataSource@7b5b5bfe]
o.s.jdbc.datasource.DataSourceUtils     :115 : Fetching JDBC Connection from DataSource
o.s.j.datasource.SimpleDriverDataSource :142 : Creating new JDBC Driver Connection to [jdbc:snowflake://xxxxxx.us-east-1.snowflakecomputing.com/?db=DEMO_DB&schema=PUBLIC&role=U_ENG_DW_DDL&warehouse=DEV_ADHOC_WH]
o.s.jdbc.support.SQLErrorCodesFactory   :186 : SQL error codes for 'Snowflake' not found
o.s.jdbc.support.SQLErrorCodesFactory   :247 : Caching SQL error codes for DataSource [org.springframework.jdbc.datasource.SimpleDriverDataSource@7b5b5bfe]: database product name is 'Snowflake'
s.j.s.SQLErrorCodeSQLExceptionTranslator:285 : Unable to translate SQLException with Error code '100072', will now try the fallback translator
o.s.j.s.SQLStateSQLExceptionTranslator  :98 : Extracted SQL state class '22' from value '22000'
...
java.lang.IllegalStateException: Failed to execute ApplicationRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:778)
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:765)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1215)
    at com.covetrus.test.SnowflakeTestApplication.main(SnowflakeTestApplication.java:45)
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO TEST (ROW_ID, COL1) VALUES(?, ?)]; NULL result in a non-nullable column; nested exception is net.snowflake.client.jdbc.SnowflakeSQLException: NULL result in a non-nullable column
    at org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:104)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:81)
    at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917)
    at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:922)
    at org.springframework.jdbc.core.simple.AbstractJdbcInsert.executeInsertInternal(AbstractJdbcInsert.java:358)
    at org.springframework.jdbc.core.simple.AbstractJdbcInsert.doExecute(AbstractJdbcInsert.java:337)
    at org.springframework.jdbc.core.simple.SimpleJdbcInsert.execute(SimpleJdbcInsert.java:117)
    at com.covetrus.test.SnowflakeTestApplication.insertRecord(SnowflakeTestApplication.java:99)
    at com.covetrus.test.SnowflakeTestApplication.run(SnowflakeTestApplication.java:51)

Comment From: quaff

SimpleJdbcInsert simpleJdbcInsert = new SimpleJdbcInsert(jdbcTemplate)
                .withTableName("TEST").usingGeneratedKeyColumns("ROW_ID");

Comment From: joepardi

Thanks for the quick response @quaff. That did the trick!

I had that call in the code previously but it was combined with using SimpleJdbcInsert.executeAndReturnKey. Since Snowflake doesn't support GetGeneratedKeys yet, I removed both pieces since I assumed the calls went together. I've opened up a ticket request with Snowflake to add this in, but it will take a bit to add it into the driver.

Again, thanks for your help!

Closing the issue.