Hi I'm using GlassFish ESB to for example to store data recieved from a Microsoft WCF webservice in SQL Server 2005.
The problem I'm faced with is firstly that i have to insert into three tables, the usual approach would be to create a DB Binding in a "BPEL" but that has the problem of me having to create one binding for each table so I'm not sure i can insert into them at the same time.
Apparently I can however create the binding from a stored procedure file.
Basically the XML schema for the DBbinding is generated from the stored procedure (or is supposed to be) and the input for the EntryBinding is from an XSD in the bpel
Lets assume i have:
- Table Person: pid int, firstname varchar(50), lastname varchar(50)
- Table X: xid int, itemname varchar(50), pid (foreign key)
- Table Y: yid int, itemname varchar(50), pid (foreign key)
each time I request data from the WS i recieve a an array of persons which have their respective data and then an array of X and Y.
How would a Stored Procedure look that would be able to handle an insert request like that, that is
foreach Person: I want to insert it's data for each instance and then for each person there's an array of items X, and another array of Y all of which contain some data. I need to insert into the person table and (which is identity specific), use @@IDENTITY for for the foreign keys i suppose.
I don't understand how to make it loop and i don't understand how i can increment the @@IDENTITY.
Is this a viable way for me to solve the problem or should i be looking elsewhere?
I thank you for any assistance you can give.