DB refresh with Database Reader(legacy)

Hello all,

I would like to ask you about for some hints with respect to the Database Reader (legacy) node and its refresh.

What are the options to refresh the database(in this case I use Oracle) in KNIME and not to do it manually ?

Thank you very much!

Hi @TomS and welcome to the Knime Community.

I have a few questions:
Is there any reason why you are using a legacy node?

What do you mean by “refresh the database”? This usually means to update a database with fresh data, is that what you mean? A Reader (Database Reader) will not update a database, only read.

However, if you meant to refresh the results of the data retrieved by the Database Reader node (and I’m guessing that’s what you meant), I don’t think the node can do that apart from re-running the node, just like most, if not all SQL query tools, where you have to re-run your SELECT statement to see updated data.

Can you elaborate more on your use case? Why do you need the results to be refreshed automatically?

2 Likes

Hi Bruno,

The original question is not relevant any more. But thank you for your feedback!
Actually I would have another one:)

I am wondering what node is the one to be used as an output for a DB joiner node.

My goal: create a new column with an IF THEN condition
In Oracle SQL Developer, I would use the CASE WHEN condition.

Unfortunately a Rule Engine node cannot be used together with DB joiner node. I do not know why

Thank you very much!

Hi @TomS , no problem.

I am not sure I understand your new question…

My goal: create a new column with an IF THEN condition
In Oracle SQL Developer, I would use the CASE WHEN condition.

Do you mean create the new column to your Knime table? This can be done via the Rule Engine node or the Column Expressions node (or via the script nodes - Python, R, Java, etc).

If that was meant to be created on the database side, you can always execute the same statement that you do in Oracle SQL Developer, pure SQL, via the DB SQL Executor node.

Unfortunately a Rule Engine node cannot be used together with DB joiner node

I’m not sure what you are trying to do there. DB joiner node would do a join at the database side. I almost never use the DB joiner node. You can do your join directly in SQL queries using the DB Query Reader node, so whatever you were doing in Oracle, you can write the same queries there.

Hello Bruno,

Yes I want to create a new column to my Knime Table. Ideally with the Rule Engine node. I already know how to use this node for my goal/use case. I did not mean to work with pure SQL on the database side(I know how to do it on the database side but I want to build my use case in KNIME with no SQL query readers, if possible)

So what is my issue. I am using e.g. the DB Joiner nodes, DB Row filters nodes in my use case. There is always a table as an output. But this table(e.g. DB filter node, output port) cannot be connected with the Rule engine node and its input port. I assume the Rule Engine node cannot work together with the DB nodes ?? I am really not sure what is the reason of my issue as I am just a very beginner in KNIME. Hopefully I described my issue properly and it is understandable for you.

Hi @TomS , one comment before diving into the issue: You should use the @ sign and the user name when you want to address a user, that way the user will get notified :slight_smile:

I now understand what you are trying to do. Indeed, the issue is that nodes such as DB Joiner, DB Row Filter, etc, do not have a data port as output (black triangle), and therefore cannot be used with nodes that expect a data port as input such as Rule Engine or String Manipulation.

I think these DB nodes are meant to do operations directly on the db side, that’s why they have these db data input and output ports (burgundy square).

The only node that I know of that takes the db data as input and can output the results as a data port is the Parameterized DB Query Reader, but this node expects and input data that you can usually use as parameters, for example in your WHERE statement (WHERE name = ‘$your_input_table_column$’).

Ideally, if you want to extract data from your db to Knime, it’s better to use the DB Query Reader instead. It takes a DB connection (red square) as input, and will execute the query that you write inside the node configuration, and returns the results as a data table:
image

That sample query is doing what both the DB Joiner and DB Row Filter are doing. The DB Joiner is being done via the JOIN statement on line 3, and the DB Row Filer is basically the WHERE statement on line 4.

And this will return the results as a Knime table, that is via the output data port. You can then plug any nodes with an input data port to it:
image

3 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.