| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Very Very Urgent..... |
« View previous topic :: View next topic » |
| Author |
Message
|
| amr |
 Posted: Mon Oct 14, 2002 3:03 am Post subject: Very Very Urgent..... |
 |
|
Newbie
Joined: 12 Oct 2002
Posts: 4
|
Hi ....
I have two tables :
Header Record:
Date:(
Seq:(3)
Sub:(5)
Detail Record:
Date:(
seq:(3)
Subm:(5)
Detail:(5)
my BLOB test data is :
20021010111aaaaa|||20021009222aaaaabbbbb|||20021009333aaaaaccccc
In the above input data, it has one Header and two Detail records but in the real case we may have one Header record and multiple number of Detail records that may be 10 or 1000...
So Messageflow has to insert header record into Header table and Detail records into detail table.
I have tried with this test message:
20021010111aaaaa|||20021009222aaaaabbbbb it is working fine but how can i insert if i have a more than one record to be inserted in the Detail table
ie. 20021010111aaaaa|||20021009222aaaaabbbbb|||20021009333aaaaaccccc can we handle in ESQL dynamically.. If yes how?
It's really urgent for me... i appreciate for any help...
Thanks
amr |
|
| Back to top |
|
 |
| jdouch |
| Posted: Mon Oct 14, 2002 3:49 am Post subject: |
|
|
Apprentice
Joined: 31 May 2002
Posts: 32
Location: London, UK
|
presumably your detail record information is a repeating group of detail information... if so you should use a while loop and the cardinality function to loop through and insert each group. the code should look something like this ...
DECLARE dtlcnt INT; (count no of dtl lines)
DECLARE dtltot INT; (total no of dtl lines)
SET dtltot = CARDINALITY(InputRoot.MRM.x.dtl[]);
SET dtlcnt = 1;
WHILE dtlcnt <= dtltot DO
SET database statement here = InputRoot.MRM.x.dtl[dtlcnt].field1;
SET dtlcnt=dtlcnt+1
END WHILE;
rgds
_________________
Julian Douch
E-business Solutions Consultant
WMQ/WMQI Specialist |
|
| Back to top |
|
|
| lillo |
| Posted: Mon Oct 14, 2002 4:56 am Post subject: |
|
|
Master
Joined: 11 Sep 2001
Posts: 224
|
Hi,
I think you have two different solutions:
1. Without creating a message set. The message flow is a MQInput node, a compute node and a MQOutput node.
| Code: |
DECLARE dtlcnt INT; (count no of dtl lines)
DECLARE dtltot INT; (total no of dtl lines)
DECLARE offset INTEGER;
DECLARE date CHARACTER;
DECLARE seq CHARACTER;
DECLARE detail CHARACTER;
SET dtltot = LENGTH(InputRoot.BLOB.BLOB)-16/21;
SET dtlcnt = 1;
WHILE dtlcnt <= dtltot DO
SET offset = 21*(dtlcnt-1)+16 + 1;
SET date = SUBSTRING(InputRoot.BLOB.BLOB FROM offset FOR 8);
SET seq = SUBSTRING(InputRoot.BLOB.BLOB FROM offset + 8 FOR 3);
SET subm = SUBSTRING(InputRoot.BLOB.BLOB FROM offset + 8 + 3 FOR 5);
SET detail =SUBSTRING(InputRoot.BLOB.BLOB FROM offset + 8 + 3 + 5 FOR 5);
INSERT INTO Database.<table>(DATE, SEQ, SUBM, DETAIL) VALUES(date, seq, subm, detail);
SET dtlcnt=dtlcnt+1
END WHILE;
|
2. Creating a message set
Create a message where you should define the header and the detail record.
Add a field before the header record. Should be an integer. It will be the counter.
Specify this field you create as the counter of the repetitive record.
The message flow is a MQInput node, a compute node, a reset content descriptor node, a compute node and a MQOutput node.
The first compute node has the following ESQL code
| Code: |
SET OutputRoot.BLOB.BLOB = LENGTH(InputRoot.BLOB.BLOB)-16/21 || InputRoot.BLOB.BLOB;
|
The RCD changes the input message from BLOB to MRM using the message you created.
The second compute node insert into the database. The ESQL code should be:
| Code: |
DECLARE refDetail REFERENCE TO InputRoot.MRM.DETAIL[1]; -- A reference to the first detail record;
WHILE LASTMOVE(refDetail) DO
INSERT INTO Database.<table>(DATE, SEQ, SUBM, DETAIL) VALUES(refDetail.date, refDetail.seq, refDetail.subm, refDetail.detail);
MOVE refDetail NEXTSIBLING NAME;
END WHILE;
|
I don´t know if it is clear enough. If you have any question about the different solutions just tell me.
Cheers,
_________________
Lillo
IBM Certified Specialist - WebSphere MQ |
|
| Back to top |
|
|
| amr |
| Posted: Mon Oct 14, 2002 7:03 am Post subject: |
|
|
Newbie
Joined: 12 Oct 2002
Posts: 4
|
hey guys!!! thanks for ur help.
i'm going to try now and let u know once it is done. |
|
| 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
|
|
|
|
