JDBC response requirement

[Former Member]

hi

we are triggering a JDBC stored procedure using MsSQL where we select a name based on the location that we get in the source file.

the structure of the response MT is like below:

<MT_response>

<row> 1..n

<Name> 0..1

what we require is ..that even if the DB doesn't return any rows from a particular location, the response should have the row field nonetheless with the Name field missing.

for example the source file has 2 cities - london and delhi

the Stored proc resultset has 4 names for london and none for delhi.

so we want that 4 rows with Name field should come in the MT_response corresponding to london

and 1 row with no Name field should come in the response for delhi.

please help.

[former_member206760 · Accepted solution]

hey itisha,

What you can do is even though u are not gtting the ROW for delhi...

you can write your manipulations in a Response mapping that you will have to develop

Edited by: Tarang Shah on Mar 19, 2009 9:25 AM

[Former Member · Accepted solution]

Hi tarang

can u elaborate on ur idea of writing manupulations in the response?

[former_member206760 · Accepted solution]

Hey itisha,

You will have to convert your MI to Outbound sync for file and inbound sync for jdbc.

Now if you see the interface mapping between this two interfaces .. it will have both request and response mapping..

request mapping is the normal that we use...but after you get the response from jdbc...you do not want the ROW node to go away....then you add a response mapping also where you can do all this kind of transformations

and then send the result file to a folder..

Settings given in the below Wiki will also need to be done...only difference is you scenario is Fileasync to Jdbc sync

but the one given here is File async to RFC sync ...concept however of response mapping and sending response to async file system will remain same

https://www.sdn.sap.com/irj/scn/wiki?path=/display/xi/file-rfc-file%2528without+bpm%2529

[former_member200962]

Hi,

In your mapping for the required target nodes use following logic:

Source Node (city-entry)--> exists --> createIf --> Target Node(row)

this will create 4 rows...one for each name field...

and for the NAME part map it directly to the corresponding source node....

So Name field wont be present if source does not have any corresponding value...however your row field will always be there as creatif creates an empty node....

both exists and createIf are standard functions available in Message Mapping.

You may need to set appropriate Context on the source node in the mapping to achieve this....not a complex

Regards,

Abhishek.

[Former Member]

hi

i dont think that will work coz the response in not the target but the source...

the mapping doesn't happen coz the response we get from JDBC gets automatically mapped to our MT_response.(the JDBC generates the output in format:JDBC_request and appends a '_response' to it.

ill elaborate the scenario a bit.

the scenario works like this:

File -> JDBC

JDBC_response->File

as u see..its a sync call to JDBC.

according to my understanding , a row node gets generated corresponding to a row returned by JDBC as JDBC_response.

However since no row is being returned for delhi, there wont be a corresponding row in JDBC_response.

Is there any way to have a Row field even if there is no row returned by the JDBC_response?

[Former Member]

Hi

Can u elobrate more pls

srini