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
On Fri, Mar 29, 2024 at 4:10 PM Balamurali Krishna Ippili <
balamurali.ipp...@broadcom.com> wrote:
> 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)
> )
>
> On Fri, Mar 29, 2024 at 4:09 PM Balamurali Krishna Ippili <
> balamurali.ipp...@broadcom.com> wrote:
>
>> 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
>>
>> On Fri, Mar 29, 2024 at 3:55 PM Balamurali Krishna Ippili <
>> balamurali.ipp...@broadcom.com> wrote:
>>
>>> Hi Evgenij,
>>>
>>> Thanks for your quick reply and suggestion. Really appreciate it. I
>>> will check further on your suggestion.
>>>
>>> Thanks,
>>> Balamurali
>>>
>>> On Fri, Mar 29, 2024 at 3:06 PM Evgenij Ryazanov <kat...@gmail.com>
>>> wrote:
>>>
>>>> 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
>>>>
>>>> --
>>>> 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+unsubscr...@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
>>>> <https://groups.google.com/d/msgid/h2-database/0f828877-acab-49eb-a39a-8df33b23d309n%40googlegroups.com?utm_medium=email&utm_source=footer>
>>>> .
>>>>
>>>
--
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.
--
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+unsubscr...@googlegroups.com.
To view this discussion on the web visit
https://groups.google.com/d/msgid/h2-database/CAG6_yCD6QbStgVr_G2cxzT3AEXyy9n-Y_snp00Xsuxmp1Jwp%3DQ%40mail.gmail.com.
smime.p7s
Description: S/MIME Cryptographic Signature
