Hi,
I’ve got a problem when i use the DB reader node.
Error message : “ERROR DB Reader 0:479 Execute failed: Data truncation”
I’ve never had this problem in the past. I’m using the microsoft SQL Server Connector, Maybe i’m not using the right driver ? Does anyone know how to solve this problem ?
Thanks,
Loris
Hi Loris,
usually the Data truncation error only appears when someone writes a string which is longer then the maximum size of the column in the database. Can you have a look in the KNIME log file if you can find further information. Also it would be interesting to know the database types of the columns you try to read.
Bye
Tobias
Hi Tobias,
Thanks for your answer. The database type is microsoft SQL server, so i’m using the right connector node. This is what i found in the log file :
2019-07-10 16:13:36,783 : ERROR : KNIME-Worker-9 : : Node : DB Reader : 2:483 : Execute failed: Data truncation
java.sql.DataTruncation: Data truncation
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:386)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
at net.sourceforge.jtds.jdbc.JtdsResultSet.(JtdsResultSet.java:149)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
at org.knime.database.connection.impl.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.impl.AbstractStatementWrapper.executeQuery(AbstractStatementWrapper.java:92)
at org.knime.database.connection.impl.monitored.MonitoredStatement.executeQuery(MonitoredStatement.java:81)
at org.knime.database.agent.reader.impl.DefaultDBReader.read(DefaultDBReader.java:160)
at org.knime.database.node.io.reader.DBReadNodeModel$1.runFinal(DBReadNodeModel.java:157)
at org.knime.database.node.io.reader.DBReadNodeModel.execute(DBReadNodeModel.java:171)
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)
As you said i think the problem is that someone wrote a string that is longer than the maximum size in the column database. Is there a way to get through this error whitout any modifications on the database as i only have read access on it…
Thanks for your help,
Loris
Hello Loris,
you are using the jTDS driver. So your problem might be related to this post regarding handling of TINYINT types. Maybe you can try the Microsoft SQL Server driver. To use this driver you have to install it separately in KNIME due to license restrictions. To do so open KNIME and go to File->Install KNIME Extensions… In the window search for SQL. Then select the KNIME Microsoft JDBC Driver for SQL Server extension. Click Next etc and restart KNIME at the end.
Once KNIME has restarted you should be able to select Official Driver for Microsoft SQL Server in the Driver Name option.
If this doesn’t help and it is related to a numeric column you could try to cast the column before reading it into KNIME via the DB Query node.
Bye
Tobias
Hi Tobias,
Thanks for your answer. I installed the microsoft SQL Server Driver correctly but i still have an issue related to Data Type. This is the message error :
ERROR DB Reader 0:483 Exception checking for more rows from database: Arithmetic overflow error converting expression to data type datetime.
ERROR DB Reader 0:483 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Arithmetic overflow error converting expression to data type datetime
I tried to cast the data with datetime type using the DB query node but still have the same error. The DB query node give this result where Dateold is the Date with Datetime type and Datenew is the Date with VARCHAR type.
Any idea to solve this ?
Thanks,
Loris
Hi Loris,
sorry for the late reply. Can you tell me which data type the datenew column has in the SQL Server? You can see the data type after the DB Table Selector node in the “DB Spec” tab of the out port view. Also can you include the query that you execute in the DB Query node.
Thanks
Tobias
Hi Tobias,
Also sorry for the late reply but it seems that the problem has disappear i don’t know why… I think the topic can be close.
Thnaks for your help,
Loris
Hi Loris,
thanks for getting back on this. Let us know if the problem occurs again.
Bye
Tobias
This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.
