I’m trying to implement a DB Connection Closer node and using the information at the below resources I felt like I duplicated their logic but it still is not working. The workflow itself executes as expected, but the DB Connection Closer node never executes. I need some help understanding why it’s not.
https://forum.knime.com/t/how-to-use-database-connection-closer-node/20940/1
https://hub.knime.com/ipazin/spaces/Public/2020_02_14_DB_Connection_Closer_Example~9q7Ks6grrZmtzS-j/current-state
Hi @Mark_White , welcome to the KNIME community forum.
In the example flow that you referenced, this would close the connection as it suggests, but possibly only if (all) the workflow nodes were selected using “select all” and then executing. With no node downstream of the connection closer attempting to execute, there is nothing to force the connection closer itself to execute which is what you are finding.
Technically, to force the DB Connection Closer to execute, the demo workflow would be coded as:
So, in your workflow, to force the execution the DB Connection Closer node in this way, you can attach its “downstream” flow port to another node that will be executed:
e.g. I’ve mocked it up using an H2 database in place of Oracle:
But… this would only have the effect of closing the last of your 4 db connections, leaving the other 3 sessions open, until such time as either the DB Connector node is reset, or the connection expires/network is chopped etc.
Is this what you intend?
To close all of the sessions, you’d need to do similar for each of them.
Some other observations…
Firstly, you are chaining the Concatenate node. It’s not that obvious, but you don’t need to. In “modern UI”, which is what I think you are using, you can hover the mouse over the input data ports of the Concatenate node and then click the “+” that appears, to add further input ports.
Add as many as you need.
(In “Classic UI” which is what my screenshots are from, you can click the three dots on the Concatenate node and you can add extra ports):
If you want to close all of the connections, you could do something like this (which I admit looks a bit busy 
)
You could also have all the queries execute in sequence from the same database connection, but this depends on whether you are intentionally wanting to execute them in parallel which is what you have at the moment. Maybe it’s for improved performance and you don’t mind having additional sessions open.
For example, here the flow variables from one Query Reader to the next ensures the sequence of execution of the queries. (As the DB Connection Closer is free to execute as soon as the 4th DB Query Reader has completed, we don’t want that query completing ahead of one of the others!)
A final observation (and this depends on whether the queries are all actually “union compatible” with each other (i.e. do they all return the same set of columns) is that if what you are doing is reading data from the same database and simply concatenating the result, this might be better done within the database, in which case you could swap the DB Query Reader nodes with “in database” nodes as follows:
In this example, you could put the queries from each of the DB Query Reader nodes as “custom queries” in the DB Table Selector nodes. The DB Concatenate will form a “union” in the SQL sent to the database and the DB Reader will extract the result. This then becomes a single database query with the database performing the heavy lifting of the data concatenation. Again, the DB Connection Closer is wired to occur after the final database action and is forced to execute by being linked to a downstream node.
I hope that helps you with your question, and gives some additional insights.
Ah… thank you so much. I had downstream nodes I didn’t want to execute, so I wasn’t doing an Execute All. I understand what you mean that this node was not forced to run because nothing was connected to it downstream.
As to the DB connections, I have 4 of them because there are 4 physically separate DB’s. So unioning isn’t an option, but it also provides parallelism which is nice.
I’m new to Knime and while I’ve picked most things up naturally, the flow variable links have been a bit challenging to grasp. I appreciate your detailed answer, it was very helpful!
No problem. Glad it helps. I had assumed they were the same database because of the single credentials, but fair enough if they all have the same. I must confess I often don’t use the in-database nodes even when I can, because sometimes I find it is slightly slower but simpler to conceptualise, and in other cases KNIME is actually faster than doing it in the database, so its very much down to your individual use case anyway, but thought I’d put it out there.
Flow variable links are one of those things that aren’t immediately obvious, but the main thing to note is that they can be used to guarantee the order of processing where it would otherwise cause problems (such as closing a db connection too soon!), but if you have any questions then ask away as the forum is very well frequented.
Welcome to KNIME once again.
