MQSeries.net :: View topic - Procedure ends prior to reaching the End statement

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Procedure ends prior to reaching the End statement

Procedure ends prior to reaching the End statement

« View previous topic :: View next topic »

Author

Message

djs

Posted: Thu Apr 17, 2014 10:38 am Post subject: Procedure ends prior to reaching the End statement

Newbie

Joined: 25 Mar 2014
Posts: 5

Iâ€™m new to both MQ and ESQL.

In my esql compute node I am attempting to make a call to com.bdc.cache.GetTheSettingFromCache(). The com.bdc.cache.GetTheSettingFromCache() returns back to the esql compute node prior to executing all the logic between the Begin and End statements. Can someone provide any suggestions on how to resolve this issue?

Here is a simplified version of my esql compute node which is calling another procedure in a different esql file.

Code:

CREATE COMPUTE MODULE Compute1
CREATE FUNCTION Main() RETURNS BOOLEAN
BEGIN
   CALL CopyEntireMessage();

   DECLARE mapFrom REFERENCE TO Environment.OriginalMessage.XMLNSC.Order;
   DECLARE mapTo REFERENCE TO OutputRoot.XMLNSC.DFSWebservice;

   CALL DoMap(mapFrom, mapTo);

   SET OutputLocalEnvironment = InputLocalEnvironment;

   RETURN TRUE;
END;

-- Perform the more complex mapping bits not handled in the node map!
CREATE PROCEDURE DoMap (IN mapFrom REFERENCE, IN mapTo REFERENCE )
BEGIN

   DECLARE localMapFrom REFERENCE TO mapFrom; -- Order
   DECLARE localMapTo REFERENCE TO mapTo; -- DeluxeFsWebService

   MOVE localMapTo TO localMapTo.Order.OrderHeader;

   CALL com.bdc.cache.GetTheSettingFromCache(localMapFrom,localMapTo);

   SET localMapTo.(XMLNSC.Attribute)NumberOfBoxes = 1;
   SET localMapTo.(XMLNSC.Attribute)Quantity = 50;
   SET localMapTo.(XMLNSC.Attribute)Desgin = 'VP5';
   SET localMapTo.(XMLNSC.Attribute)Style = 'WAL';

   MOVE localMapTo TO mapTo; -- DeluxeFsWebService

END;

CREATE PROCEDURE CopyEntireMessage() BEGIN
   SET OutputRoot = InputRoot;
END;

END MODULE;

Here is the second esql file.

Code:

BROKER SCHEMA com.bdc.cache

DECLARE luCheckOrderPackage SHARED ROW;
DECLARE luCheckOrderShipMethod SHARED ROW;

CREATE PROCEDURE GetCheckOrderPackage ( IN P1 INT )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.usp_GetCheckOrderPackage";

CREATE PROCEDURE GetCheckOrderShipMethod ( IN P1 INT )
LANGUAGE DATABASE
DYNAMIC RESULT SETS 1
EXTERNAL NAME "dbo.usp_GetCheckOrderShipMethod";

CREATE PROCEDURE FetchAll()
BEGIN
BEGIN ATOMIC
   CALL GetCheckOrderPackage(NULL,luCheckOrderPackage.top[]);
   CALL GetCheckOrderShipMethod(NULL,luCheckOrderShipMethod.top[]);
END;
END;

CREATE PROCEDURE GetTheSettingFromCache (IN whichSetting REFERENCE, IN settingValue REFERENCE)
BEGIN
-- luCheckOrderPackage is null when the first call is made so FetchAll will execute
IF luCheckOrderPackage IS NULL THEN
   CALL FetchAll();
END IF;
-- Procedure ends after the FetchAll call and never reaches this point
CALL LookupPackage(whichSetting.Order.PackageId);

END;

CREATE PROCEDURE LookupPackage(IN packageId CHARACTER)
BEGIN
DECLARE mapFrom REFERENCE TO luCheckOrderPackage.Top[];
WHILE mapFrom IS NOT NULL DO
   IF mapFrom.CheckOrderPackageId = packageId THEN
      declare i int;
   END IF;
   MOVE mapFrom LASTCHILD;
END WHILE;

END;

fjb_saper

Posted: Thu Apr 17, 2014 11:43 am Post subject:

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20697
Location: LI,NY

You're passing the reference to luCheckOrderPackage.top[] but the top element has never been defined to the row.

Do something like in procedure FETCH ALL

Code:

DECLARE lupackage REFERENCE TO luCheckOrderPackage.top;
IF NOT LASTMOVE(lupackage) THEN
CREATE LASTCHILD OF luCheckOrderPackage NAME 'top';
MOVE lupackage TO luCheckOrderPackage.top;
END IF;
DECLARE lushipmethod REFERENCE TO luCheckOrderShipMethod.top;
IF NOT LASTMOVE(lushipmethod) THEN
CREATE LASTCHILD OF luCheckOrderShipMethod NAME 'top';
MOVE lushipmethod TO luCheckOrderShipMethod.top;
END IF;

This construct allows you to create the top element only if absent (1st time)...
You can then substitute in the DB call the reference you created.

The problem arises because of the call to the dbprocedure.

Normal call would have been

SET <rowname>.top[] = SELECT ....

This construct will create the top element if absent...

Remember ALWAYS check if your reference is valid with LASTMOVE(reference)

And the reason for this is because you have to pass a reference to the DB call and cannot pass a ROW...

Thanks Dave and Dominic...

_________________
MQ & Broker admin

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Procedure ends prior to reaching the End statement

Jump to:

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

Protected by Anti-Spam ACP