I am using KNIME 3.2.0 on ubuntu.
I have a workflow where I append a dataset to an existing SQLITE table.
I am using the SQLite Connector node to connect to the database, I use the Database Writer node to append the data, using the "Append data ...to existing table (if any!)" checkbox.
The error I am getting is:
Execute failed: Column ""bebygget areal"" of type "Number (double)" from input does not match type "NUMERIC" in database at position 26.
I think its the same type of error as this one:
Anders E. Andersen
Yes, this ia general problem of SQLite, it doesn't tell the correct type of columns. I can only (again) strongly recommend not using SQLite. Use H2 instead. I have the same problem also with KNIME 3.1 and the old JDBC driver, so this isn't a new issue.
Is this caused by the driver?
SQLite itself shouldn't care what you put into each column, so as long as you have the right column names and number of columns, naively I would think that it should work without problems?
SQLite doesn't care but probably any client program cares and so does almost any other SQL database. We are checking whether the databases' column type matches the one from the KNIME table and issue an error before we try to actually insert the data. SQLite tells us it's an integer column (which is wrong) and we see that the incoming table has a double column and they are not compatible. For SQLite - and only SQLite - you may even be able to insert the double value but all other databases will either error out or truncate the double values when inserting them into an integer column. We simply cannot add all kinds of exceptions and special treatment for SQLite.
I managed to work around this issue by changing column types from Numeric to Real types in the SQLite database using http://sqlitebrowser.org/
Thanks for the assistance.