How to use a variable in a sql statement

I have a connection to the database and run an sql script to retrieve data from it.
Now I need to update the sql script everytime. therefor I like to put an variable in the sql script and just have to change the input of the variable to run the script again.

these two nodes I have: “Database Connector” and “Database reader”
In “Database Reader” I have the next sql statement:
select p.barcode
,strack.tiny_01(w.id) as Dest_to_Source
from strack.vsk_wells w
inner join strack.vsk_plates p
on p.id = w.pte_id
where p.barcode = ‘19GROF059’
order by w.row_number,w.column_number.

Now I like to have ‘19GROF059’ as a variable and use another node for input of the variable and then run the sql with the new input value

has anyone an idea how to do this

Hi,
Ok let’s imagine you have something in columns of a table and it contains ‘19GROF059’, ‘19GROF058’, ‘19GROF057’, etc…

You want to use each value to make sql queries. You can use for looping your values
1 - a chunk or a group loop start / end.
2 - Inside the loop a table row to variable
3 - then transform the variable in a query via string manipulation (variable version) (can be sometimes a bit tricky don’t forget the escape character /)
5 - In your database reader you can go in the window “Flow variable” and attribute the “sql_statement” to the value of your variable (the one that contains your query as a variable value in the output port of the string manipulation variable version).
6 - launch your loop.

If you try to do a first knime workflow version explaining where you are stuck. It could be easier to answer more precisely.

In any case I tried to give you inspiration.

Best regards,
Fabien

3 Likes

Hi Fabien,

thanks for your reply.

will give it a try

thanks

Also, you can look at


node specifically designed to accept parameters.
4 Likes

I have tried both options and both solves the problem.
thanks, now I have to solutions

2 Likes

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