| Author |
Message
|
| pfaulkner |
 Posted: Wed Oct 16, 2002 1:52 pm Post subject: ESQL CAST quesion |
 |
|
Master
Joined: 18 Mar 2002
Posts: 241
Location: Colorado, USA
|
I have a BLOB of data sent to me from an MVS QMGR. I need to take a substring of the BLOB and cast it to an integer.
I tried this:
DECLARE HDRLEN INTEGER;
SET HDRLEN = CAST(SUBSTRING(InputBody.BLOB FROM 13 FOR 7) as INTEGER CCSID InputRoot.Properties.CodedCharSetId);
but it doesn't work.
the data in the BLOB at this substring is 'F0F0F0F0F2F2F6' which is EBCIDC 0000226
Could anyone correct my ESQL statement please.
thanks |
|
| Back to top |
|
 |
| philip.baker |
| Posted: Wed Oct 16, 2002 2:02 pm Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Could it be that your InputRoot.Properties.CodedCharSetId is still set to the mainframe? CCSID = 500. You may want to try setting it to the native CCSID of the OS (e.g. for Windows, try 437) your broker is running on before executing your CAST statement.
_________________
Regards,
Phil |
|
| Back to top |
|
|
| lung |
| Posted: Wed Oct 16, 2002 5:27 pm Post subject: |
|
|
Master
Joined: 27 Aug 2002
Posts: 291
Location: Malaysia
|
pfaulkner,
Try this
| Code: |
| SET HDRLEN = CAST(SUBSTRING(InputBody.BLOB FROM 13 FOR 7) as CHAR CCSID 500); |
_________________
lung |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Oct 16, 2002 9:05 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Paul,
If above doesn't work then try using CAST ... CCSID .. Encoding option.
_________________
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 |
|
|
| philip.baker |
| Posted: Thu Oct 17, 2002 5:29 am Post subject: |
|
|
Voyager
Joined: 21 Mar 2002
Posts: 77
Location: Baker Systems Consulting, Inc. - Tampa
|
Paul,
What OS is your broker running on that receives the MVS message, and are you manipulating/setting the CCSID or Encoding in any other node?
_________________
Regards,
Phil |
|
| Back to top |
|
|
| pfaulkner |
| Posted: Thu Oct 17, 2002 7:10 am Post subject: |
|
|
Master
Joined: 18 Mar 2002
Posts: 241
Location: Colorado, USA
|
My message comes from MVS and my WMQI Qmgr is running on AIX.
I have tried using CCSID and Encoding for both AIX (819/273) and MVS (500/785) in my cast statement and get the same results.
Here is my ESQL
-------------------
DECLARE HDRLEN INTEGER;
DECLARE tempString CHARACTER;
SET tempString = SUBSTRING(InputBody.BLOB FROM 13 FOR 7);
--SET HDRLEN = CAST(tempString AS INTEGER CCSID 500 ENCODING 785);
SET HDRLEN = CAST(tempString AS INTEGER CCSID 819 ENCODING 273);
--------------------------------------------------------------------------------------
This is the output in the trace
----------------------------------
2002-10-17 09:31:30.140361 3606 UserTrace BIP2537I: Node 'EMT_REPLY.EMT_PARSE_REGISTRY_MSG_C_TO_XML1.Flow1 Extract Header': Executing statement 'SET HDRLEN = CAST(tempString AS INTEGER);' at (15, 1).
The statement being executed was 'SET HDRLEN = CAST(tempString AS INTEGER);'.
No user action required.
2002-10-17 09:31:30.140621 3606 UserTrace BIP2538I: Node 'EMT_REPLY.EMT_PARSE_REGISTRY_MSG_C_TO_XML1.Flow1 Extract Header': Evaluating expression 'CAST(tempString AS INTEGER)' at (15, 14).
The expression being evaluated was 'CAST(tempString AS INTEGER)'.
No user action required.
2002-10-17 09:31:30.140834 3606 UserTrace BIP2538I: Node 'EMT_REPLY.EMT_PARSE_REGISTRY_MSG_C_TO_XML1.Flow1 Extract Header': Evaluating expression 'tempString' at (15, 19).
The expression being evaluated was 'tempString'.
No user action required.
2002-10-17 09:31:30.146896 3606 Error BIP2628E: Exception condition detected on input node 'EMT_REPLY.RGSH.REPLY.QUEUE.REGISTRYSOAP.SI.COS1'.
The input node 'EMT_REPLY.RGSH.REPLY.QUEUE.REGISTRYSOAP.SI.COS1' 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.
2002-10-17 09:31:30.146942 3606 ImbComputeNode::evaluate
2002-10-17 09:31:30.146961 3606 SqlTypeCast::evaluate , 15, 14, CHARACTER, INTEGER
2002-10-17 09:31:30.146987 3606 imbWcsToInt64 , X'f0f0f0f0f2f2f6'
2002-10-17 09:31:30.148462 3606 UserTrace BIP2231E: Error detected whilst processing a message 'EMT_REPLY.RGSH.REPLY.QUEUE.REGISTRYSOAP.SI.COS1'.
The message broker detected an error whilst processing a message in node 'EMT_REPLY.RGSH.REPLY.QUEUE.REGISTRYSOAP.SI.COS1'. The message has been augmented with an exception list and has been propagated to the node's failure terminal for further processing.
See the following messages for details of the error.
2002-10-17 09:31:30.148609 3606 ImbComputeNode::evaluate
2002-10-17 09:31:30.148639 3606 SqlTypeCast::evaluate , 15, 14, CHARACTER, INTEGER
2002-10-17 09:31:30.148679 3606 imbWcsToInt64 , X'f0f0f0f0f2f2f6'
--------------------------------------------------------------------------------------
Here is the output in the log
--------------------------------
Oct 17 09:31:30 cose2e1 WMQIv210[165432]: (BROKER1.SOAPExecutionGroup)[3606]BIP2628E: Exception condition detected on input node 'EMT_REPLY.RGSH.REPLY
.QUEUE.REGISTRYSOAP.SI.COS1'. : BROKER1.85e23aec-f000-0000-0080-ef8ed9a98840: /build/S000_P/src/DataFlowEngine/ImbMqInputNode.cpp: 1248: ImbMqInputNod
e::readQueue: ComIbmMQInputNode: d42b27d7-ed00-0000-0080-f6615009c53c
Oct 17 09:31:30 cose2e1 WMQIv210[165432]: (BROKER1.SOAPExecutionGroup)[3606]BIP2230E: Error detected whilst processing a message in node 'EMT_REPLY.EM
T_PARSE_REGISTRY_MSG_C_TO_XML1.Flow1 Extract Header'. : BROKER1.85e23aec-f000-0000-0080-ef8ed9a98840: /build/S000_P/src/DataFlowEngine/ImbComputeNode.
cpp: 390: ImbComputeNode::evaluate: ComIbmComputeNode: 4b070bf1-f000-0000-0080-ef8ed9a98840.46f262c6-ef00-0000-0080-ef8ed9a98840
Oct 17 09:31:30 cose2e1 WMQIv210[165432]: (BROKER1.SOAPExecutionGroup)[3606]BIP2521E: (15, 14) : Error casting a value from CHARACTER to INTEGER. : BR
OKER1.85e23aec-f000-0000-0080-ef8ed9a98840: /build/S000_P/src/DataFlowEngine/ImbRdl/ImbRdlTypeCast.cpp: 163: SqlTypeCast::evaluate: :
Oct 17 09:31:30 cose2e1 WMQIv210[165432]: (BROKER1.SOAPExecutionGroup)[3606]BIP2595E: Error casting character string 'X'f0f0f0f0f2f2f6'' to an integer
. : BROKER1.85e23aec-f000-0000-0080-ef8ed9a98840: /build/S000_P/src/CommonServices/ImbUtility.cpp: 195: imbWcsToInt64: : |
|
| Back to top |
|
|
| kwelch |
| Posted: Thu Oct 17, 2002 11:32 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
Hi,
I am not sure about this but could it be that your field is too big to convert to INTEGER?
Can you convert it to CHARACTER then INTEGER ?
Look in the ESQL Reference there is a table that tells what you can convert to what.
Good Luck!
Karen |
|
| Back to top |
|
|
| lung |
| Posted: Thu Oct 17, 2002 6:22 pm Post subject: |
|
|
Master
Joined: 27 Aug 2002
Posts: 291
Location: Malaysia
|
Paul,
Try changing your CAST AS INT CCSID to CAST AS CHAR CCSID
Karen,
I've been trying to get my hands on a ccsid/encoding conversion table, but I couldn't find it anywhere. You mentioned that it can be found in the ESQL reference but I couldn't find it in there either... (ESQL Reference, Third Edition, March 2002)
_________________
lung |
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Oct 18, 2002 4:15 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
lung,
I was referring to a table that tells you that char to int is a valid cast blob to char etc. not a table with the actual values.
Karen |
|
| Back to top |
|
|
| pfaulkner |
| Posted: Fri Oct 18, 2002 7:37 am Post subject: |
|
|
Master
Joined: 18 Mar 2002
Posts: 241
Location: Colorado, USA
|
| My data comes from a system with a CCSID of 500 (MVS) and is being used on one with CCSID of 819 (AIX). When using the CAST should I be using the CCSID for the sending system or the receiving system? |
|
| Back to top |
|
|
| pfaulkner |
| Posted: Fri Oct 18, 2002 7:39 am Post subject: |
|
|
Master
Joined: 18 Mar 2002
Posts: 241
Location: Colorado, USA
|
| I have tried CAST with both CCSID to CHARACTER but the trace still shows the data as 'f0f0f0f0f2f2f6' which is EBCDIC 0000226. How do I get it to ASCII? I assume the error I get when trying to cast to an INT is because it's still EBCDIC. |
|
| Back to top |
|
|
| kwelch |
| Posted: Fri Oct 18, 2002 8:14 am Post subject: |
|
|
Master
Joined: 16 May 2001
Posts: 255
|
I thought INT was max of 4 bytes??????
Karen |
|
| Back to top |
|
|
| pfaulkner |
| Posted: Fri Oct 18, 2002 8:28 am Post subject: |
|
|
Master
Joined: 18 Mar 2002
Posts: 241
Location: Colorado, USA
|
Ok, fixed the problem.
According to the manual, CCSID only works for certain CAST's. It doesn't work for CHAR to INT but it does for BLOB to CHAR.
So I changed my code as follows (this time when I do a substring I put it into a BLOB field and then CAST it to CHAR using my CCSID).
DECLARE tempString BLOB;
DECLARE temp1 CHARACTER;
SET tempString = SUBSTRING(InputBody.BLOB FROM 13 FOR 7);
SET temp1 = CAST(tempString AS CHARACTER CCSID 500 ENCODING 785);
SET Environment.HDRLEN = CAST(temp1 AS INTEGER);
thanks everyone for all the input |
|
| Back to top |
|
|
|
|
