topic Re: Null Output from tAS400Row using Prepared Statement in Talend Studio

Null Output from tAS400Row using Prepared Statement

PhilipSommer — Fri, 29 Mar 2019 10:31:21 GMT

Hey all,

I'm trying to use the following prepared statement in my tAS400Row in Talend Data Integration Studio component:

SELECT ERSEQ FROM F5747ERR WHERE
			EREDOC = ?
 			AND EREDCT = ?
 			AND EREKCO = ?

However, I'm only getting null values as an output. In order to analyse the issue I tried printing the Query and its' parameters in a tJava and log the row output as such:

The tJava Code looks like this:

System.out.println(((String)globalMap.get("tAS400Row_5_QUERY")));
System.out.println("1: " + ((Integer)globalMap.get("rowOut.SYEDOC")));
System.out.println("2: " + ((String)globalMap.get("rowOut.SYEDCT")));
System.out.println("3: " + ((String)globalMap.get("rowOut.SYEKCO")));

Output from the Java and tLogRow is:

SELECT ERSEQ FROM F5747ERR WHERE  			EREDOC = ?   			AND EREDCT = ?   			AND EREKCO = ?
1: 10019751
2: IN
3: 00700
.-----.
|tLogRow_1|
|=---=|
|ERSEQ|
|=---=|
|null |
'-----'

Now, if I run the same query - with the '?' replaced by the values - directly against the DB, I get the expected results (i.e. a set of numbers).

At this point I don't know what I'm doing wrong. Any help would be appreciated.

Kind Regards,

Philip

Re: Null Output from tAS400Row using Prepared Statement

akumar2301 — Fri, 29 Mar 2019 11:46:16 GMT

Can you please show us the advance setting of Row Component.

Just wanted to see if "Use PreparedStatement" has been well configured.

Re: Null Output from tAS400Row using Prepared Statement

PhilipSommer — Fri, 29 Mar 2019 11:50:50 GMT

There you go. This is also how I configured tAS400Row components with Update statements in the same job.

EDIT:

For clarification, the global variables result from a previous tFlowToIterate which gets the rowOut as input:

Re: Null Output from tAS400Row using Prepared Statement

akumar2301 — Fri, 29 Mar 2019 13:22:54 GMT

I am not sure what is going wrong here , except these globalVariable are null in this component.

Can you please try once to change the query as non-prepared and let me know if these are still NULL in tJava result.

"SELECT ERSEQ FROM F5747ERR WHERE

EREDOC = " + ((Integer)globalMap.get("rowOut.SYEDOC"))

+ " AND EREDCT = '" + ((String)globalMap.get("rowOut.SYEDCT"))

+"' AND EREKCO = '" + ((String)globalMap.get("rowOut.SYEKCO")) + "'"

Re: Null Output from tAS400Row using Prepared Statement

vapukov — Fri, 29 Mar 2019 13:26:55 GMT

Hi,

tDBRow do not propagate resultset by default

at least you need to set a switch in the advanced component settings to "Propagate Query's Recordset".

and you need to use a tParseRecordSet component to further evaluate the RecordSet in your flow.

check some scenarios for MySQL - https://help.talend.com/reader/hm5FaPiiOP31nUYHph0JwQ/61XKWjkH3MqvYNU79nWw_A

but really no reason to use tDBRow for SELECT, for SELECT it more easy to use tDBInput, best chois for tDBRow - DDL, UPDATE, DELETE and etc

Re: Null Output from tAS400Row using Prepared Statement

PhilipSommer — Mon, 01 Apr 2019 12:48:52 GMT

Hey Vapukov,

Thanks for the info. I still cannot seem to get it to work. I have now set up the tAS400Row as follows:

Checked the "Propagate QUERY's recordset" checkmark under adv. settings
Change the ERSEQ column in the schema to type Object under basic setting

I then rout the output into a tParseRecordSet and log the output of that. If I set the output type of the tParseRecordset to be an Integer column, then I get a conversion error from Object to Integer. If I leave the output as an object-typed column I simply get no output rows whatsoever:

I would expect to get a single output row with value "1".

Changing from a prepared statement to one with "inline-values" does not seem to change anything.

Kind Regards,

Philip

EDIT: Is there any way to use prepared statements with tDBInput as you suggest I use that component? I don't really feel like concatenating a SQL statement inline with values I know little about.

EDIT 2: Okay, I got it to work after specifing the correct column name as a value in the attribute table for the tParseRecordset. However, I originally wanted to read the max value of that column. If I change the SQL to "SELECT MAX(ERSEQ) ..." Talend tells me the specified column was not found in the tParseRecordset, despite the traces debug showing the columns' name to be unchanged (i.e. "ERSEQ").