One Star

No rows return from procedure - Unexpected EOF in prolog

Hi everyone again. I am new in Open Studio, so I have many questions Smiley Happy
I created web-service, which calling stored procedure in Oracle and returns records set.
If my procedure returns even 1 row - everything is ok. But when the result is empty - there is a error (in attach).
What shall I do?
Thank you.

  • Data Integration
14 REPLIES
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Please, help me. I can modify a stored procedure, that its return in anyway something, but its a bad decision.
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi, still no ideas?
Employee

Re: No rows return from procedure - Unexpected EOF in prolog

Hi Alexandr
Sorry if this is obvious, but your web service flow needs to return something to the tESBResponse. Right now the tParseRecordSet is not passing anything to the tXMLmap. So naturally the tXMLmap throws an error because there is an unexpected eof. You need to handle the edge condition.
Ed
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Ed, thank you very much! Please, can you give me a simple example? I am very very new in all this Smiley Happy
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Please, help me Smiley Happy
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi Alexander..

if your data flow may return no rows (e.g. they are filtered out), then you could try to use lookups in tmap / txmlmap. That way there is always a row returned, so XML is generated even with empty data
just next time pay attention - you see the "0 rows" at your flow debug information. If you are implementing web serivices using Talend jobs, you will get a row at the start (tESBRequest) and you need to pass a row (one row) to the tESBResponse or tESBFault on the same subjob.
lookup exmaples:
- http://techblog.appirio.com/2009/12/performing-lookups-and-transformations.html
- http://www.talendforge.org/tutorials/tutorial.php?idTuto=9#

good luck
gabriel
Employee

Re: No rows return from procedure - Unexpected EOF in prolog

There are global variables created for each available component. Each global variable includes certain properties depending on the type of component. Lookup the global variables for either the oracle stored procedure or the tParseRecordSet. Use a Run If condition for the connector based on the number of rows return in the record set. If there are no rows returned then throw a SOAP fault. If there are records then just execute as normal. See section 4.3 in the Studio User's Guide, or search for "Run If".
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Thank you for the answers! But I feel myself so stupid?
I decided to use Run If condition, how Edward wrote, and from tParseRecordSet try to connect row (Trigger ? Run If) to tLogRow_4 ? for normal work and to tXMLMap_4 ? for no data found. But I can?t connect this components Smiley Sad
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi Alexander,
I believe you could try the tMap and lookups, I've uploaded screenshots of one of our data services. The iterator component (tFowToIterate) is there so you could parametrize the database output (e.g. as a parameter to your stored procedure). In case no data are returned, you will still got a reply with no data.
As Ed suggested using RunAs looks a little bit more complicated for me, on the other hand it allows you to handle edge situations much better and send faults or do something else if needed.
Gabriel
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi, Gabriel, thanks for answer. I've just do like you, but how can you connect tFlowToIterate and tFixedFlowInput?
I can't do it...
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi alexandr,
1. to connect tIterateFlow to tFixedInput there should be 'iterate' link available from the tFlowToIterate element
2. I wouldn't advice to fork the flow the way you did, I don't know the consequences if it works correctly, just start the lookup flow from the tOraceSP and in the paratemers you can use data passed into the tFlowToIterate element (ctrl-space to popup component elements in the parameter editor). The lookup flow will start when required.
good luck
Gabriel
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Sorry, I can't do it... Too stupid, I think Smiley Sad
The output columns from my stored procedure doesn't match with my input parameter. So I can't join it.
I think there will be a more easy way to resolve problem with 0 rows in output to xmlmap.... I think it is a standard and usual situation in web-services, isn't it?
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hm, I did something Smiley Happy
I added tDie and connected it with tParseRecordSet with condition
((Integer)globalMap.get("tParseRecordSet_1_NB_LINE"))==0
Now, If oracle procedure returns 0 rows, I have a much more better XML output:

soapSmiley Frustratederver
Talend job execution error

And I have a question - can I set in other message? Something like 'no data found'.
One Star

Re: No rows return from procedure - Unexpected EOF in prolog

Hi,
well - your data types should match in data flows..
you should use tEsbProviderFault instead of tDie component where you can specify the fault message..
gabriel