Database reader does not work with SQLite

KNIME Analytics Platform
1

Hi,

Since the 2.4 release the database nodes do not work with sqlite databases. The error is:

 

ERROR DatabaseReaderConnection SQL Exception reading Object of type "2004": not implemented by SQLite JDBC driver - all further errors are suppressed and reported on debug level only

ERROR Database Connection Reader Execute failed: Cell at index 58 is null!

 
The database per se can be opened read and queried with several database tools (CP Analyst, SQLitemanger, DbVisualizer) and checks OK upon verification. The drivers of these tools were used and still the same error lines pop up. The database reader of 2.3 worked without problem.
 
Thanks,
2

Thanks for reporting this problem. Can you please check the knime.log in your <KNIME_ROOT>/workspace/.metadata/knime if you can find any exception related to this problem?

3

I had the exact same problem but with MySQL. I am using mysql-connector-java-5.1.17-bin.jar

Working with NULL is a pain that has to be handled. Following the most logic by using "SELECT * FROM database_table WHERE table_column IS NOT NULL" in the database reader node did not do the trick.

A fair guess is that the node is very strict by following the settings in the database for the table, and my column that sometimes is empty even though it is set to "NOT NULL" is a timestamp.

I tried ""SELECT * FROM database_table WHERE timestamp_column != '0000-00-00 00:00:00'"

Works as a clock even thought the expression may be formaly wrong...

PS

4

This problem still exists in the Database Reader: "Execute Failed: Cell at index xx is null!"

KNIME Version: 3.1.1
MySQL Driver: mysql-connector-java-5.1.37-bin.jar

The problematic cell is a null DATETIME though weirdly other rows can sometimes be read even when this field is null.

This is from the log file:

2016-03-28 14:03:41,484 : ERROR : KNIME-Worker-41 : Database Reader : Database Reader : 0:299:1135 : Execute failed: Cell at index 18 is null!
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : Database Reader : Database Reader : 0:299:1135 : Execute failed: Cell at index 18 is null!
java.lang.NullPointerException: Cell at index 18 is null!
	at org.knime.core.data.def.DefaultRow.<init>(DefaultRow.java:98)
	at org.knime.core.node.port.database.reader.DBRowIteratorImpl.next(DBRowIteratorImpl.java:314)
	at org.knime.core.node.ExecutionContext.createBufferedDataTable(ExecutionContext.java:245)
	at org.knime.core.node.port.database.reader.DBReaderImpl.createTable(DBReaderImpl.java:242)
	at org.knime.core.node.port.database.reader.DBReader.createTable(DBReader.java:115)
	at org.knime.base.node.io.database.DBReaderNodeModel.getResultTable(DBReaderNodeModel.java:150)
	at org.knime.base.node.io.database.DBReaderNodeModel.execute(DBReaderNodeModel.java:127)
	at org.knime.core.node.NodeModel.executeModel(NodeModel.java:563)
	at org.knime.core.node.Node.invokeFullyNodeModelExecute(Node.java:1146)
	at org.knime.core.node.Node.execute(Node.java:933)
	at org.knime.core.node.workflow.NativeNodeContainer.performExecuteNode(NativeNodeContainer.java:556)
	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)
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : WorkflowManager : Database Reader : 0:299:1135 : Database Reader 0:299:1135 doBeforePostExecution
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : NodeContainer : Database Reader : 0:299:1135 : Database Reader 0:299:1135 has new state: POSTEXECUTE
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : WorkflowManager : Database Reader : 0:299:1135 : Database Reader 0:299:1135 doAfterExecute - failure
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : Database Reader : Database Reader : 0:299:1135 : reset
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : Database Reader : Database Reader : 0:299:1135 : clean output ports.
2016-03-28 14:03:41,484 : DEBUG : KNIME-Worker-41 : WorkflowFileStoreHandlerRepository : Database Reader : 0:299:1135 : Removing handler a1b5b0fb-a868-4bf2-865b-1a392185a045 (Database Reader 0:299:1135: <no directory>) - 50 remaining

 

5

I discovered the underlying problem. In some cases when I load data into the database and I don't have a DateTime I set the DATETIME field to be ZERO. In similar cases I set the DATETIME to be NULL. In both cases the queried data looks to be {null} but in actual fact is only truly NULL in the second case. This query correctly makes the distinction:

SELECT * FROM MyTable WHERE StartDate IS NULL;

When the ZERO values are masquerading as NULL values the KNIME Database Reader throws an error. To fix the problem I had to run this query:

UPDATE MyTable SET StartDate = NULL WHERE StartDate = 0;

 

6

The summer release will bring H2 support. This is also an embedded database but much more reliable and advanced than SQLite.

7

Hi Thor,

I just installed KNIME 3.2 and faced same problem when I run my database reader node that connect to SQLite Connector node as shown in figure below. I used to run the same workflow using KNIME 3.1.2 without any problem. Could you please advise. Thank you.

8

Hi saip,

This is because we updated the sqllite version and it seems some options are no longer supported. We will fix this as soon as possible. Sorry for the inconvenience.

Best, Iris

9

Can you be a bit more specific about the error? What type does the second column in your table have? Are there any unusual values in the column?