LAE

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

Originally posted by: SteveAkers

I have a list of strings that I want to include as a parameter to my JDBC query.

For example:
Select *
from Table
where ColumnA in (?)

The "?" will be replaced by the contents of the SqlQueryFieldBindings variable.

For example:
SqlQueryFieldBindings = 'abc','def','ghi'

the ? is expected to be expanded to 'abc','def','ghi'

What the node is actually doing is: ''abc'',''def'',''ghi''
(these are two single quotes, not a single double quote)

so the SQL now goes off the DB engine looking like:
select *
from Table
where ColumnA in (''abc'',''def'',''ghi'')

which in turn returns unpredicable results or usually no data at all.

I have tried sending a string with just commas with no quotes around the strings and end up wih 'abc,def,ghi'. (the node quoted the entire string)

This is an extremely powerful capability that I would LOVE to use, so any input would be greatly appreciated.

Lavastorm 4.5.2

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

Originally posted by: rboccuzzi

We have seen something like this before Steve, and I believe it was the JDBC driver that was the issue. Can you tell me what DB you are trying to access, and what version of the driver you are using with the JDBC node? If I recall, this was solved by updating to the latest driver when we last encountered the issue.

To give a little more info, we are taking a query of the form:
select * from someTable where someColumn = ?
and passing that along to the driver exactly as is, with a string typed variable (when the bind variable is typed as a string in our data).

We correctly do not modify the select statement to add any quotes around the bind indicator (?), as that would indicate that we were looking for a comparison with the string containing the single character �?�.

The driver is given the bind values, the statement, and the types, and should perform the correct substitution for it.

Cheers
Rich

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

Originally posted by: SteveAkers

I am trying to access a Netezza Twinfin V6.0.3

On a lavastorm server linux 64 bit VM running redhat enterprise 5.4.

The netezza driver install file version is "npsclient.6.0.3".


So what I am trying to do is take a list of alphanumeric accounts output from an earlier node, reduce them to a single comma delimited list and send that single string to dynamically populate an "IN" statement in the aquisition node.

If you have an elegant solution, I'm all ears

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

Originally posted by: rboccuzzi

Whoops, I mis-understood what you were trying to do the first time, sorry about that! Now that you restated what you were trying, I see the problem. The bind position is typed (in this case as a string), and you are trying to put a LIST of strings into that single bind position, which will be treated like a single string, which is what you are seeing.

The way you are attempting to do this, unfortunately, won't work, due to the way JDBC drivers are structured. However, there are a few other ways you can do this. You could dump the table, and do a lookup or join or filter in the LAE directly, which I would recommend is the easiest and most direct route. Another way, you could use a JDBC node, but have each value you are looking for be a single row, and re-execute the query for each value. So instead of doing an "in", you just do repeated queries. Another way could be to create a temporary table, and put your data in that temp table, and then do a select using that table as well. Without knowing the sizes of your data and your infrastructure, I can't say which is better for you, but these are all workable solutions.

Let me know if this helps or if you have further questions
Cheers
Rich

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

Originally posted by: SteveAkers

Its too bad that SQL can't have a different delimiter for lists that would not confuse the driver.

Or is there a way to pass a list to the driver through a custom node??

I would ultimately love to just connect the output of one node to another aquisition node and instruct it to either implicitly load/join to a temp table or embed in the "IN" syntax of the SQL.


Netezza will allow temp tables to be created, but they last only for the session and from what I can tell, there is a single session for every individual JDBC node. If there was a way to persist a session across multiple nodes, maybe by clocking them together or something like that, I could create a temp table, load it with what I needed, run the aqusition node with the join to the temp table and then clean up. But from what I can tell, there is no way to send multiple sql statements in a single JDBC call or persist a session across nodes... Any ideas??

Working with a DBA to get a read/write table built for supporting joins in the data warehouse is a time consuming thing that takes a lot of planning and is not very versitile, leveraging session based temp tables would be an elegant solution if it was possible...

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

Originally posted by: rboccuzzi

I don't believe you can do what you want...if you do it programmatically, you end up with an item in the IN which is an "array", and it looks for a matching "array" not the values in it. The best ways, unfortunately, are the ones listed above.

Cheers
Rich