Jdbc to ABAP Proxy (Synchronus) with Stored Procedure

[former_member200386]

Dear Experts,

I have a scenario where my client wants to integrate their Sales orders details with Oracle Database.

where i need to collect the salesorders from Oracle DB (jdbc) and post into ECC(Abap Proxy) andECC( Abap proxy) needs to send the response(Sale order number, status ) two fileds only back to Oracle DB(sync) Using Stored procedure

I need help on this scenario desparately.

1) Can we do this with BPM then how ?

2)With out BPM  ?

I will give you  the Business requirement below

1. Read last updated SO Number from SD_LASTSONUM table.
2. Read the Sales order details from Main table which are greater than Last updated SO Number
3. Read the sales order numbers from SDO_ERRORSONUM
4. Read the Sales order details from Main table for Error Sales Order number
5. Combine the result set of step 2 & 4, and sort the content by Sales Order number Ascending( to ensure the FIFO Process)

[Former Member · Accepted solution]

Hi,

Why are you heading towards BPM for this Synch scenario.

My advice is try to implement this with out going for BPM..

You can call stored procedure using JDBC sender adapter and pass the output of the proxy to the same (sync scenarios..) - this approach should work,,

HTH

Rajesh

[former_member200386 · Accepted solution]

Hi Rajesh,

Thanks  for your quick response

My requirement is JDBC to ABAP proxy(sycnhronus)

Can we use  JDBC adapter  at sender side for synchronous call.

here the tricky part is i need to pull & post data Using Stored procedure

your are saying that it is possible without BPM?

then tell me how can i do that approach?

[Former Member · Accepted solution]

Hi,

I think you need to have two stored procedure in your case...

One for sending the input to proxy and another for passing the output to stored procedure..

try the above approach though i haven't done personally but i strongly believe this can be done..

Another possiblity /approach to achieve in one stored procedure is as follows..

1. Stored procedure should send the request to PI Via http .

2. call the proxy with request received and get the  response from the Proxy ..

3. send the proxy back to stored procedure via Http..

HTH

Rajesh

[former_member200386 · Accepted solution]

Hi Rajesh,

Earlier I suggested Soap To proxy approach. Here the problem with client end they don't have any java programmer to call our wsdl.

so the only way that we have to go with the JDBC adapter.

I searched in sdn for the step by step asynch/ synch bridge BPM with screen shots hence this is my first BPM Scenario

The syntax screen shots for both request & response data types for jdbc

because the syntax was differ from the normal jdbc data type syntax.

One of my frnd suggest me this approcah

2 channels for jdbc(sender & receiver)

1 sender agreement

2 receiver agreement

2 interface determination

2 receiver determination

for this scenario

I am really glad if you help me on this

Regards,

Pavan

[former_member200386 · Accepted solution]

Thank you guys,

Now  We are doing the scenario to 2 ASynch

1 will load data from oracle db using stored procedure

2 will be post the data into oracle db using stored procedure.

can u guys suggest me how i need to create Data types for jdbc side

both sender & receiver using Stored Procedure. with screen shots

Regards,

Pavan

[rajasekhar_reddy14 · Accepted solution]

check below thread to call stored procedure using sender JDBC adapter

http://scn.sap.com/thread/1574165

search in scn, you will find many docs and references.

Previous post we have mentiones stored procedure structure for receiver JDBC scenario so use the same.

[former_member200386 · Accepted solution]

Hi,

Raj ,Now  i got it, we can use oracle-table- functions

We need to write query like this select * from TABLE(SP);

Update with<test>

I haven’t worked on this scenario before. Please suggest me how we need to create a sender datatype(jdbc)

In normal we create DT like this

Sender_dt

Rows

Field1

Field2

Field3

............................

Do we need to follow any syntax for Storedprocedure datatype?

Please send any links, screen shots.

[rajasekhar_reddy14]

Hi Pavan,

Dont use Stored procedure at sender side, use standard insert/update statments, without BPM you can achive this requirement using request/response beans.

Refer below thread and check out praveen replay and explore the options

http://scn.sap.com/thread/1749229

Regards,

Raj

[former_member200386]

Hi Raj,

Thanks for your quick response. The requirement looks quite typical. We have to use stored stored procedure only. Because the SELECT queries will not give the desired result. Because data is from multiple tables( where we cannot use JOIN/UNION also).

So I end up with stored procedure only.

I have gone through the given link also. He has proposed to generate two asynchronous instead of one synchronous proxy. But I will miss the data integration. I need have synchronous call only.

Please suggest

1. Can I use BPM with Oracle stored procedure? Is it possible?

2. If you suggest me to go with message swap beam?, Can you guide me how to use message swap beam, where I need to write the required data fetching logic in my communication channel.

3. Please suggest me any link for async - sync bridge BPM.

Thanks in advance.

With Regards,

Pavan

[rajasekhar_reddy14]

The tricky point here is calling stored procedure using JDBC sender channel, as per my understanding there is no standard way to call stored procedure.(search in scn).

my solution is ask your DB team create intermediate table which hold the results of multiple tables, then perform select operation on new table to pull the data then use BPM .

[former_member200386]

Hi Raja ,

Thanks for your response. Tell me how to create Asynch/synch Bridge BPM? Because I never worked on BPM Before. Please send me any links for step by step Scenarios with screen shot.

The problem is here with client end They are not ready to create any tables in their DB.

So for the BPM I need to create two Jdbc comm channels  for sender & receiver ?

How many Serivce Interfaces?

Please Suggest me Integration proceess (BPM) steps in a order.

Suppose the client Or not ready create a intermediate table

Can we do this scenario in Soap to Proxy?

  here i have one more problem to with this way they don't have any java programmer to write a code

call the web service. what are the things  we need to do SOAP adapter .at the  oracle database.

Regards,

pavan

[baskar_gopalakrishnan2]

> Tell me how to create Asynch/synch Bridge BPM? Because I never worked on BPM Before. Please send me any links for step by step Scenarios with screen shot.

http://saptechnical.com/Tutorials/XI/SYNCASYNC/Page1.htm

>Can we do this scenario in Soap to Proxy  here i have one more problem to with this way they don't have any java programmer to write a code

No.

[former_member200386]

HI Baskar,

Thanks for your response the link which you have sent  it is mentioned that for SYNCH/ASYNCH BPM.

I need ASYNCH/SYNCH BPM.

CAN we use Stored procedure in JDBC Sender side to pull the data?

I need to valuable suggestion

[former_member200386]

Dear experts,

Please suggest me , This  is very urgent requirement. I need your most valuable support.

Regards,

Pavan