on 02-26-2014 7:48 AM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 (:
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
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 :
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
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
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
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
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.