Being a total newbie to iWay I'm taking baby steps here. Having read all of the online help files, manuals, and anything else I could get my hands on I'm stymied on some seemingly very basic questions.

I am trying to create a process flow in the Designer that will ultimately be a webservice. It will need to receive a parameter from the calling process, store that parameter then use it in an sql statement. I had thought that creating a service to get and store the parameter in a global variable then using the ?batchID in the sql statement would do the trick; but I am getting an sql statement error of invalid char, and viewing the logs shows that the substitution is not being made. Any suggestions?????

Hi,
How are you passing the value to your pflow, before storing it as an SREG? I'd say check the log and see if the parameter is getting populated.
I believe that a pflow that's published as a web service needs schemas on the Start and End objects to define the input XML from the calling process, and the return XML.

Right, that's what I thought as well. One thing I did find was the cause of the invalid char error. I craft and test the sql statement in toad so I can see the results ahead of time. In toad (and Oracle) I need to end the statement with a semi-colon, so when I paste the sql into the the sql object of the process flow the semi-colon comes along for the ride. Turns out iWay doesn't like that much, since it places its own semi-colon at the end of the statement, which cause Oracle to puke since the second semi-colon now becomes invalid.

For the variable I'm trying a schema now. We'll see how that goes. I have another issue if someone could help out. I need to create a xml file in a header child relationship, but can't get a handle on how to craft the process flow to build the file.

Thanks a good spot, I'll bear it in mind in the future.

I'm not sure I understand your question? You have the xml schema layout, but don't know how to do what in the pflow exactly?

Here's the scenario of what I am trying to do:
I get a batch ID from a java process passed into iWay via webservice, execute a sql which returns 1..n rows, for each of these rows I need to retrieve header info, I then need to process each of these rows to retrieve detail lines. So the final xml should look something like

So I need to know 1) getting the batch id from the webservice call, how to iterate thru each row(s) returned to execute the details sqls.

OR, if a schema is defined for the output xml, can I simply execute one sql that returns everything and then use a transform to create the final xml file?

Ok, I'd say a transform with the SQL output as it's input and the web service response schema as its output would be your best bet, as transformer will iterate through the input XML and generate according to the schema.

And that's what I aimed for. I was able to generate output of the sql w/out the transform and create an xml doc. From that I created a transform in the transformer, tested it out and got the expected results in the test output. BUT, when I add that transform to the process flow (after the service object, before the file object) I only get one row of output. While it is in the expected format (minor victory) I kind of do need oll of the data.

Has any one else seen this?

One thing to check is the 'looping' property on your parent tag in transformer.
Are you seeing this when you run on the server, or when you test in designer?

Getting closer

Of course, another thing to check would be that the output from your SQL when you test does in fact contain multiple rows i.e. multiple child XML segments.

I know the sql returns multiple rows, since the same sql witnout the transformer builds an xml file with all the expected rows. Put the transformer in and only one is returned

Well, if the transform works when you test it in transformer, and I assume you're doing a test -> run in designer, I'd be tempted to publish to your iSM and test it there, to validate the results.