Database reader

Hi everybody!

I'd like to inject a variable in a SQL query writen in the "Database Reader" node.

To create the variable I use the "Java Edit Variable" node with this simple line: return "id_test";

Define variable and string type for return are checked.

The flow variable from the node is connected to the SQL one and I can see the new variable in the flow variable list.

So I write my query:

SELECT ...

FROM ...

WHERE

id=$${Svariable}$$

But, the query seems to be invalid and I have these two warning message:

DatabaseReaderConnection PreparedStatment not support by database: Missing IN or OUT parameter at index:: 1

Database Reader java.sql.SQLException: ORA-00907: missing right parenthesis

Is the problem, the way I create my variable?

I tried to replace the variable by its value and it works.

Thanks

Nico

Hello Nico,

I would try a String Input node connected to a Java Edit Variable connected to the flow variable port of a Database Reader.

The Java Edit Variable node would take the value of your variable id_test to generate the SQL statement. You could then feed this into the "statement" flow variable of your Database Reader.

There may be more elegant ways to do this, let me know what you think.

Hope this helps,

Fred

Hi Nico, I am away from a computer at the moment, so can’t easily check this - but from memory it looks like the sort of error you get when quotes are missing(?). You could try simply adding single-quotes around the variable in the SQL.
When you say it works if you add the variable value manually, are you quoting this? (id=‘id_test’)

Kind regards

James

Hi Fred, hi James,

Thanks for your help.

@James: Indeed the variable required the use of simple quote around it in the SQL query, as if it is a value.

@Fred: Actually, using only the "String Input" node is enough because this node does the same thing than the Java code.

Thanks again.

Nico