I’m trying to perform a delete on an Oracle Database table.
Let’s call it ‘TEST_DELETE’.
I have a Database Connector, followed by a Database SQL Executor
that contains the SQL statement 'delete from test_Detele “WITH SOME WHERE CLAUSE” '.
This works fine.
However, I prefer to use a Database Delete node, which, If I’m not mistaken,
provides an output with the number of records deleted, so I can store that
in a logging table for later reference.
SO I connect the Database Connector to a Database Delete node,
go to the settings of this delete node and put the table ‘TEST_DELETE’
in the Table name - field.
Nothing happens… I only see ‘No columns in the list’ in the Red Exclude and G reen Include
boxes… I can’t even begin to try and replicate the “WITH SOME WHERE CLAUSE” .
thx for your answer.
But that’s not the issue. The issue is the following : Knime
‘recognizes’ my table TEST_DELETE, when I explicitely perform a delete statement
in a Database SQL Executor, with, indeed, some kind of where clause.
However, when I try to do the same with the database Delete node, this node does
not ‘recognize’ or ‘find’ this table, even though it’s connected with the same database connector. So the list of availabe columns on this table is not loaded.
So writing a delete statement on my table TEST_DELETE in a Database SQL Executor works !
But when providing this table to the database delete node, I get no further than the screenshot.
I’ve got a DB which is called CO8D2, and a user called LAB_SR5210.
The Database Connector (DCo) connects straight to this user.
The table TEST_DELETE is created on this user.
When using a Database SQL Executor (DSE), I link it to the DCo
In the DSE, I simply write ‘Delete from test_delete where kol1 > 10’.
This is just to play around with a few records.
I’ve got a couple with kol1 < 10, and a few with kol1 > 10, so I can clearly see
if the delete works correctly.
And it works as expected. I didn’t need to add the user there, which is not so surprising
considering the configuration of the DCo.
The Database Delete (DDel) is also connected to the same DCo.
In there I added, following your tip, the schema name LAB_SR5210 in front of the table
name, so that the field ‘Table Name’ now contains LAB_SR5210.TEST_DELETE.
Unfortunately, that didn’t have any effect. The boxes where I expect to see the column
names of the table appear, remain empty.
Maybe I’m having a misconception on how this is supposed to work … ?
Knime does some wonderfull things, but it takes a bit of trial and error to see how to
get things done with this or that combination of nodes…
Hey. I am trying to delete all records in a database table. Did you get the Database Delete Node to work? I do not have any columns pop up to select.
I used the Database Table Connector Node and Database SQL Executor with a where stmt. I am not 100% that the timing of the delete is working. I have it connect to a Database Writer. When I uncheck append to existing, it runs fine. However, when I check append to an existing table, I get an error. My belief is that the error is from a primary key violation because the records did not get deleted.
@Piet_Van_de_Weghe and @Momdog598a if I got it right you are not seeing any columns in Database Delete node. The Database Delete node doesn’t show column names from table you have entered rather it shows column names from table you have connected to the data input port of this node. In screenshot it is data from Database Connection Table Reader node and has 5 columns which are show in configuration dialog of Database Delete node. How does your table from input port looks like?
Now I’m going to try and understand how to interpret the ‘Exclude’ & ‘Include’ box .
First question
About the Table Name : As this is not where knime finds the column names (As I know see
and understand), then wat IS the purpose of this field… ?
OK, after finding some youtube video on the database delete node,
I’m starting to understand that I have I completely wrong basic understanding
on how this node is supposed to be used…
I’ll watch that video first a few times
I agree. And that is way using SQL Executor in this case is a good option as well. What you can do is to read table from database from which you want to delete certain rows into KNIME, decide which rows are to be deleted (filter based on some criteria or analysis) and then using Database Delete node delete those rows from database table.
I can draft an example using SQLite database if it will help.
Database Update node works similar which was a bit annoying for my use case.
This is an example of a table with the Delete Node. I am not sure how to setup the node to delete all existing data from my Connected Table or to delete a set of records with a filter. Can you send me any examples of how you would do this?
I’m just starting to wrap my head around this myself…
In your construction, I think you have to replace the star in the db select by explicit columns.
What then happens is that Knime feeds the result of the select statement to the database delete node which then ‘flags’ those records with a delete-status, and then effectively deletes them from
the db. WHICH WOULD MEAN, that in this construction, YOU WOULD DELETE ALL YOUR
RECORDS.
So, in your database reader, you can only select the records that you want to delete in the
delete object… (And I am still trying to understand the include/exclude page myself )
If you are looking to do a 'delete from table where ', I would just write it explitely
in a DB Executor node. This will be much easier with complex where clauses, and also,
everything remains on the database. You don’t even need the Reader node, just a Connector
node, and the Executor.
If I understand correctly, with the Delete node, first the to-be-deleted data gets selected into the Knime memory, and then the delete gets issued on the DB. Maybe not the most performant
method…
I’m a complete beginner in Knime, so make sure to doublecheck my input…
I used Iris data set in SQLite database. Database with data set is included in data folder within workflow attached. There are 3 files (databases) - Iris, Iris1 and Iris2 cause after first running and deleting the rows running workflow again doesn’t make sense so just point to another database. Path to database is workflow relative so it should work straightforward. There are comments in workflow so things should be clear I hope. In case they are not feel free to ask questions.
Could I be so forward as to ask if you have any idea on another open forum question of me :
The short version of this question is actually :
On Oracle, the “Append data” option in the node “Database Writer (INSERT)”,
works fine : most important is that indeed the table is NOT dropped, and the data is inserted.
In Postgres, this fails : it looks as if Postgres tries to create the table again. And because
the flag is set to “Append Data”, the table was’nt dropped…
Also if you are just starting with KNIME (and also meaning with KNIME Database nodes) I suggest you to switch to new database integration nodes. They are (or at least should) improved and do offer more possibilities
.
)
