topic Re: [resolved] How to use SOAP messages to filter Oracle recordset in Talend Studio

[resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Sat, 16 Nov 2024 11:22:37 GMT

Hello, I am new to Talend and trying out ESB for one of our new webservice projects. I have a simple requirement to use a SOAP-call embedded attribute to filter our Oracle return recordset.
Basically, we need the SOAP request from tESBProviderRequest1 to filter tOracleInput or in this testbed, tAcccessInput_1 to return only two records to tESBProviderResponse. I don't know how to extract the “SalesRepId” from tFlowToIterate as a global variable and apply it as a filter or parameter to the database query.
I also tried passing to an tOracleSP transformation to no success. We can't seem to spool multiple records to xml. If anybody has samples of that, would greatly help as well.

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Mon, 29 Dec 2014 04:39:50 GMT

Hi
You need a tXMLMap after tESBProviderRequest to extract the SalesRepId data from the input document, and then you are able to use this data in other components later (use it in tAccessInput_1 as filter condition in your this example), read the tFlowToIterate component manual to learn how to access the data.

Best regards
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Mon, 29 Dec 2014 15:14:25 GMT

Thank you. Somehow this didn't work earlier when I tried it. The tAccessinput schema wouldn't show its schema, just the payload. Now it works after I rebuilt the map (Map 1) per your suggestions and am able to read the global vars. I was also able to make it work via a different map (Map 2) using the Lookup method, but Map 1 is preferred since it will bring less db records to the transformation.
Using Map 1 however doesn't serve up an empty record gracefully. I made the loop esbresponse loop node optional but it gives this when there are no expected records from the filtered recordset.
Can you further advise?
<soap:Envelope xmlns:soap="">
<soap:Body>
<soap:Fault>
<faultcode>soap erver</faultcode>
<faultstring>Could not generate the XML stream caused by: com.ctc.wstx.exc.WstxEOFException: Unexpected EOF in prolog
at : .</faultstring>
</soap:Fault>
</soap:Body>
</soap:Envelope>

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Tue, 30 Dec 2014 03:29:42 GMT

<faultstring>Could not generate the XML stream caused by: com.ctc.wstx.exc.WstxEOFException: Unexpected EOF in prolog

If I understand your problem well, you got this error if there is no records are sent to tESBProviderResponse, if that is the case, you need to create another output table on tXMLMap for tESBProviderFault.

Best regards
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Tue, 30 Dec 2014 04:16:35 GMT

Thank you for your quick response. I did try that tESBProviderFault with my earlier map design using tFlowToIterate and tAccessInput as a second terminal to tESBProviderResponse. It didn't make a difference. My error still came up.
Ironically, my other map which does make use of the Lookup you illustrated actually didn't need this special null row handling. It would just leave an empty SOAP response with no type elements, just the body.
My preferred map is the one with FlowToIterate since we can process the filter in-database. How does one handle empty rows in that map? Adding tESBProviderFault didn't seem to yield.
I attached 4 screenshots but don't see them reflected in my post. Please let me know if you haven't received them.

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Wed, 31 Dec 2014 05:05:13 GMT

Could not generate the XML stream caused by: com.ctc.wstx.exc.WstxEOFException: Unexpected EOF in prolog
 at : .

Sorry, I don't understand what you have explained above, can you add a tLogRow before tESBProviderResponse to see what are the output data after join when you get this error?
Best regards
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Wed, 31 Dec 2014 18:41:28 GMT

Hi, Here's my job run with tLogRow output attached.
tLogRowoutput_from_empty_recordset.txt.txt
tLogRowoutput_from_empty_recordset.txt_20141231-1942.txt

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Fri, 02 Jan 2015 04:45:32 GMT

Hi
From the log message, we can confirm that this error is caused by empty row. To resolve it, you have to check if the recordset extract from access DB is empty or not first, and then, trigger different processing based on the check result. For example:
tERequest-main-tXMap-tFl..rate-iterate-tAinput1-tHashOutPut-oncomponentok-tJava-runIf1-tHashInput-tXMap-tEsbProviderRes...
-runIf2-tFixedFlowInput-tXMap- tEsbProviderRes

tHashOutput: extract the data from DB and store it in memory.
tjava: check if the recordset is empty or not.

int nb_line=(Integer)globalMap.get("tAccessInput_1_NB_LINE");
if(nb_line==0){
globalMap.put("isEmpty",true);
}else{
globalMap.put("isEmpty", false);
}

Set the condition of runif1 as:

!(Boolean)globalMap.get("isEmpty")

\\It means if the recordset is not empty, read the data from memory and output them.
Set the condition of runIf2 as:

(Boolean)globalMap.get("isEmpty")

tFixeFlowInput: customize a output message if the recordset is empty, for example, define one column on the schema, and customize the value as:

"No rows return"

Best regards
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Mon, 05 Jan 2015 18:36:30 GMT

I don't seem to have tHash* components nor for that matter, a Technical Group under my Talend Open Studio for ESB installation. Do I need to install the Data Integration exe separately?

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Tue, 06 Jan 2015 14:54:32 GMT

Talend Data Integration Open Studio doesn't have it either. Is this only available in the Enterprise or Platform offering?

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Wed, 07 Jan 2015 08:23:58 GMT

Hi
Take a look at this KB article to know how activate tHashxxx components.
Best regards
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Sun, 11 Jan 2015 04:45:13 GMT

Hi SHong, Thank you for the detailed guidance. This works perfectly now, handling the null outcome. We may close the issue now.
<soap:Envelope xmlns:soap="">
<soap:Body>
<root>
<result>No rows returned</result>
</root>
</soap:Body>
</soap:Envelope>

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Anonymous — Mon, 12 Jan 2015 01:22:29 GMT

Hi
Great, thanks for your feedback!
Shong

Re: [resolved] How to use SOAP messages to filter Oracle recordset

Tech8 — Wed, 09 Jan 2019 09:37:26 GMT

Thanks so much this solution helped me with the same error.