| Author |
Message
|
| bobbee |
 Posted: Thu Mar 07, 2013 9:10 am Post subject: MQMD.msgID to SQL Server DB CHAR(24) |
 |
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
I have an issue I am trying to resolve. First I look in the Database and see that the VB app is putting the msgID into the COL which is defined as CHAR(24). I am trying to do the same in WMB. I have treated it as both BLOB and CHAR and in the DFDL as CHAR and now byte. So just trying to give a feeling here that I have tried alot of things. Now I turn here.
What I need to do is carry the MQMD.msgID through to a MQ message that goes to my DBLogger routine and insert that msgID into a SQLServer DB where the COL is defined as CHAR with a length of 24. Can I do this?
What is see in the debugger is x'423566578484984.........' 48 bytes between the quotes. What I see in the DB from the VB app is what you would expect as msgID in CHAR format 'AMQ MB8QMGR ¾96Q †' |
|
| Back to top |
|
 |
| lancelotlinc |
| Posted: Thu Mar 07, 2013 9:15 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
Each two characters represent one byte. You're looking at 24 bytes of data, not 48.
| Code: |
String refCorrelId = new String();
for( int i = 0; i < 24; i++ ){
String iHexStr = Integer.toHexString( msg.correlationId[i] );
if ( iHexStr.length()>2 ) iHexStr = iHexStr.substring( iHexStr.length()-2 );
if ( iHexStr.length()<2 ) refCorrelId = refCorrelId + "0";
refCorrelId = refCorrelId + iHexStr;
} |
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER
Last edited by lancelotlinc on Thu Mar 07, 2013 9:19 am; edited 1 time in total |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 07, 2013 9:18 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
| lancelotlinc wrote: |
| Each two characters represent one byte. You're looking at 24 bytes of data, not 48. |
Bobbee is well aware of that. |
|
| Back to top |
|
|
| lancelotlinc |
| Posted: Thu Mar 07, 2013 9:21 am Post subject: |
|
|
Jedi Knight
Joined: 22 Mar 2010
Posts: 4941
Location: Bloomington, IL USA
|
| mqjeff wrote: |
| lancelotlinc wrote: |
| Each two characters represent one byte. You're looking at 24 bytes of data, not 48. |
Bobbee is well aware of that. |
Ok. Then it would be a simple task to convert the double-byte hex value to a readable character.
_________________
http://leanpub.com/IIB_Tips_and_Tricks
Save $20: Coupon Code: MQSERIES_READER |
|
| Back to top |
|
|
| bobbee |
| Posted: Thu Mar 07, 2013 9:27 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
Ok, I do know about the double byte, I just explained this to my developer. I would be interested to see if the VB app is doing this. thanks for the code. I do have this hidden in one of my folders from another project in a galaxy far far away. I did not think I would have to resort to this. SO my disappointment is in WMB as the tight A.... it is on data types. I am surprised there was not a way to pic this up without typing and just 'drop it'.
Think to the Beatles song "Woke up, fell out of bed..............."
Thanks, will try this, at least this falls into the data model(s) and I don't have to go changing things all over the place. |
|
| Back to top |
|
|
| mqjeff |
| Posted: Thu Mar 07, 2013 9:35 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
That code does what Broker already does for you, it turns a string of 24 bytes into a string of 48 characters.
That's what you're trying to NOT do. |
|
| Back to top |
|
|
| bobbee |
| Posted: Thu Mar 07, 2013 10:08 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| did not dissect it, assumed from the discussion it was compression not expansion. |
|
| Back to top |
|
|
| bobbee |
| Posted: Thu Mar 07, 2013 10:12 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
thanks Jeff, this worked
CALL CopyEntireMessage();
set Environment.Variables.BLOB = InputRoot.MQMD.MsgId;
Set Environment.Variables.Char437 = CAST(InputRoot.MQMD.MsgId AS CHARACTER CCSID 437); |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Mar 07, 2013 4:33 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| bobbee wrote: |
thanks Jeff, this worked
CALL CopyEntireMessage();
set Environment.Variables.BLOB = InputRoot.MQMD.MsgId;
Set Environment.Variables.Char437 = CAST(InputRoot.MQMD.MsgId AS CHARACTER CCSID 437); |
At your own risk and peril! A byte[24] cannot always be converted to a char[24]. Some bytes have values that are not legal for a char...
This is why you cannot do what you are trying to do. It is not a String, even if it might sometimes have the looks of one. 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| bobbee |
| Posted: Thu Mar 07, 2013 5:14 pm Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
| ok, so how do you take a 24 byte field and save it in a 24 byte field in SQL Server. I know on z/OS I can do this in two lines of code. But this happens to be zLinux. Windows and WMB |
|
| Back to top |
|
|
| longng |
| Posted: Thu Mar 07, 2013 6:06 pm Post subject: |
|
|
Apprentice
Joined: 22 Feb 2013
Posts: 42
|
| bobbee wrote: |
| ok, so how do you take a 24 byte field and save it in a 24 byte field in SQL Server. I know on z/OS I can do this in two lines of code. But this happens to be zLinux. Windows and WMB |
I have not dealt with SQL Server that often, but I tend to agree with fjb_saper in this case. To your question with another question!
Would it be feasible to define a BINARY field in SQL Server to receive a WMQ's MsgID? |
|
| Back to top |
|
|
| bobbee |
| Posted: Fri Mar 08, 2013 12:52 am Post subject: |
|
|
Knight
Joined: 20 Sep 2001
Posts: 545
Location: Tampa
|
This would have to be a cutover item. there is an application running currently that is supporting the DMV and it is putting the msgID in there. I do not know if that app is using the ID or just storing it. My app is reading back the table to get the generated Id which is the key to store in the output message table. Right now the msgID is the only field I can see that is unique. I maybe able to use the timestamp I am putting in there. In this case the msgId just becomes a stored value. Useless yes, but that is what it is today and things have to stay the same. I will follow up on this. Jeff suggested this too but I gave him the same sad story and I went down a different path.
Oh well, life under a dictatorship. Thanks, Time to shower and get on my flight home. |
|
| Back to top |
|
|
| rekarm01 |
| Posted: Fri Mar 08, 2013 1:41 am Post subject: Re: MQMD.msgID to SQL Server DB CHAR(24) |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 1415
|
| bobbee wrote: |
| First I look in the Database and see that the VB app is putting the msgID into the COL which is defined as CHAR(24). I am trying to do the same in WMB. |
That is unfortunate, as the VB app is doing the wrong thing. It should either use a safer binary-to text encoding for the MsgId (such as HexBinary or Base64), or define the database column as a BINARY data type. If it's really not possible to fix the VB app, or database column type, and it's important that the WMB behavior matches the VB app behavior, then at the very least try to confirm which character encoding the VB app is using to convert bytes to characters, and try to avoid performing any character-like operations on the resulting character sequence.
| fjb_saper wrote: |
| At your own risk and peril! A byte[24] cannot always be converted to a char[24]. Some bytes have values that are not legal for a char... |
While that's true for some character encodings (such as UTF-8), that's not the problem for IBM-437; every byte sequence represents a legal character sequence.
But there are other problems with IBM-437. The control codes (X'00' to X'1F') are ambiguous. They can map to graphics characters, IBM-PC control characters, or ISO control characters, depending on the context. Different applications may convert them differently, and may not preserve round-trip integrity from byte -> char -> ... -> byte. Some applications also don't handle null characters well, so that may cause further problems. |
|
| Back to top |
|
|
|
|
