| Author |
Message
|
| ins0mniac2 |
 Posted: Mon Jul 25, 2005 3:12 pm Post subject: Converting BLOB into CLOB??? |
 |
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
I have come across an interesting problem, does anyone know of a way of converting a BLOB that is generated by the MQInput Node into a CLOB (clear text) to be inserted into an Oracle DB. I have several types of Data that gets processed by this flow.
The only way i know how to do it would be via the PARSE command, however, it doesnt always work especially if you are using Complicated XML Namespaces and other non support XML standard within Message broker.
The data can be XML, XMLNS, or MRM. I Need to find a generic way of converting the BLOB into a CLOB regardless of what type of data im dealing with.
I am running on WBIMB 5.0.4 using Oracle 9i
Thanx in advance |
|
| Back to top |
|
 |
| Bill.Matthews |
| Posted: Tue Jul 26, 2005 4:17 am Post subject: Re: Converting BLOB into CLOB??? |
|
|
Master
Joined: 23 Sep 2003
Posts: 232
Location: IBM (Retired)
|
| ins0mniac2 wrote: |
I have come across an interesting problem, does anyone know of a way of converting a BLOB that is generated by the MQInput Node into a CLOB (clear text) to be inserted into an Oracle DB. I have several types of Data that gets processed by this flow.
The only way i know how to do it would be via the PARSE command, however, it doesnt always work especially if you are using Complicated XML Namespaces and other non support XML standard within Message broker.
The data can be XML, XMLNS, or MRM. I Need to find a generic way of converting the BLOB into a CLOB regardless of what type of data im dealing with.
I am running on WBIMB 5.0.4 using Oracle 9i
Thanx in advance |
How about:
DECLARE InCCSID INT InputRoot.MQMD.CodedCharSetId;
DECLARE MYDATA CHAR CAST(InputRoot.BLOB.BLOB AS CHAR
CCSID InCCSID);
Then MYCATA is a string of character data
_________________
Bill Matthews |
|
| Back to top |
|
|
| ins0mniac2 |
| Posted: Tue Jul 26, 2005 7:57 am Post subject: Converting BLOB into CLOB??? |
|
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
What
DECLARE MYDATA CHAR CAST(InputRoot.BLOB.BLOB AS CHAR
CCSID InCCSID); Creates is a string representation of BLOB, but it doesnt convert the Hex into clear text. I need the BLOB to be converted into clear text. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jul 26, 2005 8:07 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
What is "clear text"?
How is the output of the CAST different than what you need?
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ins0mniac2 |
| Posted: Tue Jul 26, 2005 8:54 am Post subject: |
|
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
Clear text is the xml itself. Meaning if i have some hex in InputRoot.BLOB.BLOB i want to convert it to
<xml>
<TEST1>T</TEST1>
<TEST2>T2</TEST2>
</xml>
within the Compute Node.
The parse command returns this, but only if you know the domain you are parsing it to. I can have any number of Domains XML, XMLNS, MRM. I always need it to convert to origional text. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jul 26, 2005 9:02 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
I can't tell what you are trying to do.
Are you trying to get a bitstream that represents the input message, that you can then insert as a CLOB into the Oracle database?
Or are you trying to parse the input, and then insert pieces of it into the oracle database?
The first one is a frequently asked question and the answer is "ASBITSTREAM".
The second is not so frequently asked, and you haven't explained it very well at all if that's what you're trying to do.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ins0mniac2 |
| Posted: Tue Jul 26, 2005 9:06 am Post subject: |
|
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
I am trying to do the second part.
I am trying to Parse the BLOB and then instert the parsed message as clear text into a CLOB field into an Oracle DB.
Using the Parse command you have to specify a domain that you want to parse into, however i do not know what domain a given message will be. It can be MRM(various message sets), XML, XMLNS.
Need to do the parsing in a generic way so that clear text is always returned. |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jul 26, 2005 9:41 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
| ins0mniac2 wrote: |
I am trying to do the second part.
I am trying to Parse the BLOB and then instert the parsed message as clear text into a CLOB field into an Oracle DB. |
You want to insert the entire input message as a character stream into a single column? You don't want to insert a subsection, or individual fields into individual columns?
Then you don't need to parse the data. And once it's parsed, it's not a "message" any more, it's a set of tree elements. The only way to get that set of tree elements as any sort of text, much less "clear" text, is to use ASBITSTREAM. So why bother parsing it, if you're going to have to turn it back into a BLOB? Unless you need to make some decisions...
| Quote: |
| Need to do the parsing in a generic way so that clear text is always returned. |
You need to read up on the basic use of WBIMB. You appear to be missing some key concepts on how data is identified, parsed, and manipulated.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| ins0mniac2 |
| Posted: Tue Jul 26, 2005 9:46 am Post subject: |
|
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
Here is the scenario I am faced with.
I input a message on a queue as an example it looks like this
<xml>
<TEST1>T</TEST1>
<TEST2>T2</TEST2>
</xml>
when its read by the MQInput Node it is converted to BLOB.
Inside a compute Node i want to take the blob and turn it back into
<xml>
<TEST1>T</TEST1>
<TEST2>T2</TEST2>
</xml>
then i want to insert
<xml>
<TEST1>T</TEST1>
<TEST2>T2</TEST2>
</xml>
into Oracle CLOB field.
This is the ESQL I have tried
DECLARE PYLD_CL CHAR;
SET PYLD_CL = ASBITSTREAM(Root.BLOB.BLOB OPTIONS RootBitStream);
This does not return
<xml>
<TEST1>T</TEST1>
<TEST2>T2</TEST2>
</xml>
instead it returns 'X'' |
|
| Back to top |
|
|
| jefflowrey |
| Posted: Tue Jul 26, 2005 10:01 am Post subject: |
|
|
Grand Poobah
Joined: 16 Oct 2002
Posts: 19981
|
Then you want to use the CAST as Character, as originally mentioned by Bill.Matthews, and insert that.
You do not need to parse this data.
You must use the CCSID parameter, and it must specify the Input CCSID of the message, for the cast to give you what you are expecting, instead of a straight forward map from characters to hex digits.
_________________
I am *not* the model of the modern major general. |
|
| Back to top |
|
|
| mayur2378 |
| Posted: Tue Jul 26, 2005 10:14 am Post subject: |
|
|
Apprentice
Joined: 26 May 2004
Posts: 47
|
WHat Jeff is asking you to do is
| Code: |
| SET Environment.Variables.TEST=Cast(AsBitstream(InputBody) as char CCSID "Your CCSID Value" ENCODING "Your Encoding") |
Hopefully This makes some sense. If not then go back and read up on the AsBitstream function |
|
| Back to top |
|
|
| ins0mniac2 |
| Posted: Tue Jul 26, 2005 10:17 am Post subject: |
|
|
Apprentice
Joined: 25 Jul 2005
Posts: 33
|
OK i got it to work,
Thank you for your help! |
|
| Back to top |
|
|
|
|
