Parameterized DB Query Reader

Hi,

Can we use a ‘Parameterized DB Query Reader’ node with the SQL provided as a variable and items to include in the where clause provided in the input table?
I’m trying configure this but I’m getting an Oracle error (ORA-00936: missing expression).
When I put the SQL statement in the node, it works fine. Unfortunaltely, my SQL may change based on other input parameters so I really need to provide it as a variable.
I tried to put an equal sign between the sql variable and the column name but I get the same error.
Can you help?
Thanks,
Claire

Hi Claire,

in your case i recommend to use the DB Query Reader Node and inject the final SQL-Statement which you build with e.g. Java Snippet Node

BR
Hermann

Hi,

I cannot because my ids to use in the where clause are read from an input file. Am I missing something?

Thanks,
Claire

Hi,
what do you mean with ids are from an input file? Do you mean database column names and values or only values?

BR
Hermann

only values to be used on the right side of the equal sign in the where clause. (‘select * from table1 where id = xxxx’ where xxxx comes from my input file).
thks,
Cl

preprocess your input data with the group by node no group options but ID in Manual Aggregation (unique concatenate) and Value delimiter (’,’ ) without the brackets.
In this case you can use table row to variable to inject the criteria into your SQL statement
which should look like “select * from table 1 where id in (’$$variable$$’)”Demo

The second option is to loop over your input file with the table row to varaible loop start and inject your id into your SQL statement.

BR Hermann

Hi,
Thanks for the workarounds.
Do you know if the issue I reported is a bug in the Parameterized DB Query Reader node?
Thanks,
Claire

I can’t answer your question because i never used the Parameterized DB Query Reader Node. I prefer the DB Query Reader Node.

BR
Hermann

Ok, I understand.
Thanks for your help,
Regards,
Claire

Hi @Claire,

I used this workflow on the hub from my colleague Tobias Kötter https://kni.me/w/bvaZ-Rd2MhiVL3Gq and modified it to your needs, if I got it right dbLooping_parameterized_flowvar_SQL.knwf (38.0 KB) . The SQL is configurable via a flow variable using the new DB framework.

Best Regards,
Michael

3 Likes

Hi Michael,

Thanks a lot, I have implemented your solution and it works just fine.

Best Regards,
Claire

3 Likes

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