| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| How to retrive message from Database using ESQL |
« View previous topic :: View next topic » |
| Author |
Message
|
| dca_vikas |
 Posted: Mon Oct 25, 2004 5:34 am Post subject: How to retrive message from Database using ESQL |
 |
|
Newbie
Joined: 25 Oct 2004
Posts: 5
|
We are using WBIMB(Web Sphere Business Integration Message Broker, Version-5.1.2).
We store an incoming SOAP message from the HTTPInputNode in the database from a ComputeNode and subsequently try to retrieve it in a different message flow. We have been able to ascertain that our storing operations are going OK but retrieval always fail.
| Code: |
<---------------------------Inserting Message into DB------------>
DECLARE TId CHARACTER;
DECLARE CId CHARACTER;
SET TId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:TransactionID;
SET
CId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:CorrelationID;
DECLARE X BLOB;
SET X=BITSTREAM(InputRoot);
PASSTHRU('UPDATE GATEWAY_STATUS SET MESSAGE_STATUS_ID=103 MESSAGE_RECV= X
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId);
<-----------------select from DB the Bitsteram Message----------->
SET InputLocalEnvironment.Mesg_Id[]=CAST((PASSTHRU('SELECT
MESSAGE_RECV AS myMesg FROM GATEWAY_STATUS
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId)) AS CHARACTER);
SET OutputRoot=CAST ((InputLocalEnvironment.Mesg_Id[0].myMesg) AS
CHARACTER); |
|
|
| Back to top |
|
 |
| jefflowrey |
| Posted: Mon Oct 25, 2004 5:39 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
The function called 'BITSTREAM' is deprecated, and has been since 2.1.
Use 'ASBITSTREAM'.
And please search here for all of the other times we've talked about how to store and retrieve message data in a database.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| PGoodhart |
| Posted: Mon Oct 25, 2004 5:41 am Post subject: |
|
|
Master
Joined: 17 Jun 2004
Posts: 278
Location: Harrisburg PA
|
You are going to want to put a trace node in place and try to figure out exactly what is happening. You need to find the error messages.
Your attempt to overwrite the OutputRoot with a single bitstream is definately wrong. (I bet that is the major issue).
You want to write the select output as a bitstream to the OutputBody of a message in the BLOB domain.
Oh and Jeff is right, this has been discussed to death and there has to be a thread with some code in it that you can pirate.
_________________
Patrick Goodhart
MQ Admin/Web Developer/Consultant
WebSphere Application Server Admin |
|
| Back to top |
|
|
| JT |
| Posted: Mon Oct 25, 2004 6:42 am Post subject: |
|
|
Padawan
Joined: 27 Mar 2003
Posts: 1564
Location: Hartford, CT.
|
| Quote: |
| SET InputLocalEnvironment.Mesg_Id[]= |
This doesn't look right. I don't believe you can set anything that begins with"Input". Substitute "OutputLocalEnvironment" instead. |
|
| Back to top |
|
|
| kirani |
| Posted: Mon Oct 25, 2004 3:43 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Also, it's recommended that you use Environment for your temp storage.
_________________
Kiran
IBM Cert. Solution Designer & System Administrator - WBIMB V5
IBM Cert. Solutions Expert - WMQI
IBM Cert. Specialist - WMQI, MQSeries
IBM Cert. Developer - MQSeries
|
|
| Back to top |
|
|
| dca_vikas |
| Posted: Mon Oct 25, 2004 10:01 pm Post subject: Re: How to retrive message from Database using ESQL |
|
|
Newbie
Joined: 25 Oct 2004
Posts: 5
|
| dca_vikas wrote: |
We are using WBIMB(Web Sphere Business Integration Message Broker, Version-5.1.2).
We store an incoming SOAP message from the HTTPInputNode in the database from a ComputeNode and subsequently try to retrieve it in a different message flow. We have been able to ascertain that our storing operations are going OK but retrieval always fail.
| Code: |
<---------------------------Inserting Message into DB------------>
DECLARE TId CHARACTER;
DECLARE CId CHARACTER;
SET TId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:TransactionID;
SET
CId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:CorrelationID;
DECLARE X BLOB;
SET X=BITSTREAM(InputRoot);
PASSTHRU('UPDATE GATEWAY_STATUS SET MESSAGE_STATUS_ID=103 MESSAGE_RECV= X
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId);
<-----------------select from DB the Bitsteram Message----------->
SET InputLocalEnvironment.Mesg_Id[]=CAST((PASSTHRU('SELECT
MESSAGE_RECV AS myMesg FROM GATEWAY_STATUS
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId)) AS CHARACTER);
SET OutputRoot=CAST ((InputLocalEnvironment.Mesg_Id[0].myMesg) AS
CHARACTER); |
|
<----------Tried some of your suggession with new changed code below and the excerpts from trace file------------>
SET OutputLocalEnvironment.NUM[] = PASSTHRU('SELECT MESSAGE_RECV AS MSG FROM GATEWAY_STATUS');
Create FIRSTCHILD OF OutputRoot DOMAIN ('XMLNS') PARSE(ASBITSTREAM(OutputLocalEnvironment.NUM[0].MSG),InputRoot.Properties.Encoding,437);
OutputLocalEnvironment.NUM[ ] = PASSTHRU('SELECT MESSAGE_RECV AS MSG FROM GATEWAY_STATUS');' at (.DBTestFlow_Compute.Main, 6.3).
2004-10-26 11:16:32.890613 2560 UserTrace BIP2538I: Node 'DBTestFlow.Compute': Evaluating expression 'PASSTHRU('SELECT MESSAGE_RECV AS MSG FROM GATEWAY_STATUS')' at (.DBTestFlow_Compute.Main, 6.3 .
2004-10-26 11:16:32.890773 2560 UserTrace BIP2544I: Node 'DBTestFlow.Compute': Executing database SQL statement 'SELECT MESSAGE_RECV AS MSG FROM GATEWAY_STATUS' derived from (.DBTestFlow_Compute.Main, 6.3; expressions ''; resulting parameter values ''.
2004-10-26 11:16:32.891651 2560 UserTrace BIP2562I: Node 'DBTestFlow.Compute': Assigning a list to 'OutputLocalEnvironment.NUM[]'.
2004-10-26 11:16:32.892459 2560 UserTrace BIP2537I: Node 'DBTestFlow.Compute': Executing statement 'CREATE FIRSTCHILD OF OutputRoot DOMAIN 'XMLNS' PARSE(ASBITSTREAM(OutputLocalEnvironment.NUM[0].MSG), InputRoot.Properties.Encoding, 437);' at (.DBTestFlow_Compute.Main, 7.3).
2004-10-26 11:16:32.892681 2560 UserTrace BIP2538I: Node 'DBTestFlow.Compute': Evaluating expression 'ASBITSTREAM(OutputLocalEnvironment.NUM[0].MSG)' at (.DBTestFlow_Compute.Main, 7.5.
2004-10-26 11:16:32.893260 2560 UserTrace BIP2540I: Node 'DBTestFlow.Compute': Finished evaluating expression 'ASBITSTREAM(OutputLocalEnvironment.NUM[0].MSG)' at (.DBTestFlow_Compute.Main, 7.5. The result was 'X'''.
2004-10-26 11:16:32.893409 2560 UserTrace BIP2538I: Node 'DBTestFlow.Compute': Evaluating expression 'InputRoot.Properties.Encoding' at (.DBTestFlow_Compute.Main, 7.105).
2004-10-26 11:16:32.893711 2560 UserTrace BIP2537I: Node 'DBTestFlow.Compute': Executing statement 'RETURN TRUE;' at (.DBTestFlow_Compute.Main, 9.3).
2004-10-26 11:16:32.893882 2560 UserTrace BIP4124I: Message propagated to 'out' terminal of compute node 'DBTestFlow.Compute'.
2004-10-26 11:16:32.894027 2560 UserTrace BIP2538I: Node 'DBTestFlow.Trace': Evaluating expression 'Root' at (, 1.3).
2004-10-26 11:16:32.911933 2560 Error BIP2628E: Exception condition detected on input node 'DBTestFlow.MQInput'.
The input node 'DBTestFlow.MQInput' detected an error whilst processing a message. The message flow has been rolled-back and, if the message was being processed in a unit of work, it will remain on the input queue to be processed again. Following messages will indicate the cause of this exception.
Check the error messages which follow to determine why the exception was generated, and take action as described by those messages.
2004-10-26 11:16:32.912040 2560 RecoverableException BIP2230E: Error detected whilst processing a message in node 'DBTestFlow.Trace'.
The message broker detected an error whilst processing a message in node 'DBTestFlow.Trace'. An exception has been thrown to cut short the processing of the message.
See the following messages for details of the error.
2004-10-26 11:16:32.912128 2560 ParserException BIP5009E: XML Parsing Errors have occurred.
Errors have occurred during parsing of XML.
Review further error messages for an indication to the cause of the errors.
2004-10-26 11:16:32.912189 2560 ParserException BIP5004E: XML parsing error (Invalid document structure) encountered on line 1 column 1 while parsing element .
The above error was reported by the generic XML parser.
This message is usually caused by a badly-formed XML message. Check that the XML message being passed in is a well-formed XML message that adheres to the XML specification. Note that the line number and column number quoted above give the position where the parser discovered the problem. The actual error may be earlier in the message. Internal error codes : (182), ().
2004-10-26 11:16:33.913009 2560 Error BIP2630I: No backout queue or DLQ defined for a message to be backed out; input node 'DBTestFlow.MQInput' received from queue 'IN'
The input node 'DBTestFlow.MQInput' has received a message from queue 'IN' but this message has previously been backed out because of a processing error in the message flow. The MQMD 'backoutCount' of the message exceeds (or equals) the 'backoutThreshold' defined for the WebSphere Business Integration input queue. The message broker has attempted to propagate the message to the 'backoutRequeueQueueName' or the dead letter queue, but neither of these queues exist. The message is no longer processed by the message flow and continues to be backed out periodically. This situation can cause other messages to build up on the input queue until this message is removed or successfully processed.
Examine the other messages and the message flow to determine why the message is being backed out. Defining either the 'backoutRequeueQueue' or the dead letter queue will cause the message to be propagated to one of those queues.
Still not working |
|
| Back to top |
|
|
| PGoodhart |
| Posted: Tue Oct 26, 2004 4:14 am Post subject: |
|
|
Master
Joined: 17 Jun 2004
Posts: 278
Location: Harrisburg PA
|
Your method for retriving the message is still all hosed up. You can't assign a single character string to the OutputRoot of an message in the XML domain. (Hence the XML parser error!) OutputRoot is the entire message including the headers and the payload.
_________________
Patrick Goodhart
MQ Admin/Web Developer/Consultant
WebSphere Application Server Admin |
|
| Back to top |
|
|
| dca_vikas |
| Posted: Tue Oct 26, 2004 9:01 pm Post subject: |
|
|
Newbie
Joined: 25 Oct 2004
Posts: 5
|
| PGoodhart wrote: |
| Your method for retriving the message is still all hosed up. You can't assign a single character string to the OutputRoot of an message in the XML domain. (Hence the XML parser error!) OutputRoot is the entire message including the headers and the payload. |
Thanks got it worked, now working fine. |
|
| Back to top |
|
|
| dca_vikas |
| Posted: Wed Oct 27, 2004 3:37 am Post subject: Re: How to retrive message from Database using ESQL |
|
|
Newbie
Joined: 25 Oct 2004
Posts: 5
|
| dca_vikas wrote: |
We are using WBIMB(Web Sphere Business Integration Message Broker, Version-5.1.2).
We store an incoming SOAP message from the HTTPInputNode in the database from a ComputeNode and subsequently try to retrieve it in a different message flow. We have been able to ascertain that our storing operations are going OK but retrieval always fail.
| Code: |
<---------------------------Inserting Message into DB------------>
DECLARE TId CHARACTER;
DECLARE CId CHARACTER;
SET TId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:TransactionID;
SET
CId=InputRoot.XMLNS.*:Envelope.*:Body.*:Message.*:Header.*:MessageDetails.*:CorrelationID;
DECLARE X BLOB;
SET X=BITSTREAM(InputRoot);
PASSTHRU('UPDATE GATEWAY_STATUS SET MESSAGE_STATUS_ID=103 MESSAGE_RECV= X
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId);
<-----------------select from DB the Bitsteram Message----------->
SET InputLocalEnvironment.Mesg_Id[]=CAST((PASSTHRU('SELECT
MESSAGE_RECV AS myMesg FROM GATEWAY_STATUS
WHERE CORRELATION_ID=? AND TRANSACTION_ID=?',CId,TId)) AS CHARACTER);
SET OutputRoot=CAST ((InputLocalEnvironment.Mesg_Id[0].myMesg) AS
CHARACTER); |
|
Thanks every body finally it worked like as below
<----------Inserting ht emessage through update------>
DECLARE msgdata BLOB;
SET msgdata=ASBITSTREAM(InputBody,546,437);
PASSTHRU('UPDATE GATEWAY_STATUS_MESG as A SET A.MESSAGE_STATUS_ID=103,A.MESSAGE_RECV= ?
WHERE A.CORRELATION_ID=? AND A.TRANSACTION_ID=?',msgdata,CId,TId);
<---------------------------Selection of data form DB----------------->
DECLARE msgBlob BLOB;
SET msgBlob = THE(SELECT ITEM A.MESSAGE_RECV FROM
Database.WBIADMIN.GATEWAY_STATUS_MESG AS A
WHERE A.CORRELATION_ID = CId AND A.TRANSACTION_ID = TId);
--,CId,TId);
Create LASTCHILD OF OutputRoot DOMAIN 'XMLNS' PARSE(msgBlob,546,437); |
|
| 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
|
|
|
|
