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.
Appreciate that. I think I’m getting the hang of it, and I can see where it will be super helpful for controlling the execution order of things.
Unfortunately since I have to have 4 separate connections, I couldn’t find a way to accomplish this without creating a bunch of spaghetti lines going everywhere. I compromised by creating a Component that closes two connections at a time and then mirroring that against the top and bottom pair of connections. That lessens the connection lines flying everywhere.
Yes the “spaghetti” is indeed an unfortunate side-effect and it can be a little frustrating when you have a nice clean process, which you then have to litter with all the “control” lines.
There have been a couple of posts in the past requesting “wireless connectors” (a feature seen in a competing product A???x), but sadly this has never gained traction.
You could still reduce your concatenate nodes down to a single node with 4 input ports although maybe it’s neater the way you have it.
I’ve often felt that all the DB nodes should have a pass through DB Session port, which would then make it much clearer what the intent was, and order of processing. You’d get much cleaner workflows like this:
instead of this:
I can definitely see the advantage of wireless connectors. You make a good point about being able to reduce the concatenate nodes down, but I made that choice for the ability to run the DB queries in groups (mostly for testing purposes). It’s a good point and maybe when the design is finalized I will shrink down the nodes, although readability wise it might also make sense to leave it the way it is.
I do also like your suggestion about having pass through ports for the DB Session. It would help the readability. I suppose you could create a component that accomplished that same thing though.
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.
