MQSeries.net :: View topic - ESQL

Jane · Posted: Mon May 24, 2004 8:22 am Post subject:

Hi,

I am trying to insert into a database from an XML formatted file with an element 'Containers' that can occur multiple times within the file. Then each of the child elements need to be inserted as a single row in the DB2 table. My ESQL is as follows:

CREATE PROCEDURE PackInsert(IN K INTEGER)
BEGIN
DECLARE PackBookAgtSub CHARACTER;
DECLARE PackBookAgtNbr INTEGER;
DECLARE PackEstNbr CHARACTER;
DECLARE PackAgtSub CHARACTER;
DECLARE PackAgtNbr INTEGER;
DECLARE PackCodeServofPnt CHARACTER;
DECLARE PackRateTypeCode CHARACTER;
DECLARE PackOtRateused CHARACTER;
DECLARE PackFullUnpackIndt CHARACTER;
DECLARE PackItemCode CHARACTER;
DECLARE PackCartonCode INTEGER;
DECLARE PackQuantity1 INTEGER;
DECLARE B INTEGER 1;
DECLARE C INTEGER;

SET OutputRoot.*[K] = InputRoot.*[K];
SET C = CARDINALITY(InputBody.order_hhg[K].containers[]);

SET PackBookAgtSub = InputBody.order_hhg
[K].estimate_id.booking_agt_subsidiary;
SET PackBookAgtNbr = InputBody.order_hhg
[K].estimate_id.booking_agt_number;
SET PackEstNbr = InputBody.order_hhg
[K].estimate_id.est_number;

--Containers
SET B = 1;

WHILE B < C DO

SET PackAgtSub = InputBody.order_hhg[K].containers
[B].agt_subsidiary;
SET PackAgtNbr = InputBody.order_hhg[K].containers
[B].agt_number;
SET PackCodeServofPnt = InputBody.order_hhg[K].containers
[B].point_of_service;
SET PackItemCode = '054';
SET PackRateTypeCode = 'CT';
SET PackOtRateused = InputBody.order_hhg[K].containers
[B].ot_rateused;
SET PackFullUnpackIndt = ' ';

If InputBody.order_hhg[K].containers[B].dish > 0 then
SET PackCartonCode = 005;
SET PackQuantity1 = InputBody.order_hhg[K].containers
[B].dish;
CALL InsertPacking( PackBookAgtSub,
PackBookAgtNbr, PackEstNbr,
PackAgtSub,
PackAgtNbr,
PackCodeServofPnt,
PackRateTypeCode,
PackOtRateused,
PackFullUnpackIndt,
PackItemCode,
PackCartonCode,
PackQuantity1,
C);

End If;

If InputBody.order_hhg[K].containers[B].c15 > 0 then
SET PackCartonCode = 010;
SET PackQuantity1 = InputBody.order_hhg[K].containers
[B].c15;

CALL InsertPacking(
PackBookAgtSub,
PackBookAgtNbr,
PackEstNbr,
PackAgtSub,
PackAgtNbr,
PackCodeServofPnt,
PackRateTypeCode,
PackOtRateused,
PackFullUnpackIndt,
PackItemCode,
PackCartonCode,
PackQuantity1,
C);
End if;

SET B = B + 1;

END WHILE;

CREATE PROCEDURE InsertPacking(
INOUT PackBookAgtSub CHARACTER,
INOUT PackBookAgtNbr INTEGER,
INOUT PackEstNbr CHARACTER,
INOUT PackAgtSub CHARACTER,
INOUT PackAgtNbr INTEGER,
INOUT PackCodeServofPnt CHARACTER,
INOUT PackRateTypeCode CHARACTER,
INOUT PackOtRateused CHARACTER,
INOUT PackFullUnpackIndt CHARACTER,
INOUT PackItemCode CHARACTER,
INOUT PackCartonCode INTEGER,
INOUT PackQuantity1 INTEGER,
INOUT C INTEGER)

BEGIN

INSERT INTO Database.TESTPACK
(NBR_AGT_EST,
NBR_EST,
CODE_SUB_CMPY_EST,

IDEN_SCRN, DESC_ITEM,
cont with rest of table columns)

VALUES
(PackBookAgtNbr, SUBSTRING(PackEstNbr FROM 1 FOR 7),
SUBSTRING(PackBookAgtSub FROM 1 FOR 1),
' ',
COALESCE(PackAgtNbr,0),
SUBSTRING(COALESCE(PackCodeServofPnt,' ')
FROM 1 FOR 1),
' ',
' ',
' ',
' ',
' ',
' ',
' ',
SUBSTRING(COALESCE(PackItemCode,' ') FROM 1 FOR 3),
' ',
COALESCE(PackCartonCode,0),
SUBSTRING(COALESCE(PackRateTypeCode,' ') FROM 1 FOR 2),
' ',
' ',
0.00,
0.00,
SUBSTRING(COALESCE(PackOtRateused,' ') FROM 1 FOR 1),
' ',
' ',
COALESCE(PackQuantity1,0),
C,
0,
0,
0,
0,
0,
0,
' ',
0,
0,
CURRENT_TIMESTAMP,
'UPLD',
' ',
' ',
SUBSTRING(COALESCE(PackAgtSub,' ') FROM 1 FOR 1),
' ',
' ',
' ',
0,
' ',
' ',
' ',
SUBSTRING(COALESCE(PackFullUnpackIndt,' ') FROM 1 FOR 1)
);

IF ((SQLCode <> 0) and (SQLSTATE <> 23502)) then

THROW USER EXCEPTION MESSAGE 2950 VALUES
('TESTPACK',SQLSTATE,SQLCODE,SQLERRORTEXT,SQLNATIVEERROR);

END IF;

END;

END MODULE;