JDBC Result set from HANA procedure

[tbealby]

I wondered if you could advise on the following issue please -

I have not been able to get a result set back from a procedure querying a SAP HANA database when using vertx2.

I am calling the procedure like this (pseudocode) and it's not working: ... String sql = "CALL "XX_SCHEMANAME\".mySprocName(?,?,?,?)"; JsonArray ja_vars = new JsonArray().addString("val1").addString("val2").addString("val3").addNumber(123));

eb.send(JDBC_EVENTBUS_ADDR, new JsonObject().putString("action", "select").putString("stmt", sql) .putArray("values", new JsonArray().addArray( ja_vars ):

[vert.x-worker-thread-14] 2016-06-27T09:11:45.345+01:00 SEVERE [com.bloidonia~mod-jdbc-persistor~2.1.2-com.bloidonia.vertx.mods.JdbcProcessor-1186973244] Error performing batch select com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: SQL statement would generate a row count. 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.CallableStatementSapDB.executeQuery(CallableStatementSapDB.java:754) at com.sap.db.jdbc.trace.PreparedStatement.executeQuery(PreparedStatement.java:161) at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:404) at com.bloidonia.vertx.mods.JdbcProcessor$4.process(JdbcProcessor.java:276) at com.bloidonia.vertx.mods.JdbcProcessor$BatchHandler.handle(JdbcProcessor.java:596) at com.bloidonia.vertx.mods.JdbcProcessor.doSelect(JdbcProcessor.java:283) at com.bloidonia.vertx.mods.JdbcProcessor.doSelect(JdbcProcessor.java:255) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:207) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:47) at org.vertx.java.core.eventbus.impl.DefaultEventBus$7.run(DefaultEventBus.java:689) at org.vertx.java.core.impl.DefaultContext$3.run(DefaultContext.java:170) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)

I am wondering if there is a problem in com.bloidonia.vertx.mods.JdbcProcessor with allocating parameters to statement place holders.

The problem might be with the following code in the anonymous BatchHandler's process() method defined in method JdbcProcessor.doSelect

        List<Object> params = valueIterator.next() ;
        statementFiller.fill( statement, params ) ;
        resultSet = statement.executeQuery() ;

When I replace the above code with

Statement stmt = connection.createStatement(); resultSet = stmt.executeQuery(message.body().getString( "stmt" )) ;

AND change the value of the sql used to set field "stmt"..

String sql = "CALL "XX_SCHEMANAME\".mySprocName('val1','val2','val3',123)"; JsonArray ja_vars = new JsonArray();

then I do get a result set returned from the procedure.

I have also tried changing the code to

            statement = connection.prepareStatement(message.body().getString( "stmt" ));
            statement.setString(1, (String) params.get(0));
            statement.setString(2, (String) params.get(1));
            statement.setString(3, (String) params.get(2));
    statement.setInt(4, (int) params.get(3));
    resultSet = statement.executeQuery() ;

but this second change does not work either.

The issue is occuring only on a SAP HANA database. The code works fine when executing a procedure on a mysql database.

I am wondering whether I need to upgrade to vertx3.

[timyates]

Have you tried with "action" set to "execute"?

Not sure, HANA issues are hard because they're basically impossible for me to test

[tbealby]

Thanks for the quick response and for your suggestion. When I change to use 'execute' instead of 'select' I do get a different error message, which is hopefully progress! The stacktrace is now...

[vert.x-worker-thread-13] 2016-06-27T10:38:53.772+01:00 SEVERE [com.bloidonia~mod-jdbc-persistor~2.1.2-com.bloidonia.vertx.mods.JdbcProcessor-837515422] Caught error with EXECUTE com.sap.db.jdbc.exceptions.JDBCDriverException: SAP DBTech JDBC: [340]: not all variables bound: unbound parameter : 1 of 4 at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.createException(SQLExceptionSapDB.java:345) at com.sap.db.jdbc.exceptions.SQLExceptionSapDB.generateDatabaseException(SQLExceptionSapDB.java:185) at com.sap.db.jdbc.packet.ReplyPacket.buildExceptionChain(ReplyPacket.java:100) at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:1130) at com.sap.db.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:877) at com.sap.db.jdbc.StatementSapDB.sendCommand(StatementSapDB.java:932) at com.sap.db.jdbc.StatementSapDB.sendSQL(StatementSapDB.java:981) at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:256) at com.sap.db.jdbc.StatementSapDB.execute(StatementSapDB.java:228) at com.sap.db.jdbc.trace.Statement.execute(Statement.java:79) at com.mchange.v2.c3p0.impl.NewProxyStatement.execute(NewProxyStatement.java:197) at com.bloidonia.vertx.mods.JdbcProcessor.doExecute(JdbcProcessor.java:312) at com.bloidonia.vertx.mods.JdbcProcessor.doExecute(JdbcProcessor.java:296) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:210) at com.bloidonia.vertx.mods.JdbcProcessor.handle(JdbcProcessor.java:47) at org.vertx.java.core.eventbus.impl.DefaultEventBus$7.run(DefaultEventBus.java:689) at org.vertx.java.core.impl.DefaultContext$3.run(DefaultContext.java:170) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745)

I appreciate that HANA issues will be very hard if you can't test them.

I will do some more investigation and then update the post. Thanks.

[timyates]

Can you get the query to work outside of vertx with a regular JDBC connection?

What does that look like?

[tbealby]

I have tried the following code using a regular JDBC connection. ie NOT going through vert.x.

String sql = "CALL "XX_SCHEMANAME\".mySprocName(?,?,?,?)"; PreparedStatement statement = connection.prepareStatement(sql); statement.setString(1, "val1"); statement.setString(2, "val2"); statement.setString(3, "val3"); statement.setInt(4, 123); resultSet = statement.executeQuery() ;

I get the error - SAP DBTech JDBC: SQL statement would generate a row count

I guess this means that it is probably a HANA issue rather than a vert.x issue.

[timyates]

No idea, but is this any help?

[tbealby]

Thanks very much for all your help & useful suggestions. I am going to look into the HANA side more deeply now. Please do not spend any more time on this. I will update this blog when I have a better understanding of the HANA side.

[timyates]

Cool, I might close this, and you can open a new Issue when you get further if needs be!

Still might be an issue with this mod ;-)

Good luck tracking it down!