vertx-jdbc-client
vertx-jdbc-client copied to clipboard
batch execution involving BLOB not working for oracle
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 )