Receiver JDBC structure and Mapping

[Former Member]

Hello everyone,

Scenario - WS (async) -> PI 7.1 -> JDBC (sync) -> PI 7.1 -> WS (async)

My question is - In my request structure (from WS), I have a field "Transaction ID". This field is not present in the target DB, but when I get the response back from DB, I need to insert this transaction id in the response back to WS.

I am aware that there is an option in the request query (passing parameter and getting back the same value) and this can be achieved through XSL mapping. Can this same functionality be achieved through Graphical Mapping?

In other words, if I pass Transaction Id in my request query, can I get it back in the response? Just to remind, Transaction Id is not available in DB.

Any help / suggestion will be highly appreciated.

Thanks,

Abhi

[Former Member · Accepted solution]

Abhi,

Although your post is an old one, but since it is not yet closed, i assume that you are still working on this.

I believe you have a view from where you are doing the select. You can give it a try with the following query -

SELECT V1.<your other fields that you want to select>, V2.TRANSACTIONID,

FROM <ViewName> V1,

(SELECT "TRANSACTIONID" AS TRANSACTIONID FROM DUAL) V2

WHERE <Condition>

This will simply create a temporary view V2 for holding the transaction id, and it will pick the value that you are passing with each message and in the response to the SELECT, it will always bring back the Transaction Id that you passed.

I would recommend that you use an XSL Mapping for this. Hope this helps.

Regards,

Neetesh

[Former Member · Accepted solution]

Neetesh,

Yes, this issue was still open

The solution sounds real promising. I tried it quickly (on SQL Developer) and it is working fine. Now I am sure this will work on PI, I just need to put things in place.

Thanks for your help. Closing the thread now.

--Abhi

[Former Member]

Hi Abhi_1516,

whats about using Dynamic Configuration in UDF? If you create a Key in Request Message you usually get back the Dynamic Key in your Response Message. Did you try something like this?

Regards,

Alex

[Former Member]

Hi,

Do it using BPM design.

1. Receive Step from WS ( WS request)

2. Send Syn step using JDBC and get back the response.

3. Send step to WS ( use a transformation step N:1) the response from JDBC and the initial WS request as the source messages to the required target.

Regards

Sunil.

[Former Member]

Thanks for the response Sunil.

But my scenario is little complex. I have 3 different Select queries and they can have multiple occurrences of the response nodes. Once the response is back, I have to "merge" the response of 3 queries into 1 message.

Since I will be doing lots of stuff with the response message, I am not interested in having the transaction id being handled at that point of time.

I might use XSL Mapping to handle this scenario ...

But I would like to know whether I can achieve this using Graphical?

Thanks,

Abhi

Edited by: Abhi_1516 on Jan 15, 2010 7:09 PM

[Former Member]

Am not sue if this would be feasible but can you try this?

Create a seperate column/table in the DB for storing the Transaction ID and pass it with your query. When you get the response read the data back. That would allow you holding the transaction ID.

Just crossed my mind so mentioned it.

Regards

Soumen...

[Former Member]

Thanks Soumen.

But I will have to pass on your suggestion, as DB team won't be creating and maintaining a field for me, which they don't require at all.

Appreciate your kind response.

-Abhi

[abhay_rajhans2]

Hi Abhi,

DB team won't be creating and maintaining a field for me

This is one workaround. You can create a database table on Abap stack of PI. There store the transaction ID in this table with some identification of input message using RFC & UDF. For this you need to write UDF in the graphical mapping. When you pass back the message to WS call this ABAP table using some RFC & UDF.

Edited by: Rajhans Abhay on Jan 16, 2010 9:47 AM

[former_member181985]

You can go ahead with Alexander Kirsch's solution. I did the same for one of our project requirements.

Regds,

Praveen

Edited by: Praveen Gujjeti on Jan 29, 2010 3:23 PM