Auto Increment statement is giving an error when passing null with H2DB 2.2.224 version

[Balamurali Krishna Ippili]

Hi Team,

As part of the H2DB 1.4.199 to 2.2.224 migration we have faced a challenge in inserting data in a table where we are using auto_increment for the id.

My table with H2DB 1.4.199 is like below:

create table nim_entity_properties(
id int auto_increment,
entity_type varchar(40),
property_name varchar(50)
);

and when I am trying to insert the values with insert statement like below:

INSERT INTO `nim_entity_properties` (`id` ,`entity_type` ,`property_name` ) VALUES (null,'incident','custom123',);

The statement is executed fine with H2DB version 1.4.199 and it has inserted the value with an auto incremented id (existing rows count +1 )

However the same insert statement is giving an error like below with the H2DB 2.2.224 version.

Caused by: org.h2.jdbc.JdbcSQLIntegrityConstraintViolationException: NULL not allowed for column "ID"; SQL statement:
INSERT INTO `nim_entity_properties` (`id` ,`entity_type` ,`property_name`  ) VALUES (?,?,?) [23502-224]

I have tried to alter the table column for id as below that was allowing the insert statement but it is  giving a duplicate id 1 and the insertion successful. It is not adding 1 to the existing row count so that is not helpful for me .

So is there any way that we can get the auto_increment to consider null in the insert statement and generate an id for the new row and insert into DB successfully?

Thanks in advance.

Regards,

Balamurali

This electronic communication and the information and any files transmitted with it, or attached to it, are confidential and are intended solely for the use of the individual or entity to whom it is addressed and may contain information that is confidential, legally privileged, protected by privacy laws, or otherwise restricted from disclosure to anyone else. If you are not the intended recipient or the person responsible for delivering the e-mail to the intended recipient, you are hereby notified that any use, copying, distributing, dissemination, forwarding, printing, or copying of this e-mail is strictly prohibited. If you received this e-mail in error, please return the e-mail to the sender, delete it from your computer, and destroy any printed copy of it.

[Balamurali Krishna Ippili]

I have tried to alter the table column for id as below and that is allowing the insert statement but it is adding the new row with a duplicate id (1) and the insertion successful. But I am expecting that id to be count of current rows +1  so that is not helpful for me .

ALTER TABLE nim_entity_properties ALTER COLUMN id
 int  generated by default as identity default on null

Thanks,

Balamurali

[Evgenij Ryazanov]

Hi!

You shouldn't try to insert NULL into columns with NOT NULL constrains. Only non-null values or DEFAULT may be inserted into them.

H2 allows such incorrect insertions into identity columns only in Legacy, HSLQDB, MariaDB, and MySQL compatibility modes.

You can also enable this feature for any column in any compatibility mode with ALTER TABLE tableName ALTER COLUMN columnName SET DEFAULT ON NULL

[Balamurali Krishna Ippili]

Hi Evgenij,

Thanks for your quick reply and suggestion. Really appreciate it. I will check further on your suggestion.

Thanks,

Balamurali

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0f828877-acab-49eb-a39a-8df33b23d309n%40googlegroups.com.

[Balamurali Krishna Ippili]

Hi Evgenij and Team,

I have changed the tabl;e definition as like below:

create table nim_entity_properties(
id int auto_increment DEFAULT ON NULL,
entity_type varchar(40),
property_name varchar(50),
data_type varchar(40)
            )

And now I am able to insert the row with the auto increment number even if I pass the null for the ID , however I am getting a different SQL exception like below but the actual data inserted in a new row with proper ID number.

Caused by: java.sql.SQLException: Unexpected ID column type STRING (typeVal 12) in column ENTITY_TYPE(#2) is not a number
at com.j256.ormlite.jdbc.JdbcDatabaseConnection.getIdColumnData(JdbcDatabaseConnection.java:309) ~[ormlite-jdbc-4.48.jar:?]
at com.j256.ormlite.jdbc.JdbcDatabaseConnection.insert(JdbcDatabaseConnection.java:179) ~[ormlite-jdbc-4.48.jar:?]
at com.j256.ormlite.stmt.mapped.MappedCreate.insert(MappedCreate.java:91) ~[ormlite-core-4.48.jar:?]

Unfortunately I cannot use compatibility MODEs like Legacy, MYSql etc...

Any thoughts or suggestions on this?

Thanks,
Balamurali

[Balamurali Krishna Ippili]

Small correction on the table definition:

create table nim_entity_properties(
id int auto_increment DEFAULT ON NULL,
entity_type varchar(40),
property_name varchar(50)

)

[Balamurali Krishna Ippili]

Hi  Evgenij and Team,

As per my understanding  the following prepared statement is executed perfectly on H2DB and inserted in a row. 

prep1250: INSERT INTO `nim_entity_properties` (`id` ,`entity_type` ,`property_name` ,`data_type` ,`is_custom_property` ) VALUES (?,?,?,?,?) {1: NULL, 2: 'incident', 3: 'TEST', 4: 'string', 5: TRUE}

However the following stmt.getGeneratedKeys(); method( belongs to com.j256.ormlite.jdbc.JdbcDatabaseConnection.java) on the stmt object is returning 3 columns and 1 row with H2DB 2.2.224, but when we are executing the same prepared statement on H2DB 1.4.199 the stmt.getGeneratedKeys(); that is resulting to 1 column and 1 row which leads to execute the switch case in default case and caused the issue:

com.j256.ormlite.jdbc.JdbcDatabaseConnection.java 

========================================

/**
* Return the id associated with the column.
*/
private Number getIdColumnData(ResultSet resultSet, ResultSetMetaData metaData, int columnIndex)
throws SQLException {
int typeVal = metaData.getColumnType(columnIndex);
switch (typeVal) {
case Types.BIGINT :
case Types.DECIMAL :
case Types.NUMERIC :
return (Number) resultSet.getLong(columnIndex);
case Types.INTEGER :
return (Number) resultSet.getInt(columnIndex);
default :
String columnName = metaData.getColumnName(columnIndex);
throw new SQLException("Unexpected ID column type " + TypeValMapper.getSqlTypeForTypeVal(typeVal)
+ " (typeVal " + typeVal + ") in column " + columnName + "(#" + columnIndex
+ ") is not a number");
}
}

What could be the reason for returning 3 columns and 1 row when getting the generatedKeys on PreparedStatement Object(stmt.getGeneratedKeys();) with H2DB 2.2.224 but returning only 1 column and 1 row with H2DB 1.4.199?

Thanks,

Balamurali

[Evgenij Ryazanov]

When you use Statement.RETURN_GENERATED_KEYS, driver may choose what it should return by itself, see JDBC™ 4.3 Specification, section 13.6. Modern versions of H2 return primary key columns, identity columns, and columns with non-constant default expression (including expressions inherited from domains).

But you can pass an array of column names or column indexes instead of that constant to request the exact columns.

[Balamurali Krishna Ippili]

Hi Evgenij,

Thank you so much for your response. 

Sure , I will check the feasibility in our application to pass an array of column names or column indexes instead of that constant to request the exact columns.

Regards,

Balamurali

On Fri, Mar 29, 2024 at 7:06 PM Evgenij Ryazanov <kat...@gmail.com> wrote:

When you use Statement.RETURN_GENERATED_KEYS, driver may choose what it should return by itself, see JDBC™ 4.3 Specification, section 13.6. Modern versions of H2 return primary key columns, identity columns, and columns with non-constant default expression (including expressions inherited from domains).

But you can pass an array of column names or column indexes instead of that constant to request the exact columns.

--

You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/e2b9ff41-9cd6-48fb-871f-1d8f73ff095cn%40googlegroups.com.