need help on proxy to jdbc sync with stored procedure

[former_member200386]

Dear Experts,

I am very new on working with JDBC adapter synchronous way. this is my first scenario. i tried to search in scn for the step by step scenario with screen shots.

1)where do we  need to write Stored procedure in XI or at Data base level?

2)How do i need to create the data types  for the request & response

3) what are things i need to  do in communication channel configuration

4)the data i am getting from the Oracledatabase is a string that i need to split into fields and post into ecc & vice versa.  in Message mapping.

can you suggest the UDF for this requirement

please help me

Thanks in advance

pavan

[rajasekhar_reddy14 · Accepted solution]

1)where do we  need to write Stored procedure in XI or at Data base level?

Your DB team ahs tro write Stored procedure at Data base level and you need to call stored procedure from PI at Mapping level.

Action--- EXECUTE

Table---->Stored procedure name.

2)How do i need to create the data types  for the request & response

Yes, you need two data types.

3) what are things i need to  do in communication channel configuration.

Very simple, provide ddriver details and logoin credetials and dont use batch mode.

4)the data i am getting from the Oracledatabase is a string that i need to split into fields and post into ecc & vice versa.  in Message mapping.

You need to write a mapping logic to split the string. if possible request DB team to perfrom the same .

[former_member200386 · Accepted solution]

HI Raj,

Thanks for your reply. I am still confused with creating of Data types who do i create a data type for stored procedure.

we have standard syntax for the jdbc at the receiver

for the jdbc data type 1(request)

statement

table

action

access

how i need to crfeate theJDBC response datatype.

i checked my database team he don't know how to split the strings to fields. how can i work this out from PI end. i mean i need to merge the string for the request  &  split  for the response.

Please send me any screen shots for the Req & Res DATA types If you have any

Regards

pavan

[rajasekhar_reddy14 · Accepted solution]

Create data type like below format request data type .

First understand from DB team how they are going to send response , based on that you have to create response structure.

If they are going to send in one string then you need split the string in PI mapping to map different fields. this can be done at PI level.

[former_member200386 · Accepted solution]

Hi Raj,

Thanks for your reply . Sorry for asking this again. i am little confused about the screen shot which you have sent. this is for requset or response?

in that i don't find any access tab there. i am really sorry if am irritating you because this is my scenario please bare me.

Regards,

Pavan

[Former Member · Accepted solution]

Hi,

chk below blogs:

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/1671

http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/9618

Thanks

Amit Srivastava

[rajasekhar_reddy14 · Accepted solution]

Stored procedure structure is different compare to normal INSERT statment structure,so there is no access tag.

The structure is for request stored procedure.

[former_member200386 · Accepted solution]

Hi Raj,

Thanks for your reply,

In the screenshot

ordertype, orderpartnerDTN,order number .... these are fileds  name?

like this i need to creat my request data type write. correct me if i am wrong.

how can i map isinput, type fields?

can u please suggest me the response structure also with screen shot

Regards,

pavan

[baskar_gopalakrishnan2 · Accepted solution]

>ordertype, orderpartnerDTN,order number .... these are fileds  name?

like this i need to creat my request data type write. correct me if i am wrong.

Yes they are field names. You are correct.   Please refere Amit reply's blog for mapping and response structure details.

[rajasekhar_reddy14 · Accepted solution]

isinPut-----constant value(true).

Type----pass db type(eg VARCHAR).

[former_member200386 · Accepted solution]

Hi Baskar,

Thanks for your reply , i have one question for you in the screen shot isinput,type  fields category was given  as attribute. may i know why we need to give attribute?

in the Amit reply's blog isinput, type is mentioned as boolean but in the above screen shot their type was string?

Regards,

Pavan

[baskar_gopalakrishnan2 · Accepted solution]

>isinput,type  fields category was given  as attribute. may i know why we need to give attribute?

That is the standard for the store procedure message structure. Refer sap help document for the jdbc receiver structure.

This link also specifies type as string only. only isInput is boolean.

http://scn.sap.com/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

[rajasekhar_reddy14 · Accepted solution]

Hi Pavan,

Attribute is nothing but a preperty of the field , in stored procedure case we need to define as a attribute only. 

Regards,

Raj

[former_member200386 · Accepted solution]

Hi Raj thanks for your reply,

I will give you my exact requirement please suggest me how can i develop the scenario.

In this below requirement i need to take the data from two tables and merge accordingly . so how i need to create my request & response data types?

• Process Logic – PI Stored Procedures.

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)
6. Call the Web service( generated by SAP XI) and pass the Order details to SAP XI and wait for the result
7. After getting the result, update the following tables
	Main table for updating the SAP Sales Order No.
	SDO_LASTSONUM for updating the Last processed Sales Order No
	SDO_ERRORSONUM for updating the failed sales order numbers.

[former_member200386 · Accepted solution]

HI All, I have to reopen the question there is mistake from My end now my scenario is JDBC to ABAP Proxy syncronus

wan we do it with using BPM ? i checked the scn i haven't i found the exact information . please suggest me how can do this scenario

Regards,

Pavan