| Author |
Message
|
| KIT_INC |
 Posted: Mon Jun 11, 2007 8:21 pm Post subject: Best practice for storing msgId |
 |
|
Knight
Joined: 25 Aug 2006
Posts: 589
|
| We have a requirement to store some data in a message together with the msgId of the message in a database. We like to get the data back using the MsgId as one criteria in the select statement. The question is what is the best practice to store the MsgId for this kind of use. Is it better to store it as binary or better to store it as character. We know that MsgId is binary fiels. We are not sure if casting it into character will always work. I was told that cast may not work with a series of binary zeros. But I may be wrong. If we store it as binary, we are not sure if we can do binary with the select statement ? |
|
| Back to top |
|
 |
| MQMD |
| Posted: Mon Jun 11, 2007 9:38 pm Post subject: |
|
|
Apprentice
Joined: 03 May 2007
Posts: 45
|
| You can do the binary(BLOB) with the select statment.There won't be any problem.But you will have serious performance issues as your table records will increase.So, If you can limit the table size go ahead with that. |
|
| Back to top |
|
|
| Vitor |
| Posted: Mon Jun 11, 2007 11:58 pm Post subject: Re: Best practice for storing msgId |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| KIT_INC wrote: |
| We are not sure if casting it into character will always work. |
It won't.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jun 12, 2007 3:01 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Have you thought about using the hex representation of the binary values as a string and key? 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| vk |
| Posted: Tue Jun 12, 2007 6:16 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
| Quote: |
| Have you thought about using the hex representation of the binary values as a string and key? |
Thats what I do in all the interfaces where I need to store the MsgId. Store the hexadecimal representation as a String. it will be 48 characters in length. I have not faced any issues with this till now.
Regards,
VK. |
|
| Back to top |
|
|
| KIT_INC |
| Posted: Thu Jun 14, 2007 7:45 am Post subject: |
|
|
Knight
Joined: 25 Aug 2006
Posts: 589
|
I did some testing
my esql is just
INSERT INTO Database.MyDB(MSG_ID) VALUES
(InputRoot.MQMD.MsgId);
I am using Oracle and have the MSG_ID column defined ad VARCHAR2 48 in length.
Under Oracle SQL Developer GUI, I can see
AMQ BKRQM1 v;qF ¾
If I output the msgid in an XML message I can see
<MSG_ID>414d5120424b52514d31202020202020763b714620001a02</MSG_ID>
Does it mean that I am storing it as hex as suggested. I have a feeling that Oracle has casted it into char because I have the column defined as VARCHAR2. Am I right ? The tools shows "AMQ BKRQM1 v;qF ¾ "
also indicated that.
If so what should I define the column as in Oracle if I want to store it as hex as suggested. |
|
| Back to top |
|
|
| vk |
| Posted: Thu Jun 14, 2007 9:28 am Post subject: |
|
|
Partisan
Joined: 20 Sep 2005
Posts: 302
Location: Houston
|
| Quote: |
| Does it mean that I am storing it as hex as suggested |
Yes. You can either do this conversion in ESQL before inserting into the table or let the database take care of the conversion. To do in ESQL issue a CAST without specifying the CCSID in incoming MQMD header -
CAST(InputRoot.MQMD.MsgId AS CHARACTER)
Regards,
VK. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Thu Jun 14, 2007 9:32 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
And, actually, what your database is showing is that you AREN'T storing as hex as recommended.
You should be seeing the "414d5120424b52514d31202020202020763b714620001a02" value in the database , not the one you're seeing.
Vk's code is correct, though. Cast it as CHARACTER, not specifying a CCSID.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
|
|
