| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Problem with FOR statement in ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| blueburn |
 Posted: Tue Mar 20, 2007 2:48 am Post subject: Problem with FOR statement in ESQL |
 |
|
Newbie
Joined: 20 Mar 2007
Posts: 3
|
Hi guys,
I'm having a strange problem with the FOR statement in my esql. Basically I start off with a SQL query using PASSTHRU, which is placed in DB_OUT_PARAMS.MESSAGE_EXCEPTION[]. This works perfectly. Then the fun starts!
The following is my code from that point:
Set evRef.failedMessages[] = DB_OUT_PARAMS.MESSAGE_EXCEPTION[];
For msgRef As evRef.failedMessages[] Do
--some code, referring to msgRef.field1, msgRef.field2, etc.
End For
-------------------------
Assume my original query returns field1, field2, etc. What I would expect is this:
For every record found in my query, i get the structure
DB_OUT_PARAMS
----MESSAGE_EXCEPTION
--------field1
--------field2
----MESSAGE_EXCEPTION
--------field1
--------field2
etc
Then, when I place this onto the evRef reference, I would expect to see:
evRef
----failedMessages
--------field1
--------field2
----failedMessages
--------field1
--------field2
This is in fact what I am seeing. From what I have read on the FOR statement, I would then expect the for statement to load the field1, field2, etc. values into MsgRef, i.e:
msgRef
----field1
----field2
for the first instance of failedMessage and run the code in the for loop until it reaches End For, then load in the values for the next failedMessage. Please correct me if I'm wrong on that!
However, what I am in fact seeing, is msgRef looks like this:
msgRef
----failedMessage
--------field1
--------field2
----failedMessage
--------field1
--------field2
which is not good! I'm not sure whether it is because I am using a database query that I am running into problems, but I cannot see any other difference between that and the example in the documentation.
Any suggestions would be very welcome!
Tim |
|
| Back to top |
|
 |
| elvis_gn |
| Posted: Tue Mar 20, 2007 3:32 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi blueburn,
Please take a file trace right after this compute and show us the tree that your creating in your environment. Plus paste the section of the code where you may be using the above mentioned references in any way.
Regards. |
|
| Back to top |
|
|
| blueburn |
| Posted: Tue Mar 20, 2007 4:13 am Post subject: |
|
|
Newbie
Joined: 20 Mar 2007
Posts: 3
|
Hi Elvis, thanks for quick reply, here is the trace from after the compute:
--------------------------------------------------------------------------------------Broker Trace {After} - 2007-03-20, 10:04:41.838964(
(0x01000000):MQMD = (
(0x03000000):SourceQueue = 'QNAME'
(0x03000000):Transactional = TRUE
(0x03000000):Encoding = 546
(0x03000000):CodedCharSetId = 437
(0x03000000):Format = 'MQHRF2 '
(0x03000000):Version = 2
(0x03000000):Report = 0
(0x03000000):MsgType = 8
(0x03000000):Expiry = -1
(0x03000000):Feedback = 0
(0x03000000):Priority = 0
(0x03000000):Persistence = 0
(0x03000000):MsgId = X'414d51204431455342303031202020206e77fe4520001b18'
(0x03000000):CorrelId = NULL
(0x03000000):BackoutCount = 0
(0x03000000):ReplyToQ = ' '
(0x03000000):ReplyToQMgr = 'QMGR '
(0x03000000):UserIdentifier = 'uid '
(0x03000000):AccountingToken = X'16010515000000f89fb474782e9d1375b97554d729000000000000000000000b'
(0x03000000):ApplIdentityData = ' '
(0x03000000):PutApplType = 11
(0x03000000):PutApplName = 'c:\rfhutil\rfhutil.exe '
(0x03000000):PutDate = DATE '2007-03-20'
(0x03000000):PutTime = GMTTIME '11:54:43.230'
(0x03000000):ApplOriginData = ' '
(0x03000000):GroupId = X'000000000000000000000000000000000000000000000000'
(0x03000000):MsgSeqNumber = 1
(0x03000000):Offset = 0
(0x03000000):MsgFlags = 0
(0x03000000):OriginalLength = -1
)
(0x01000000):MQRFH2 = (
(0x03000000):Format = 'MQSTR '
(0x01000000):mcd = (
(0x01000000):Msd = (
(0x02000000): = 'xmlns'
)
)
)
(0x01000010):XMLNS = (
(0x01000000)some.schema:Message = (
(0x03000000)http://www.w3.org/2001/XMLSchema-instance:schemaLocation = 'some.schema /some/schemas/Message.xsd'
(0x07000012)xmlns:some.schema'
(0x07000012)xmlns:xsi = 'http://www.w3.org/2001/XMLSchema-instance'
(0x02000000): = '
'
(0x01000000):Data = (
(0x02000000): = '
'
(0x01000000):content =
(0x02000000): = '
'
)
(0x02000000): = '
'
(0x01000000):ID = (
(0x02000000): = '
'
(0x01000000):id = (
(0x02000000): = '-1'
)
(0x02000000): = '
'
)
(0x02000000): = '
'
(0x01000000):businessServiceID = (
(0x02000000): = '3'
)
(0x02000000): = '
'
(0x01000000):correlationID = (
(0x02000000): = '0'
)
(0x02000000): = '
'
(0x01000000):gatewayRequestID = (
(0x02000000): = '315'
)
(0x02000000): = '
'
(0x01000000):servReqCorrelationID =
(0x02000000): = '
'
(0x01000000):sourceSystem = (
(0x02000000): = '99999System'
)
(0x02000000): = '
'
(0x01000000):userReference =
(0x02000000): = '
'
)
)
)(
(0x01000000):Variables = (
(0x01000000):STATIC = (
(0x03000000):ERR_Sleep = '10'
)
(0x03000000):maxMsgs = 100
(0x03000000):useMQOutputNode = 'Y'
(0x01000000):failedMessages = (
(0x03000000):CORREL_ID = NULL
(0x03000000):EXCEPTION_ID = 37
(0x03000000):EXCEPTION_TSTMP = TIMESTAMP '2007-03-20 11:15:18.443407'
(0x03000000):FORMAT = 'MQHRF2 '
(0x03000000):IS_EXCEPTION_TRUNC = 'N'
(0x03000000):IS_MESSAGE_TRUNC = 'N'
(0x03000000):IS_USR_FLDR_TRUNC = 'N'
(0x03000000):MESSAGE = X'3c676f763a4d657373616765207873693a736368656d614c6f636174696f6e3d22676f762e64766c612e6170702e656c6973652e636f6d6d6f6e2e64746f2e736368656d61202f6f70742f49424d2f6d7173692f362e302f62696e2f736368656d61732f4d6573736167652e7873642220786d6c6e733a676f763d22676f762e64766c612e6170702e656c6973652e636f6d6d6f6e2e64746f2e736368656d612220786d6c6e733a7873693d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d612d696e7374616e6365223e0d0a203c446174613e0d0a20203c636f6e74656e743e3c2f636f6e74656e743e0d0a203c2f446174613e0d0a203c49443e0d0a20203c69643e2d313c2f69643e0d0a203c2f49443e0d0a203c627573696e6573735365727669636549443e333c2f627573696e6573735365727669636549443e0d0a203c636f7272656c6174696f6e49443e303c2f636f7272656c6174696f6e49443e0d0a203c676174657761795265717565737449443e3331353c2f676174657761795265717565737449443e0d0a203c73657276526571436f7272656c6174696f6e49442f3e0d0a203c736f7572636553797374656d3e3939393939506f6c6963654361736553797374656d3c2f736f7572636553797374656d3e0d0a203c757365725265666572656e63652f3e0d0a3c2f676f763a4d6573736167653e'
(0x03000000):MQ_MSG_ID = X'414d51204431455342303031202020206e77fe4520001b18'
(0x03000000):QUEUE_NAME = 'QNAME'
(0x03000000):REQUEST_TYPE = 'ANY'
(0x03000000):REPLAY_QUEUE = 'QNAME'
(0x03000000):REPLAY_RULE = 'A'
(0x03000000):REPLY_TO_QUEUE = ' '
(0x03000000):REPLY_TO_QMGR = 'QMGR'
(0x03000000):EXCEPTION_STATUS = 'N'
(0x03000000):RFH_USER = NULL
(0x03000000):REPLAY_COUNT = 0
(0x03000000):ERROR_CODE = 'E0101'
(0x03000000):USER_ID = 'uid '
)
(0x03000000):theXML = X'3c676f763a4d657373616765207873693a736368656d614c6f636174696f6e3d22676f762e64766c612e6170702e656c6973652e636f6d6d6f6e2e64746f2e736368656d61202f6f70742f49424d2f6d7173692f362e302f62696e2f736368656d61732f4d6573736167652e7873642220786d6c6e733a676f763d22676f762e64766c612e6170702e656c6973652e636f6d6d6f6e2e64746f2e736368656d612220786d6c6e733a7873693d22687474703a2f2f7777772e77332e6f72672f323030312f584d4c536368656d612d696e7374616e6365223e0d0a203c446174613e0d0a20203c636f6e74656e743e3c2f636f6e74656e743e0d0a203c2f446174613e0d0a203c49443e0d0a20203c69643e2d313c2f69643e0d0a203c2f49443e0d0a203c627573696e6573735365727669636549443e333c2f627573696e6573735365727669636549443e0d0a203c636f7272656c6174696f6e49443e303c2f636f7272656c6174696f6e49443e0d0a203c676174657761795265717565737449443e3331353c2f676174657761795265717565737449443e0d0a203c73657276526571436f7272656c6174696f6e49442f3e0d0a203c736f7572636553797374656d3e3939393939506f6c6963654361736553797374656d3c2f736f7572636553797374656d3e0d0a203c757365725265666572656e63652f3e0d0a3c2f676f763a4d6573736167653e'
)
)(
(0x01000000):MESSAGE_REPLAY_RULE = (
(0x01000000):INPUT = (
(0x03000000):QUEUE_NAME = 'QNAME'
(0x03000000):REQUEST_TYPE = 'ANY'
(0x03000000):ERROR_CODE_MATCH_REPLAY = 'ANY'
)
)
(0x01000000):MESSAGE_EXCEPTION = (
(0x01000000):INPUT = (
(0x03000000):STATUS = 'P'
(0x03000000):REPLAY_TSTMP = TIMESTAMP '2007-03-20 10:04:41.800655'
(0x03000000):EXCEPTION_ID = 37
(0x03000000):QUEUE_NAME = 'QNAME'
)
)
(0x01000000):Destination = (
(0x01000000):MQDestinationList = (
(0x01000000):DestinationData = (
(0x03000000):queueName = 'QNAME'
)
)
)
)
-----------------------------------------
As for the code, I won't paste it all here, but the following is all of the relevant code, with obviously more stuff happening in the FOR loop....oh, and I've had to write PASSTHR*U because for some reason the server won't allow it through without the *?
Declare DB_IN_PARAMS Reference To InputLocalEnvironment; -- Used in database write and reads
Declare DB_OUT_PARAMS Reference To InputLocalEnvironment; -- Used in database reads to hold the output
Declare evRef Reference To Environment.Variables;
Declare inRef Reference To InputLocalEnvironment;
Declare msgRef Reference To InputLocalEnvironment;
Declare outRFH Reference To OutputRoot;
Declare oleRef Reference to OutputLocalEnvironment; -- Used for db reads
Declare SQL_STATEMENT Char;
Declare throwOnNoRowsTrue Boolean True;
Declare throwOnNoRowsFalse Boolean False;
Declare resultBranchName Char;
Declare WhereClause Char;
Declare ic Namespace 'some.schema';
Declare imsg Namespace 'some.schema';
Move inRef To InputRoot.XMLNS.{imsg}:messageReplayTrigger;
-- Set maximum number of messages to be processed in one go
Set evRef.maxMsgs = Coalesce(evRef.STATIC.BROKER_REPLAY_MAXMSGS, 100);
-- This flag is used to determine which path to follow in the filter
Set evRef.useMQOutputNode = 'Y';
-- ************************************************************************************************
-- Start of component replay messages processing
-- ************************************************************************************************
SET DB_OUT_PARAMS.MESSAGE_EXCEPTION[] = PASSTHR+U('Select CORREL_ID,
EXCEPTION_ID,
EXCEPTION_TSTMP,
FORMAT,
IS_EXCEPTION_TRUNC,
IS_MESSAGE_TRUNC,
IS_USR_FLDR_TRUNC,
MESSAGE,
MQ_MSG_ID,
QUEUE_NAME,
REQUEST_TYPE,
REPLAY_QUEUE,
REPLAY_RULE,
REPLY_TO_QUEUE,
REPLY_TO_QMGR,
EXCEPTION_STATUS,
RFH_USER,
REPLAY_COUNT,
ERROR_CODE,
USER_ID
From T_MESSAGE_EXCEPTION
Where ROWNUM < ' || Cast(evRef.maxMsgs As CHAR) || '
And EXCEPTION_STATUS = ''N''
And REPLAY_RULE = ''A''
And AUTO_REPLAY_TSTMP < CURRENT_TIMESTAMP
Order by EXCEPTION_ID');
Set Environment.Variables.failedMessages[] = DB_OUT_PARAMS.MESSAGE_EXCEPTION[];
-- For each message
For msgRef As evRef.failedMessages[] Do
-- Read MAX_REPLAY_COUNT and REPLAY_WITH_RFH from MESSAGE_REPLAY_RULE table
Create Field oleRef.MESSAGE_REPLAY_RULE.INPUT As DB_IN_PARAMS;
Set resultBranchName = 'MESSAGE_REPLAY_RULE';
CREATE LASTCHILD of DB_IN_PARAMS Name 'QUEUE_NAME' Value msgRef.QUEUE_NAME;
End For; |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Mar 20, 2007 4:34 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| Code: |
| Set Environment.Variables.failedMessages.[] = DB_OUT_PARAMS.MESSAGE_EXCEPTION[]; |
seems to be closer to what you're expecting.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| blueburn |
| Posted: Tue Mar 20, 2007 6:02 am Post subject: |
|
|
Newbie
Joined: 20 Mar 2007
Posts: 3
|
Hi Jeff,
Thanks for your idea, but that actually goes further away from what I want!
evRef now looks like this:
evRef
----failedMessage
--------MESSAGE_EXCEPTION
------------field1
------------field2
--------MESSAGE_EXCEPTION
------------field1
------------field2
and nothing goes into msgRef.
I also tried:
For msgRef As evRef.failedMessage.[] Do
But again, nothing is loaded into msgRef. I'm sure this is going to be simple when someone spots it, but I'm really stuck at the moment!
Tim |
|
| Back to top |
|
|
| elvis_gn |
| Posted: Tue Mar 20, 2007 9:42 am Post subject: |
|
|
Padawan
Joined: 08 Oct 2004
Posts: 1905
Location: Dubai
|
Hi blueburn,
I was hoping you would paste the Environment tree trace and not the root 
Why are you even using this
| Code: |
| Set evRef.failedMessages[] = DB_OUT_PARAMS.MESSAGE_EXCEPTION[]; |
you are simply copying one tree to another when you could have directly populated the 2nd tree on calling your function(DatabaseRead, if i'm correct  )....simply send the reference of evRef with failedMessages as the multiple segment to create 
And then the old FOR loop looks fine.
Regards. |
|
| 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
|
|
|
|
