| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Multiple database inserts from single message |
« View previous topic :: View next topic » |
| Author |
Message
|
| nathanw |
 Posted: Tue Mar 01, 2005 1:45 am Post subject: Multiple database inserts from single message |
 |
|
Knight
Joined: 14 Jul 2004
Posts: 550
|
Windows
5.1
DB2
I have a message being actioned within a flow that has 1 - 4 entries which may vary
all is handled under XML / XMLNS
the actual insert is fine HOWEVER
as there could be a multiple insert i have introduced cardinality into the ESQL to insert for as many entries within the incoming root ie 1 entry, 1 insert, 2 entries, 2 inserts etc
this scenario works fine HOWEVER for some reason it is only inserting the first set of values multiple times
Most of he values themselves are set to be Variables elswhere in the code but the makor items are included within the cardinality
| Code: |
DECLARE A INTEGER 1;
DECLARE B INTEGER CARDINALITY(InputRoot.XMLNS.XML.Element)"XML".DispenseNotification.Items.*[]);
WHILE A < B DO
SET ITEMID = InputRoot.XMLNS.(XML.Element)"XML".DispenseNotification.Items.Item.(XML.attr)ID;
SET ITEMQUAN = InputRoot.XMLNS.(XML.Element)"XML".DispenseNotification.Items.Item.Quantity;
SET SNOMED = InputRoot.XMLNS.(XML.Element)"XML".DispenseNotification.Items.Item.SNOMED;
INSERT INTO Database.XXXX.XXXX(TOKENID, DATETIME, AGENTID, AGENTCODE, PHARMID, PHARMTELE, PHARMNAMEP, PHARMNAMEG, PHARMNAMEF, PHARMORGCODE, PHARMORGNAME, PHARMORGCOMM, STATUSID, MESSAGEID, STOREID, ITEMID, ITEMQTY, ITEMUNIT, SNOMED) values (TOKENID, DATETIME, AGENTID, AGENTCODE, PHARMID, PHARMTELE, PHARMNAMEP, PHARMNAMEG, PHARMNAMEF, PHARMORGCODE, PHARMORGNAME, PHARMORGCOMM, STATUSID, MESSAGEID, STOREID, ITEMID, ITEMQUAN, ITEMUNIT, SNOMED);
SET A = A + 1;
END WHILE; |
The result happens with for example 3 inserts of the same values
I am assuming that the problem is the variables not updating during cardinality
Any ideas?
Many thanks |
|
| Back to top |
|
 |
| JMB |
| Posted: Tue Mar 01, 2005 4:38 am Post subject: |
|
|
Apprentice
Joined: 01 Apr 2002
Posts: 29
|
| I believe cardinality simply counts your occurences - you still need to increment your variables for the insert, not just your counter. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 01, 2005 5:48 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I'm going to agree with JMB, but qualify his answer a little more.
Change
| Code: |
| SET ITEMID = InputRoot.XMLNS.(XML.Element)"XML".DispenseNotification.Items.Item.(XML.attr)ID; |
to
| Code: |
| SET ITEMID = InputRoot.XMLNS.(XML.Element)"XML".DispenseNotification.Items[A].Item.(XML.attr)ID; |
Or some such. I may have forgotten a needed . between Items and [A].
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
