How to set up database transactions: what is "DB connection extractor"? Do I need it to set up a database transaction? Why?

I have been trying to understand how database transactions work in Knime.
According to the docs, the “DB Transaction End” node

has 2 input ports. The first one is the transactional DB connection port which should be connected to from the end of the transaction chain. The second port should contain the transactional DB connection from the output of the DB Transaction Start node

My workflow looks like this.
It seems that I cannot connect the DB Writer node directly to “DB Transaction End”, but I need a “DB Connection Extractor” between the two. Is it correct? Why? What does “DB Connection Extractor” actually do? The explanation that it “extracts the output database connection from the input database data connection.” doesn’t really mean much - what does extracting a connection mean, and why is it even necessary?

image

Hi @YetAnotherOne -

You are correct that a DB Connection Extractor is required in this case, as the last node before the DB Transaction End. The extractor is basically converting the port type from a DB Data port to a DB Connection port.

As to the big question - WHY was it implemented this way? - I will have to ask someone internally. :slight_smile:

2 Likes

Thanks. Leaving aside the why, may I suggest the how should be clarified? The documentation says that the first input port of “DB Transaction End”

is the transactional DB connection port which should be connected to from the end of the transaction chain

I find this incredibly confusing. For me, “end of the transaction chain” means the last operation you did on the database, eg a DB Writer node in my case.

Saying something like this:

the first input port must be connected to a DB Connection Extractor node, which in turn must be connected to the last operation carried out on the database (eg a DB Writer node)

would have been much clearer - and would have saved me about an hour of searches and attempts! :slight_smile:

Hi @YetAnotherOne ,

well about way question I can give my opinion with two use cases. (1) in order to finish transaction you have to connect your last node (e.g. DB Writer) to DB Transaction End node. As DB Writer doesn’t have this port you have to either create (add it) it (which means you have to create it for every node that can carry out last operation) or have converter which makes more sense. (2) Another reason to use it is when your connector is hidden inside Component or is far away from node which needs this DB connection. Now you can drag connection through Component or all over your workflow (which can me messy and some are not fond of mess) or you can extract connection from close DB node and use it.

About how and your suggestion. Seems to me that first input port doesn’t have to be connected to DB Connection Extractor node.

DBTransSE

But tnx for suggestion. Have a feeling this needs a bit clarification myself and this topic definitely helped me!

Br,
Ivan

1 Like

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