Problem writing data to Sybase

[marijn]

Hi,

I have been succesfully using a JDBC driver for Sybase for a while to read data.
However, writing data results in an error.

Error while adding row #(Row0), reason: JZ0SA: Prepared Statement: Input parameter not set, index: 0.

I enabled the JDBC logging in the Advanced section of the Database connection and checked the log file.
The result is:

2019-10-11 14:49:21,401 : ERROR : KNIME-Worker-28 : : Node : DB Writer : 0:105 : Execute failed: Error while adding row #(Row0), reason: JZ0SA: Prepared Statement: Input parameter not set, index: 0.
java.sql.SQLException: Error while adding row #(Row0), reason: JZ0SA: Prepared Statement: Input parameter not set, index: 0.
at org.knime.database.agent.writer.impl.DefaultDatabaseWriterExecutor.handleErrors(DefaultDatabaseWriterExecutor.java:476)
at org.knime.database.agent.writer.impl.DefaultDatabaseWriterExecutor.executeInSingleMode(DefaultDatabaseWriterExecutor.java:173)
at org.knime.database.agent.writer.impl.DefaultDatabaseWriterExecutor.execute(DefaultDatabaseWriterExecutor.java:102)
at org.knime.database.agent.writer.impl.DefaultDBWriter.insert(DefaultDBWriter.java:185)
at org.knime.database.node.io.insert.DBInsertNodeModel.executeWriter(DBInsertNodeModel.java:94)
at org.knime.database.node.io.write.DBWriteNodeModel.executeWriter(DBWriteNodeModel.java:177)
at org.knime.database.node.io.insert.DBInsertNodeModel.executeWriter(DBInsertNodeModel.java:1)
at org.knime.database.node.io.DBManipulationNodeModel$1.runFinal(DBManipulationNodeModel.java:390)
at org.knime.database.node.io.DBManipulationNodeModel.execute(DBManipulationNodeModel.java:337)
at org.knime.database.node.io.write.DBWriteNodeModel.execute(DBWriteNodeModel.java:131)
at org.knime.core.node.NodeModel.executeModel(NodeModel.java:567)
at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1192)
at org.knime.core.node.Node.execute(Node.java:979)
at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:559)
at org.knime.core.node.exec.LocalNodeExecutionJob.mainExecute(LocalNodeExecutionJob.java:95)
at org.knime.core.node.workflow.NodeExecutionJob.internalRun(NodeExecutionJob.java:179)
at org.knime.core.node.workflow.NodeExecutionJob.run(NodeExecutionJob.java:110)
at org.knime.core.util.ThreadUtils$RunnableWithContextImpl.runWithContext(ThreadUtils.java:328)
at org.knime.core.util.ThreadUtils$RunnableWithContext.run(ThreadUtils.java:204)
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
at java.util.concurrent.FutureTask.run(FutureTask.java:266)
at org.knime.core.util.ThreadPool$MyFuture.run(ThreadPool.java:123)
at org.knime.core.util.ThreadPool$Worker.run(ThreadPool.java:246)
Caused by: java.sql.SQLException: JZ0SA: Prepared Statement: Input parameter not set, index: 0.
at com.sybase.jdbc4.jdbc.SybConnection.getAllExceptions(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.handleSQLE(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.sendQuery(Unknown Source)
at com.sybase.jdbc4.jdbc.SybPreparedStatement.sendQuery(Unknown Source)
at com.sybase.jdbc4.jdbc.SybStatement.executeUpdate(Unknown Source)
at com.sybase.jdbc4.jdbc.SybPreparedStatement.executeUpdate(Unknown Source)
at org.knime.database.connection.impl.AbstractPreparedStatementWrapper.executeUpdate(AbstractPreparedStatementWrapper.java:104)
at org.knime.database.connection.impl.AbstractPreparedStatementWrapper.executeUpdate(AbstractPreparedStatementWrapper.java:104)
at org.knime.database.connection.impl.logged.LoggedPreparedStatement.executeUpdate(LoggedPreparedStatement.java:239)
at org.knime.database.connection.impl.AbstractPreparedStatementWrapper.executeUpdate(AbstractPreparedStatementWrapper.java:104)
at org.knime.database.connection.impl.monitored.MonitoredPreparedStatement.executeUpdate(MonitoredPreparedStatement.java:161)
at org.knime.database.agent.writer.impl.DefaultDatabaseWriterExecutor.executeInSingleMode(DefaultDatabaseWriterExecutor.java:152)
… 21 more

One of the problems might be the database dialect (I do not know how to get the SybaseDialect or SybaseAnywhereDialect)
Does anyone know a solution on how to write a table to Sybase IQ?
Thanks in advance

[tobias.koetter]

Hello marijn,

this looks like a problem with the driver which seems to use 0-based indices when using batch insertion whereas the JDBC specification uses 1-based indices. For an example see documentation of the parameterIndex for the setString() method. For more details see this bug ticket. I couldn’t find any documentation about this so please ask the Sybase support for further details on this problem.

Since all manipulation nodes use prepared statements and batch execution for performance reasons the only workaround you can use to write data is to compose an “insert into” statement with the values hard coded into the statement and then executing each statement with the DB SQL Executor node.

Bye
Tobias