HANA "Select for update" through JDBC: CONCUR_UPDATABLE result set concurrency is considered invalid

[Former Member]

I am in the process of trying to use HANA as persistence layer for our application.

For this I am using the AWS image “ami-68bf1d1b” to run the HANA database.

I have been running our database test that verifies we adequately generate the SQL needed for our application for each database we support.

This test passes on all the other SQL databases our software already supports: Derby, Sqlite, mySql, SQLServer, Oracle and Postgresql.

It creates a table, inserts some records, performs some updates then updates some rows through a “select for update”. (The “?” are placeholders filled with values through JDBC.)

DatabaseTest/2016-02-22 11:01:22 info:create table TEST_TBL79960 (ID VARCHAR(10), DATA BLOB, AMOUNT DOUBLE, primary key (ID))

DatabaseTest/2016-02-22 11:01:23 info:insert into TEST_TBL79960 (ID, DATA, AMOUNT) values (?, ?, ?)

DatabaseTest/2016-02-22 11:01:23 info:update TEST_TBL79960 set DATA = ?, AMOUNT = ? where ID = ?

DatabaseTest/2016-02-22 11:01:24 info:select ID, DATA, AMOUNT from TEST_TBL79960 where ID = ? for update

It did fail when executing the “select for update” with the following exception:.

com.sap.db.jdbc.exceptions.jdbc40.SQLDataException: Invalid argument resultSetConcurrency, use CONCUR_READ_ONLY.

  at com.sap.db.jdbc.exceptions.jdbc40.SQLDataException.createException(SQLDataException.java:40)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:278)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateSQLException(SQLExceptionSapDB.java:146)

  at com.sap.db.jdbc.StatementSapDB.<init>(StatementSapDB.java:114)

  at com.sap.db.jdbc.CallableStatementSapDB.<init>(CallableStatementSapDB.java:88)

  at com.sap.db.jdbc.CallableStatementSapDBFinalize.<init>(CallableStatementSapDBFinalize.java:31)

  at com.sap.db.jdbc.ConnectionSapDB.prepareStatement(ConnectionSapDB.java:1287)

  at com.sap.db.jdbc.trace.Connection.prepareStatement(Connection.java:355)

  at ides.core.tools.sql.Database.select(Database.java:526)

  at ides.app.tools.sql.DatabaseTest.test(DatabaseTest.java:278)

  at ides.app.tools.sql.DatabaseTest.testHana(DatabaseTest.java:147)

This test is done after having verified that the database supports “select for update”:

Connection connection = …; // obtain connection to HANA

DatabaseMetaData metaData = connection.getMetaData();

metaData.supportsSelectForUpdate(); // HANA returns true

The select statement itself is executed with:

PreparedStatement selectForUpdate = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_UPDATABLE);

selectForUpdate.setString(1, "test");

ResultSet resultSet = selectForUpdate.executeQuery();

We specify “ResultSet.CONCUR_UPDATABLE” because we want to set values in the obtained result set - that is the whole point of having used a “select for update”.

If I change the code as indicated by the HANA exception above:

PreparedStatement selectForUpdate = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

selectForUpdate.setString(1, "test");

ResultSet resultSet = selectForUpdate.executeQuery();

Then the HANA JDBC driver raises the following exception when trying to update the content of the result set:

com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: Result set is not updatable.

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:374)

  at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateSQLException(SQLExceptionSapDB.java:113)

  at com.sap.db.jdbc.ResultSetSapDB.throwNotUpdatable(ResultSetSapDB.java:2837)

  at com.sap.db.jdbc.ResultSetSapDB.updateBytes(ResultSetSapDB.java:1666)

  at com.sap.db.jdbc.trace.ResultSet.updateBytes(ResultSet.java:1260)

  at ides.app.tools.sql.DatabaseTest.test(DatabaseTest.java:285)

  at ides.app.tools.sql.DatabaseTest.testHana(DatabaseTest.java:147)

The update is done like this:

resultSet.updateBytes(2, new byte[25]); // triggers the exception

resultSet.updateRow(); // never reached

This second exception is the correct behavior according to me, because we are supposed to request an updatable result set as done initially.

I am using ngdbc.jar 1.111.1.

(According to its manifest entry:

Bundle-Version: 1.111.1.1221f56b58af622cf9c533120b6f6a47e9334898)

Is there a more recent HANA JDBC driver that might solve this problem ?

Or am I missing something very specific that must be done with HANA to use a select for update through JDBC ?

[lucas_oliveira]

Hello Cristina,

I've tried that as well. As of now, the ResultSet implementation only allows result set concurrency to CONCUR_READ_ONLY.

In other words: currently you can't update the records directly on a ResultSet.

In order to bypass that, you can still use the 'select ... for update' to acquire the exclusive record locks, iterate through the resultset and update the rows individually with executeUpdate.

Something like:

PreparedStatement updStmt = connection.prepareStatement("update TEST_TBL79960 set DATA = ?");
Statement selStmt = con.createStatement();

ResultSet resultSet = selStmt.executeQuery("select ID, DATA, AMOUNT from TEST_TBL79960 where ID = ? for update");

//...

while (resultSet.next()) {
  //...

  updStmt.updateBytes(1, new byte[25]);
  updStmt.executeUpdate();

}
con.commit();

ps.: needless to say you need to take care with your commit sizes not to flood the memory with uncommited transactions for long

BRs,

Lucas de Oliveira