vertx-jdbc-client icon indicating copy to clipboard operation
vertx-jdbc-client copied to clipboard

batch execution involving BLOB not working for oracle

Open klas3000 opened this issue 3 years ago • 0 comments

Version

4.2.3

Context

given table

CREATE TABLE my_table
(
  ID        NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author    varchar2(20),
  book      BLOB
);

I try to INSERT using "executeBatch"

    fun demo(useBatch : Boolean) {
        val sql = "INSERT INTO my_table (author, book) VALUES (?, ?) "
        val tuple = Tuple.of("john", JsonObject().put("page1", "once upon a time...").toBuffer())
        if (useBatch)
            jdbcPool.preparedQuery(sql).executeBatch(listOf(tuple)).onSuccess {
                println("batch ok")
            }.onFailure {
                it.printStackTrace()
                println("batch failed")
            }
        else
            jdbcPool.preparedQuery(sql).execute(tuple).onSuccess {
                println("plain ok")
            }.onFailure {
                it.printStackTrace()
                println("plain failed")
            }
    }

Steps to reproduce

for reference, plain, non-batch path: "always works".

but not "executeBatch".

first exception encountered:

java.sql.SQLException: Invalid argument(s) in call: putBytes()
	at oracle.jdbc.driver.T4CConnection.putBytes(T4CConnection.java:3617)
	at oracle.jdbc.driver.OracleBlob.setBytes(OracleBlob.java:717)
	at oracle.sql.BLOB.setBytes(BLOB.java:778)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.adaptType(JDBCPreparedBatch.java:130)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.fillStatement(JDBCPreparedBatch.java:102)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.execute(JDBCPreparedBatch.java:55)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.execute(JDBCPreparedBatch.java:38)	

seems to be an "off by one" error here https://github.com/vert-x3/vertx-jdbc-client/blob/4.2.3/src/main/java/io/vertx/jdbcclient/impl/actions/JDBCPreparedBatch.java#L130 ( should be setBytes(1, ...) )

tried to patch that one locally... unfortunately, it did not end there. 2nd exception:

java.sql.BatchUpdateException: ORA-00932: inconsistent datatypes: expected BLOB got NUMBER
	at oracle.jdbc.driver.OraclePreparedStatement.executeLargeBatch(OraclePreparedStatement.java:9711)
	at oracle.jdbc.driver.T4CPreparedStatement.executeLargeBatch(T4CPreparedStatement.java:1447)
	at oracle.jdbc.driver.OraclePreparedStatement.executeBatch(OraclePreparedStatement.java:9487)
	at oracle.jdbc.driver.OracleStatementWrapper.executeBatch(OracleStatementWrapper.java:237)
	at oracle.ucp.jdbc.proxy.oracle$1ucp$1jdbc$1proxy$1oracle$1StatementProxy$2oracle$1jdbc$1internal$1OraclePreparedStatement$$$Proxy.executeBatch(Unknown Source)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.execute(JDBCPreparedBatch.java:58)
	at io.vertx.jdbcclient.impl.actions.JDBCPreparedBatch.execute(JDBCPreparedBatch.java:38)
	at io.vertx.ext.jdbc.impl.JDBCConnectionImpl.lambda$schedule$3(JDBCConnectionImpl.java:219)

Extra

currently on oracle driver 19.10.0.0.0. using pre-existing datasource/ucp-pool. wired into vertx using JDBCPool.pool(vertx, datasource)

( see also thread https://groups.google.com/g/vertx/c/z3a8JCVQT5Y/m/oAnS4ttIAgAJ )

klas3000 avatar Dec 30 '21 10:12 klas3000