LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: e0055348

I have an agg node that builds a complete query then passes to DB Execute where I have sqlSelect(1, finalSql) That works fine using OCI and Oracle.

finalSql looks similr to:
select Col1 from table1 where Col2 in('a','b','c'), etc.

I need to do something similar using JDBC with Netezza and cannot find a way to pass a prebuilt query string to the JDBC Query node. JDBC Execute does not allow the sqlSelect command and does not allow output.

I can use JDBC Execute to build a temp table, then use JDBC Execute to do inserts, then use JDBC Query to run the query I am looking for but there must be a better way.

Thank you

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Good Day,

Unfortunately, while the DB Execute node does have the ability to directly access its node input to set values in your sql query (or the whole query itself):

sqlSelect(1, 'QUERY')
#where QUERY is a string field on an input actually containing the sql query.

The JDBC nodes do not have that same ability. So you cannot dynamically construct an entire query and pass it diectly into the JDBC nodes.

You do have some ability to parameterize your JDBC query, however, using the special '?' character substitution for the SqlQueryFieldBindings node parameter. Please see the attached example graph.

Query nodes will run their queries once for each attached input row. Using the substitution character, you can write a query and make some changes to your selected values based on those input records. The attached example makes use of this for 2 fields. (Please note that it was setup specific to a test environment , so it won't work if you just try and run it, but syntactically it is correct so you can use it as a guide)

An enhancment request has been put in to allow for direct access to input fields similar to the DB Execute node.

Regards
Timon Koufopoulos
MDA Support
Attachments:
JDBC Query example.brg

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: yossi

Is it possible to get the JDBC node to output more than one row, for each row of input?

AKA, in your example

"select * from bar where tn = ? or source = ? ",
it seems if there are multiple matching phones, or otherfields, it seems like it won't emit anything?

That is what I am seeing, and in the Node Help it says:


Output(s):
Current: 1
Minimum: 1
Maximum: 1

is that editable somewhere?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: maria.pislopez

Hello Yossi,

It is possible to output more that one row for each matching input field.
The query

Select * from bar where tn = ? or source = ?

will output all records that match all the input tns and sources.

I've attached a sample graph for you. In the graph, all records matching custname Nancy Jesse and Garnet Elle and accID 1000000103 and 1000000104 are outputted which was 5 records in my case.

Hope this helps. If you still can't get it to work, let us know.

Good luck!
Maria
Attachments:
JDBCEx.brg