Parameterized Database Query

KNIME Analytics Platform
1

Hello,

The Parameterized Database Query looks like a useful way of constructing queries.

What type of node does it expect in front of Port 1? I tried a Database Connector node but get this error:

Incompatible port spec at port 1, expected: DatabasePortObjectSpec, actual: DatabaseConnectionPortObjectSpec

What type of node works?

Are there any example workflows?

Best,

Mitch

2

the first port is static data for example, Excel, csv, text files; the parameterized node is used generally is for extract information about values in specific columns of tables

the second port is the conection of database

3

Hi Mitch,

the Parameterized Database Query node allows you to execute a single query with varying parameters. The node expects a dark red database connection as input. These are the database connections that in addition to the connection information also contain a SQL statement that define the data to work with. The Parameterized Database Query node replaces the #TABLE# placeholder with the SQL statement of the input port. Attached you can find a simple example workflow with the node.

Bye

Tobias

2 Likes
4

Thank you very much, Tobias!

That example workflow explains how to use the Parameterized Database Query.

 

Mitch

5

Thanks for this example. It was very helpful. Nevertheless, I cannot manage to achieve what I need: looping on the table partition name to retrieve only a limited set of data from those partitions. It seems KNIME does not recognize the partition keyword in the Parameterized DB Query Reader node.
image

image
image263×581 5.8 KB

image
image1221×738 24.2 KB

I get the following warning and cannot execute the node.
image
image990×25 1.81 KB

How can I proceed?

Thanks in advance.

6

a propper query in the parameterized DB Query Node based on the example wf above goes like this:
SELECT * FROM #table# where “Universe_1_0” > 0.2 and “Cluster Membership” like ‘Cluster_0’

it is a bit confusing for string parametrisation that the columns are writen in " but if you choose ´ for the criteria it works…

7

Hi all,

I am stuck on a similar nuance with the Parameterized Database Query node. I can’t get it to work using $," or ’ in any fashion - I tried to use the example and it seems to work fine in that. Any tips for how to make the following work? I want to filter client_Key from the database using an table creator where input=18 and both are integers:

image
image
image

Error Message:
ERROR Parameterized Database Query 2:870 Execute failed: com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword ‘where’.

Thanks for your help!

8

You need to specify real table name. Also, get read of “” in your query.

1 Like
9

You’re right - thanks! I needed to replace the #table# with the actual table name including the string starting from the server i.e.
SELECT * FROM [COMPANY_EDW].[Fact].[WorkOrders] where “Client_Key” = $input$

Any suggestions for making this work faster? In some cases my parameter list is thousands of rows long…

1 Like
10

There is some difference in functionality but Database Pooping node may work for you.