useGeneratedKeys with Oracle appears to require keyColumns, is this correct?

classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

useGeneratedKeys with Oracle appears to require keyColumns, is this correct?

Kevin Schmidt
Using MyBatis 3.3.0 (and behaves the same with 3.4.2) I'd implemented inserting rows into a table with useGeneratedKeys=true and specifying just keyProperty for my key column.  This worked fine with MySQL.

But when I tried to use this as-is with Oracle it didn't work, getting:

org.apache.ibatis.exceptions.PersistenceException:

### Error updating database.  Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set.  Cause: java.sql.SQLException: Invalid conversion requested

...

### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set.  Cause: java.sql.SQLException: Invalid conversion requested

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)

...

Caused by: java.lang.NumberFormatException

        at java.math.BigDecimal.<init>(BigDecimal.java:494)[:1.8.0_92]

        at java.math.BigDecimal.<init>(BigDecimal.java:383)[:1.8.0_92]

        at java.math.BigDecimal.<init>(BigDecimal.java:806)[:1.8.0_92]

        at oracle.jdbc.driver.T4CVarcharAccessor.StringToNUMBER(T4CVarcharAccessor.java:825)



Searching the MyBatis docs, there was nothing indicating what might be a problem, it was only digging around in some Oracle documentation that I found this:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.

Based on that, I specified keyColumns as well and that got it to work.  So is this the correct approach?  Or is there another way to get this working with Oracle?

The MyBatis docs mention keyColumns being needed for PostgreSQL but nothing about Oracle. So if this is correct the docs should probably be updated.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: useGeneratedKeys with Oracle appears to require keyColumns, is this correct?

Guy Rouillier-2
I've always specified the keyColumn when employing useGeneratedKeys with Oracle.

--
Guy Rouillier



------ Original Message ------
From: "Kevin Schmidt" <[hidden email]>
To: "mybatis-user" <[hidden email]>
Sent: 3/23/2017 1:31:11 PM
Subject: useGeneratedKeys with Oracle appears to require keyColumns, is this correct?

Using MyBatis 3.3.0 (and behaves the same with 3.4.2) I'd implemented inserting rows into a table with useGeneratedKeys=true and specifying just keyProperty for my key column.  This worked fine with MySQL.

But when I tried to use this as-is with Oracle it didn't work, getting:

org.apache.ibatis.exceptions.PersistenceException:

### Error updating database.  Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set.  Cause: java.sql.SQLException: Invalid conversion requested

...

### Cause: org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set.  Cause: java.sql.SQLException: Invalid conversion requested

        at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:200)

        at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:185)

...

Caused by: java.lang.NumberFormatException

        at java.math.BigDecimal.<init>(BigDecimal.java:494)[:1.8.0_92]

        at java.math.BigDecimal.<init>(BigDecimal.java:383)[:1.8.0_92]

        at java.math.BigDecimal.<init>(BigDecimal.java:806)[:1.8.0_92]

        at oracle.jdbc.driver.T4CVarcharAccessor.StringToNUMBER(T4CVarcharAccessor.java:825)



Searching the MyBatis docs, there was nothing indicating what might be a problem, it was only digging around in some Oracle documentation that I found this:

If key columns are not explicitly indicated, then Oracle JDBC drivers cannot identify which columns need to be retrieved. When a column name or column index array is used, Oracle JDBC drivers can identify which columns contain auto-generated keys that you want to retrieve. However, when the Statement.RETURN_GENERATED_KEYS integer flag is used, Oracle JDBC drivers cannot identify these columns. When the integer flag is used to indicate that auto-generated keys are to be returned, the ROWID pseudo column is returned as key. The ROWID can be then fetched from the ResultSet object and can be used to retrieved other columns.

Based on that, I specified keyColumns as well and that got it to work.  So is this the correct approach?  Or is there another way to get this working with Oracle?

The MyBatis docs mention keyColumns being needed for PostgreSQL but nothing about Oracle. So if this is correct the docs should probably be updated.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

Virus-free. www.avast.com

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.