DB Delete Table Node is not deleting the rows from the SQlite DB

[iCornerstone]

Hi all,

I have an issue with a DB table which does not seem to be updating. This is the process I follow:

• I have an SQlite DB with Lat/Longs which I load into a table (see point 1)
• I then do some processing and I result in a subset of that table (see point 2)
• I then want to remove those lat/long points from the SQlite DB which I believe is achieved with point 3. I know this has worked because in point 3, I can see a table output with about 10,000 rows which I understand should have been the table rows that were deleted from the SQlite DB.
• However, when I re-load the SQlite DB table again in the next iteration of the loop, the DB Reader node outputs the identical number of rows from that SQlite DB.

What am I doing wrong?

Thanks a lot

[takbb]

Hi @iCornerstone , difficult to day without seeing the specific configurations but have you tried configuring the DB Delete (table) node to append “delete status columns” and then checked its output port to see what it says was actually deleted?

Also you could try reading the database again with a DB query and Db Reader node attached directly after the DB Delete to see what the state of the database table is at that point.

Have you double checked all the column names on the input table to DB Delete in case something has changed accidentally?

This from the DB Delete (Table) documentation:

All selected column names of the KNIME table need to exactly match the column names inside the database. Only the rows matching all values of the selected column of any given input row will be deleted.

Could be case sensitivity on column names for example.

[iCornerstone]

@takbb

see what the state of the database table is at that point.

Have you double checked all the column names

Hi takbb,

I did append the delete status columns option in the Node. See below.
I understand from this that there were no issues. Also, the fact that you even have the table created is proof that there was a matching between the selected columns for specific rows (as per the documentation) and these should have been removed.

[mlauber71]

@iCornerstone the status of 0 does indicate that the command was executed successfully but no rows did match the conditions.

A number greater than or equal to zero – indicates that the command was processed successfully and is an update count giving the number of rows in the database that were affected by the command’s execution.

First to check is the conditions. Then you can try and use a DB Connection Extractor – KNIME Community Hub after the writer to maybe have one single line of connections.

Then you could try a join with a column as deletion indicator and see if the conditions do match.

The you should check if you have a recent sqlite driver. And finally you can explore the VACUUM command to compact the database

Maybe you can create an example that demonstrates the problem.