SQL script with multiple DB data outputs

[Pavlov_Alexey]

I have a complicated sql query script, which I want to execute on several dbs output. But sql executor can take only one db data.
Can someone help me with this?

[takbb]

Hi @Pavlov_Alexey , welcome to the KNIME community

I’m assuming that each of those DB Writers is writing to a different table. Is the problem that you are wanting them to all be able to write at the same time? In the above screenshot, only one of your DB Writers can write at any given moment, and the others will be blocked until it completes. This is a feature to my knowledge of all relational databases, as on any one connection there can be only a single query running at one time. It would be difficult/impossible to allow multiple concurrent processes within the same connection and yet maintain proper transactional state within the database.

I believe you can have more than one connection open to an H2 database at the same time, so if none of your database inserts are dependent on any of the others, you could increase the number of H2 Connector nodes, and attach a DB Writer to each. What happens if you do that?

Is that your actual question? It wasn’t clear. You mentioned a “complicated sql query script”, but haven’t given any details about that.

[Pavlov_Alexey]

Right now I came up with this solution. I create local database and then connect to it. And so I can run query.
But if there’s more simple solution. let me know/
Thanks

[Pavlov_Alexey]

Thanks, it’s useful to use multiple connectors.
Actual problem is that I have query that joins three of output db tables.
So I want to make something like this:

But cannot connect several db data outputs to one query executor

[takbb]

Hi @Pavlov_Alexey ,

You seem to be wanting to join tables using SQL Executor, but that isn’t what SQL Executor is used for. Which is why I’m confused about what you are trying to do.

If your data is in spreadsheets, then putting them into a local database to query them, as you are doing, is a reasonable way forward, but then you’d normally query them using something like a DB Query Reader, or with the “in database” nodes. But equally, you could just use KNIME nodes to read the spreadsheets and join them using multiple joiner nodes or other mechanisms.

But from what you said, putting the data into an H2 database appears to be a workaround for some bigger problem you are facing, yet you haven’t described really what this problem is. Can you take a step back and give more detail about the overall problem you are trying to solve?

[hmfa]

Hi.

I think each DB writer node must create one table and must not be executed in paralel, but in sequence.

So, split your SQL code. Each step (DB writer) must create one table.

Step_one creates table t1
Step_two creates table t2 (depending on t1)
Step_three creates table t3 (depending on t2 and t1.)
etc.

And the sequence of execution must be granted by variable ports.

Variable outport of step_one connects to variable inport of step_two.
Variable outport of step_two connects to variable inport of step_three
etc.

This assures the sequence of execution
Please try it.
Regards

[mlauber71]

@Pavlov_Alexey there is this example

multiple joins

I set up a demo how this could be done in KNIME although you could do that in consecutive steps or directly in a SQL database (as has been mentioned) but it is possible. Here I use SQLite which I like because it is a ‘real’ SQL database but you can just have it within one single file (you could switch that out for any SQL database). The Join itself is done in the SQL Executor node. Plus: you have it in one step. The downside could be it might get a little bit confusing with all the steps, but y…

SQLite - multiple joins with KNIME – mlauber71