Fetch primary key generated by sequence using jdbctemplate / jdbc for Hana db

Question

We are trying to insert record in hana db so we are currently using jdbc template and key holder to save data and retrieve newly generated column id.This works fine for postgress but not for hana.

query ="SELECT CURRENT_IDENTITY_VALUE() FROM \"HALOSYS\".\"HaloTestDemo\"";  
resultSet =  stmt.executeQuery(query);

Above statement gives current identity value but this doesn't fit into our context where we want to use jdbc template. Please give me idea to fix this. if we add preparedstatement generated keys i am getting follwing exception

com.sap.db.jdbc.exceptions.jdbc40.SQLFeatureNotSupportedException: Method prepareStatement( String, int )() of Connection is not supported.

You want to get the keys generated when you insert new rows? — Nicolas Filotto, Nov 23 '16 at 09:55
com.sap.db.jdbc.exceptions.jdbc40.SQLFeatureNotSupportedException: Method prepareStatement( String, int )() of Connection is not supported. — pruthvi pv, Nov 23 '16 at 10:41

score 0 · Answer 1 · answered Jan 21 '21 at 08:53

0

Try from DUMMY instead of the table name, and do it in the same session as the insert. If you use connection pooling, that will be much harder.

answered Jan 21 '21 at 08:53

Remi sap

144
6

score 0 · Answer 2 · answered Jun 02 '21 at 13:17

Please prepare your query statement to include insert record and select CURRENT_IDENTITY_VALUE() from the same table into one query statement in an anonymous block as below :

DO BEGIN
    INSERT INTO <TABLE_NAME> .....;
    SELECT CURRENT_IDENTITY_VALUE() FROM <TABLE_NAME>;
END;

Fetch primary key generated by sequence using jdbctemplate / jdbc for Hana db

2 Answers2