Create connection to a Database / Databse querry.

KNIME Analytics Platform
1

I have been trying to set up a connection to a database so that I can access all of the tables in the databse, but I cannot find any valid SQL command to do that.

I can certainly access one table from my databse, but that is it.

The goal would be to have a single Databse connector and accsess all of the tables from the DB then using different SQL querry (on different tables), obtain the data of interest.

For instance one may want to run a join between two tables, or extract from the same Database different piece of data. Currently the only way that I see is to use multiple instances of the "database reader" node.

Perhaps I am missing something, but it seems that the logic behind having a "Connector Node" is to provide the connection info to the database so that they do not need to be typed multiple times.

Thanks in advance for the help.
2

Unless i mis-understood your query: Surely you can run a join see http://en.wikipedia.org/wiki/Join_(SQL). From a database reader node…?? If this is not the problem please xplain futher?

Chido

3

I'm having a similar situation as gbonamy.

My database has over 150 tables and to read each table I have to create a database reader. Now if I want to switch to a different database, with the same schema, I have to touch all the 150 database reader nodes.

Is there a way to just creat an connection node without an sql statement and then pipe this connection into a database query node?

Cheers, Coalr

4

The database reader and connector nodes need to open the database connection together with a pointer to a table. The solution would be to embed the database reader or connector into a loop and replace the "SELECT * table" field with a flow variable (enable expert mode in knime.ini). The loop start exposes in each iteration a SELECT-query which is then used inside the database reader; the loop end collects the results of each iteration. Hope this helps to get started?

5

Hello Gabriel, sorry i'm newbie. Can you explain step by step how to "embed the database reader or connector into a loop and replace the "SELECT*table" field with a flow variable". Thanks for your answer.

6

hello gabriel
i want to konw how to Put the database read node into the loop, what kind of loop start node is what we need