on 04-08-2013 6:22 AM
Hi,
Our application is connecting to a HANA database via Hibernate (we are using the SAPDBDialect).
I am able to read, update and delete records.
For create, we use a database sequence (running number) to get the id which will be used as the primary key of the new record being inserted.
<table name="TABLE1">
<meta attribute="implements">Object1</meta>
<primary-key>
<generator class="sequence-identity">
<param name="sequence">SD_DW_PK_SEQ</param>
</generator>
<key-column name="ID" type="java.lang.Long"/>
</primary-key>
<column name="SEQ" type="java.lang.Short"/>
<column name="MIN_RANGE" type="java.lang.Short"/>
......
Above is the hibernate configuration that allows us to do this in ONE call with Oracle.
I know the call translates to the following JDBC call
prepareStatement(String sql, int autoGeneratedKeys)
With HANA, we get the following exception
java.lang.UnsupportedOperationException: Retrieving auto generated keys is not supported.
at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:2113)
at com.sap.db.jdbc.trace.Connection.prepareStatement(Connection.java:375)
at org.apache.commons.dbcp.DelegatingConnection.prepareStatement(DelegatingConnection.java:418)
at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.prepareStatement(PoolingDataSource.java:385)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:527)
at org.hibernate.jdbc.AbstractBatcher.prepareStatement(AbstractBatcher.java:131)
at org.hibernate.id.SequenceIdentityGenerator$Delegate.prepare(SequenceIdentityGenerator.java:99)
at org.hibernate.id.insert.AbstractReturningDelegate.performInsert(AbstractReturningDelegate.java:54)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2345)
at org.hibernate.persister.entity.AbstractEntityPersister.insert(AbstractEntityPersister.java:2852)
at org.hibernate.impl.StatelessSessionImpl.insert(StatelessSessionImpl.java:112)
at org.hibernate.impl.StatelessSessionImpl.insert(StatelessSessionImpl.java:97)
It seems HANA JDBC driver does not support prepareStatement(String sql, int autoGeneratedKeys).
Can someone confirm? If so, is there a plan to fix this?
We don't really want to do it in 2 separate calls/steps
a) get the next number from running sequence
b) insert the record with id and other values
Thanks!
Amit
Hi Amit,
confirmed, this is currently not supported by the HANA JDBC driver.
I've no idea on whether or not this is on the to-do-list (but would guess so), so for now you can't expect this feature to be present soon.
At the moment I don't see a way to save the two calls and just perform one.
If you don't actually need to know the generated number after you created the record, you could just include the <sequence_name>.nextval call in the INSERT statement.
After the call you can fetch the current value via <sequence_name>.curval - but that would just be another call as well.
- Lars
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Lars,
Thanks for such prompt response!
Actually, the original post had a minor error. We are using
prepareStatement(String sql, String[] columnNames)
not
prepareStatement(String sql,int autoGeneratedKeys)
Anyways, the driver doesn't support both of them.
You are right about putting the nextval expression in the INSERT statement. That works but I actually don't want to write queries by hand (We are using Hibernate for everything and would not like to deviate).
I will use a workaround for now. I hope it's supported soon.
It would be great if someone from the JDBC driver team and give some sort of estimation on the timeline.
Thanks Again!
Amit
User | Count |
---|---|
86 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.