How to work with stored procedures(SP) XI 3.0 receiver JDBC .?

[Former Member]

Hi All,

Finally I some how manage to use stored procedures in my requirement.

Please help me on this :

NOTE : I am using SAP XI (3.0)

Sender: Proxy

Receiver : JDBC(MSSQL)

I need to send 4 tables data to the respective 4 tables in SQL server for this :

1. I have created  one sender DT which is having 4 tables as 4 records

2  Previously i thought to use UPDATE_INSERT statement, So i have created one receiver DT which is having 4 TABLES (each table have individual UPDATE_INSERT statement ).

Now I am thinking to use SP , so what is the structure i need to create in receiver Data type ..?

I got this from help.sap:

<StatementName5> 

<storedProcedureName action=” EXECUTE”>

<table>realStoredProcedureeName</table>

<param1 [isInput=”true”] [isOutput=true] type=SQLDatatype>val1</param1>

</storedProcedureName > 

  </StatementName5>

I am not getting how i will create my DT structure ... Please help

Regards,

Vin

[Former Member · Accepted solution]

Hello,

Create a linear structure in PI having all the fields relevant for 4 tables and the rest will be taken care by SP (DB team).

Check below articles

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

[Former Member · Accepted solution]

Hi Amit,

Linear structure means i need to create all 4 tables fields under access segment. am i correct..?

I want the sample structure like in UPDATE_INSERT statement we will create action,TABLE and all the fields under access segment . Likewise how i will create the structure for SP according to my requirement  .??

the 2 link is not opening (:

[Former Member · Accepted solution]

Hello,

Create ur SP structure like this:

<MT_Name>

<StatementName>

<storedProcedureName action=” EXECUTE”>

<table>Stored Procedure name</table>

<Field1 type=SQLDatatype>val1</Field1 >

<Field2 type=SQLDatatype>val1</Field2>

<Field4 type=SQLDatatype>val1</Field3>

......

......

</storedProcedureName >

  </StatementName5>

</MT_Name>

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

I have seen below structure also, according to below one

1. In my requirement I have total 46 fields in all 4 tables.I will create all 46 fields under TABLE      SEGMENT irrespective of table name, Is it correct ?

2. Should I provide my target structure to the DB team for creating SP ..? 

Please suggest

sample provided by on of the blog :

[Former Member · Accepted solution]

Hello,

1. In my requirement I have total 46 fields in all 4 tables.I will create all 46 fields under TABLE      SEGMENT irrespective of table name, Is it correct ?

Yes, mention all the 46 fields in ur structure under Statement node (table field is an element not node).

>>BTW, while using SP u don't have to worry about tables, becoz at runtime u will send all the data to SP and then it's the responsibility of SP to perform DML operations.

2. Should I provide my target structure to the DB team for creating SP ..? 

>>Obviously, yes, discuss ur target structure with DB team and most importantly check how u are gng to populate data in ur target structure (mapping logic).

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hello,

I have 4 tables in my source structure.each table as a one record. In target structure I have only 1 statement under which I have all the fields as my target is SP.

My question is : To which fields in target structure should map to source side of 4 record segments (nodes) ?

Vin

[Former Member · Accepted solution]

Hello,

Not sure how ur business data is flowing, but u can replicate statement node four time and map each record with the target statement node and its corresponding fields.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi,

I have developed proxy structure with 4 tables and same tables and its fields there in target structure also but according SP (stored procedure) structure I have combined all the 4 table fields under one statement as you said in above replies.Please see below screen shot:

Now source side 4 records(tables) I have mapped all its fields with same fields in target structure as above shown.

My question is : To which fields in target structure should map to source side of 4 record segments (nodes) marked in green ..?

Vin

[Former Member · Accepted solution]

Hello,

First of all ur structure is incorrect, change the occ of SQL** (just beneath ur Message Type name) and set it as 0...Unbounded.

Now, duplicate ur SQL** (statement name) four times using duplicate subtree option and then map one by one each record (and its corresponding fields) to the duplicated statement node. So first occ of statement node will only be mapped with first record, second occ of statement node with be mapped with second record and so on...

In this way, at runtime ur SP will excute 4 times.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi,

Then each duplicate statement has all the fields, means the fields which I have in original statement. am i correct..?

Each 4 statement node has all the fields but i need to map which is required , correct.?

Plz clarify

Vin

[Former Member · Accepted solution]

Hello,

>>Then each duplicate statement has all the fields, means the fields which I have in original statement. am i correct..?

Yes

>>Each 4 statement node has all the fields but i need to map which is required , correct.?

Yes. So, for instance in the first statement node u will only map fields from first source record and rest all fields will not be mapped.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Thanks Amit

Now should i provide all the structure (including duplicate statements) to the DB team for SP creation ..? or only original statement is enough ?

plz clarify

Vin

[Former Member · Accepted solution]

Hello,

Just provide ur Message type structure to ur DB team, but let them know how u are gng to populate the values in this structure (i mean first occ of statement node will have only values for table1, second will have values for table2...)

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi Amit,

I have designed the mapping as discussed. but another issue came

The interface that insert to multiple tables do not insert the same amount of rows, there is no 1-to-1 relationship between the tables in this interface.

For instance, in this interface 4 tables are updated, but they will all have a different amount of rows,

ex :

TABLE 1 :  640 inserts/updates

TABLE 2 :  940 inserts/updates

TABLE 3 :  92 inserts/updates

TABLE 4 :  45 inserts/updates

Each table have a different number of inserts/updates.

So,

1. Is our mapping  will work for the above requirement ?

2. Single SP for all tables will be possible ?? or we need to go for 1 SP for 1 table ..?

Please help .

Vin

[Former Member · Accepted solution]

Hello,

Since u have multiple rows in ur source structure so it would be easy to create 1 SP for each table (in total 4 SPs).

Remodel ur structure like this:

<MT_Name>

<StatementName1> -----------------0..Unbounded

<storedProcedureName1 action=” EXECUTE”>      ----------For Table 1

<table>Stored Procedure name</table>

<Field1 type=SQLDatatype>val1</Field1 >

<Field2 type=SQLDatatype>val1</Field2>

<Field4 type=SQLDatatype>val1</Field3>

......

......

</storedProcedureName >

  </StatementName1>

<StatementName2> ----------------0...Unbounded

<storedProcedureName2 action=” EXECUTE”> ------------For Table2

<table>Stored Procedure name</table>

<Field1 type=SQLDatatype>val1</Field1 >

<Field2 type=SQLDatatype>val1</Field2>

<Field4 type=SQLDatatype>val1</Field3>

......

......

</storedProcedureName >

  </StatementName2>

....

....

....

</MT_Name>

Thanks

Amit Srivastava

[Former Member · Accepted solution]

Hi,

If present structure(duplication of statement ) is sufficient for my requirement then I will stick with 1 SP structure only. If not, I will go for individual SP .

Please suggest

[Former Member · Accepted solution]

Hello,

>>If present structure(duplication of statement ) is sufficient for my requirement then I will stick with 1 SP structure only. If not, I will go for individual SP

It will only complicate ur DB team task.

Anyway, did u tried checking with ur DB team that if u send a flag in each statement node specifiying that particular statement is for Table1 or Table2 will that suffice? Doing this will help them in identifying that particular record is for which Table.

If yes then u can add one more field in ur structure (Flag) and then send some constant value while doing mapping in each duplicated node.

Thanks

Amit Srivastava

[Former Member · Accepted solution]

It seems harder .

I will go for 1 SP for 1 table . it should work as expected right ..?

Vin

[former_member182455]

Hi,

check the below links,

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

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

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

https://scn.sap.com/message/13163250

http://scn.sap.com/community/pi-and-soa-middleware/blog/2012/04/12/jdbc-data-type-structure-creation...

Regards

srinivas