| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| Storing MQ message id in Oracle |
« View previous topic :: View next topic » |
| Author |
Message
|
| pcoffey |
 Posted: Thu Aug 13, 2009 5:40 am Post subject: Storing MQ message id in Oracle |
 |
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Hi,
I have a message flow which stores MQ message ids in an Oracle table as a way of monitoring message receipt by an external application. The message id is stored as an Oracle RAW(24) datatype. The message id is inserted by various message flows and subsequently read by the monitoring message flow and also by another flow which receives responses from the external application.
I recently changed my message broker profile to use the setting NLS_LANG=AMERICAN_AMERICA.UTF8 (to correct euro character translation) and have since found that while the message ids are still correctly inserted into the Oracle table, they are no longer correctly read from the table. In my message flow I select all records from the table, store them in the Env and then try to update the table record by record, matching on the message id but the match fails as the message id (from an Oracle trace) appears to be converted in some way from the binary data.
Any help or tips appreciated.
Pauline |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Thu Aug 13, 2009 2:46 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Pauline,
Just so that we understand each other.
You store the msgId BLOB as RAW bytes in Oracle, right?
You retrieve the RAW bytes from Oracle into a BLOB.
When you compare the BLOBs they are not the same?
How do you retrieve the RAW bytes from Oracle?
How do you compare the BLOBs? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| pcoffey |
| Posted: Fri Aug 14, 2009 1:15 am Post subject: |
|
|
Apprentice
Joined: 03 Feb 2006
Posts: 33
|
Hi,
Thanks for the response.
Yes, I store the msgId BLOB as RAW(24) in Oracle and I retrieve the RAW bytes from Oracle directly into the LocalEnvironment tree via a select statement. I have since found that if I explicitly cast both the database column and the message id to CHAR, that the comparison again succeeds. Similarly if I explicitly cast the datbase column to BLOB, the comparison against the message id succeeds. I'm still unsure why setting NLS_LANG caused this change though.
Just FYI, the select statement from the table is as follows:
| Code: |
SET outLocalEnv.CMN_INFLIGHT_MESSAGE[] =
(SELECT T.PROCESS_NAME,
T.MSG_ID,
T.SOURCE_MSG_ID,
T.MSG_TIMESTAMP,
T.TARGET_QUEUE
FROM Database.CMN_INFLIGHT_MESSAGE AS T
WHERE T.EXCEEDED_TIME = 'N');
|
the subsequent comparison is now:
| Code: |
UPDATE Database.CMN_INFLIGHT_MESSAGE AS T
SET EXCEEDED_TIME = 'Y'
WHERE CAST(T.MSG_ID AS CHAR) = CAST(messageId AS CHAR);
|
whereas before I did not need these CASTS.
Thanks,
Pauline |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Aug 14, 2009 1:42 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| pcoffey wrote: |
Hi,
Thanks for the response.
Yes, I store the msgId BLOB as RAW(24) in Oracle and I retrieve the RAW bytes from Oracle directly into the LocalEnvironment tree via a select statement. I have since found that if I explicitly cast both the database column and the message id to CHAR, that the comparison again succeeds. Similarly if I explicitly cast the datbase column to BLOB, the comparison against the message id succeeds. I'm still unsure why setting NLS_LANG caused this change though.
Just FYI, the select statement from the table is as follows:
| Code: |
SET outLocalEnv.CMN_INFLIGHT_MESSAGE[] =
(SELECT T.PROCESS_NAME,
T.MSG_ID,
T.SOURCE_MSG_ID,
T.MSG_TIMESTAMP,
T.TARGET_QUEUE
FROM Database.CMN_INFLIGHT_MESSAGE AS T
WHERE T.EXCEEDED_TIME = 'N');
|
the subsequent comparison is now:
| Code: |
UPDATE Database.CMN_INFLIGHT_MESSAGE AS T
SET EXCEEDED_TIME = 'Y'
WHERE CAST(T.MSG_ID AS CHAR) = CAST(messageId AS CHAR);
|
whereas before I did not need these CASTS.
Thanks,
Pauline |
There could be a reason to not matching here.
My experience and information tells me that the messageId and correlationId are in fact byte[24]. This will not match at all a char[24] nor a cast String(byte[24]).
Some byte values are not legal char values.
Of course you could store the JMSMessageId which is a String of 48 characters representing the hex value of the byte array.....
This means you need to convert any byte array to its hex representation before you compare... The cast as char will only meddle the waters. Some values will get "fudged" as multiple source values (illegal char values) could have the same target char value...
If I had a choice I would explicitly cast to BLOB not to CHAR...
I would expect that with the NLS_LANG setting Oracle makes some assumptions as to what the stuff needs to be translated to.
Have fun
_________________
MQ & Broker admin |
|
| 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
|
|
|
|
