MQSeries.net :: View topic

Seagull · Posted: Tue Dec 18, 2007 8:56 am Post subject:

I am using the filter node and this esql code as to acheive a database query and an update along with an Insert.

The input message will have a header and Line details(array).I have check the the different tables in database and update them/insert into them according the existene of a number.

DATABASE NAME: DEVDTA
TABLE1 NAME: D4401
TABLE2 NAME: D3301
TABLE3 NAME: D4401Z
TABLE4 NAME: D3301Z

Column1= TRNUM-------------------(Number)
Column2 = TRIST-------------------(Initial status)
Column2 = TRFST-------------------(Final status)

CREATE FILTER MODULE DATABASE_Filter

CREATE FUNCTION Main() RETURNS BOOLEAN

BEGIN

IF (EXIST(SELECT T.TRNUM FROM DEVDTA.D4401 AS T,

WHERE T.TRNUM = InputRoot.XMLNSC.Company.Header.OrderNumber)) THEN

//IF Order number exists then modify the record

update Database.D3301 AS T

SET TRIST = InputRoot.XMLNSC.Company.LineDetails.InitialStatus[] WHERE T.TRNUM = InputRoot.XMLNSC.Company.Header.OrderNumber;
SET TRFST = InputRoot.XMLNSC.Company.LineDetails.FinalStatus[] WHERE T.TRNUM = InputRoot.XMLNSC.Company.Header.OrderNumber;

RETURN TRUE;

ELSE

//if the Order Number does n't exists at all

INSERT INTO DEVDTA.D4401Z (TRIST,TRFST) values (InputRoot.XMLNSC.Company.LineDetails.InitialStatus[],InputRoot.XMLNSC.Company.LineDetails.FinalStatus[]);
INSERT INTO DEVDTA.D3301Z (TRIST,TRFST) values (InputRoot.XMLNSC.Company.LineDetails.InitialStatus[],InputRoot.XMLNSC.Company.LineDetails.FinalStatus[]);

END IF;

END;

END MODULE;

Anything else I need to take care as I am dealing with an array input and as per code I will be updating/inserting the number of times the array is iterated.
Is my approach correct ? Please let me know if I have made any mistakes or should consider other factors too?

Thanks in adavance.