| Author |
Message
|
| mverh |
 Posted: Thu Mar 07, 2002 8:12 am Post subject: |
 |
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
Hi, I have an interesting (at least I think it is) problem. I have a flow that logs all messages that go through our broker to a message warehouse. The fields of the MQMD each have there own column and the payload of the message is in a separate column, currently the payload is stored as varchar but probably will need to be changed due to the size limitation on varchar fields. All messages logged to the warehouse are in the XML domain.
The load to the warehouse works with no problems.
I then wrote an extract flow. The problem I encounter is that the XML stored as varchar is treated as data and not XML. Hence if I restore the message via an extract flow WMQI replaces all '<' with '<' (this applies to all the other special chars as well) and the message is no longer parseable.
Does anybody know a way to treat the field returned as XML and make it part of the tree and not data so that it is parseable on replay.
Note that if I select the XML from the d/b using the HEX function and reset the content to BLOB I can this to work but then I'm limited to only 16366 characters, so I'd prefer not to have to do it this way.
|
|
| Back to top |
|
 |
| kirani |
| Posted: Thu Mar 07, 2002 9:34 am Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
|
| Back to top |
|
|
| mverh |
| Posted: Thu Mar 07, 2002 10:11 am Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
Thanks for the response, it's not how the database stores it but rather how I build the message after reading it from the database...WMQI treats the data I retrieve as just that - data...so if my original message is <xml>some data</xml> I store '<xml>some data</xml>' in the database and then when I try and rebuild my message WMQI interprets the string retrieved via the database node as data not as part of the tree...I want the tag <xml> in my string to be intepretted as a part of the XML tree not as data...
Like I said in my first post if I jump through hoops and treat the data retrieved from the d/b as a BLOB then I can get this to work but this places a size restriction on me...
If ESQL had a HEX function then I could get around the size restriction and use the BLOB technique... |
|
| Back to top |
|
|
| mverh |
| Posted: Thu Mar 07, 2002 10:21 am Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
| Solved. I used the BITSTREAM function to essentially cast my string as a BLOB and then used a resent content descriptor to go from the XML domain to BLOB domain... |
|
| Back to top |
|
|
| mverh |
| Posted: Thu Mar 07, 2002 10:28 am Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
| NOT SOLVED! I thought the bitstream worked but it didn't...I saw tags in my message and assumed it worked but they were tags of other fields that were retrieved from the d/b call...so I am back to square one... |
|
| Back to top |
|
|
| kirani |
| Posted: Thu Mar 07, 2002 1:55 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Hi,
This may solve your problem,
First Define a BLOB filed in your database that will store incoming message body. (for example, your field name is DATA). In your MQInput node, set your message domain to BLOB.
Either you can use Database or Warehosue node to store your message body into database. If using Warehouse node, add your db table name in output pane. Add field name to store your message body. Right click on that field name and select "Add mapping". In Input message ESQL, type Root."BLOB"."BLOB"
This will store your incoming message into database.
Now, in your compute node where you want to retrieve this message, modify following ESQL as per your requirement. Make sure you select "Copy Message Headers only".
SET OutputRoot."BLOB"."BLOB" = THE (SELECT ITEM tbl.DATA FROM Database.tbl_name WHERE .....);
Now if you are storing XML messages into DB, add RCD after this compute node to change your message domain from BLOB to XML.
Hope this is helpful!
Regards,
Kiran
|
|
| Back to top |
|
|
| mverh |
| Posted: Fri Mar 08, 2002 3:15 pm Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
Kiran, have you tried the ESQL you posted? The reason I ask is I think the "SET OutputRoot."BLOB"."BLOB" = THE (SELECT ITEM tbl.DATA FROM Database.tbl_name WHERE .....);" won't necessarily work since I'll need to CAST the data returned in the select to BLOB which would require in to be in HEX. So I believe it would have to look like:
SET OutputRoot."BLOB"."BLOB" = CAST(THE (SELECT ITEM HEX(tbl.DATA) FROM Database.tbl_name WHERE .....) AS BLOB);
The issue with this is the max size that tbl.DATA could be is 16366, HEX pukes if the argument is greater than 16336 bytes. Which is the crux of my problem.
If I could get around the limitation on HEX then using this BLOB technique will work (I've done it).
Does anyone have some ESQL to convert a string to its HEX representation? Seems like a builtin HEX function in ESQL would be nice... |
|
| Back to top |
|
|
| kirani |
| Posted: Fri Mar 08, 2002 4:53 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
I used the above ESQL in my previous project and I am very confident that it works!! Since you are selecting BLOB field from the database you don't have to explicitly CAST it.
Regards,
Kiran
|
|
| Back to top |
|
|
| mq_developer |
| Posted: Fri Mar 08, 2002 6:53 pm Post subject: |
|
|
Voyager
Joined: 18 Feb 2002
Posts: 82
|
As kiran said , as long as the datatype of column in DB2 is BLOB (where you store ur XML) , you dont need to do any casting.I am using the same functionality in my projects.
Cheers,
Ram |
|
| Back to top |
|
|
| mverh |
| Posted: Sat Mar 09, 2002 8:43 am Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
| Great...I'll make the change and rty it out...btw in DB2 if you define the BLOB column as BLOB(512K) say and the message you store is 32K does DB2 store 32K or 512K? |
|
| Back to top |
|
|
| mverh |
| Posted: Wed Mar 13, 2002 7:28 am Post subject: |
|
|
Voyager
Joined: 06 Mar 2002
Posts: 97
|
| Update to let you all know I defined the field in the table as BLOB and had no problem extracting the messages. Also, the DB2 manual states that a BLOB is a varying length field so that answers my question regarding how DB2 stores the blob. |
|
| Back to top |
|
|
| shreevats |
| Posted: Wed Jan 21, 2004 9:46 am Post subject: |
|
|
Newbie
Joined: 10 Apr 2002
Posts: 3
|
Hi All,
I have a similar requirement.
I want to update some base64 encoded data coming from the XML message onto a database table of type CLOB without changing the data.
The XML schema contains a type of "base64Binary" which would contain the base64 encoded data. Please note that the encoded data is not coming in the CDATA section.
When I try to insert it to the DB, the base64 data is interpreted as hex values and it causes the inserting to fail. I wanted to know in MQSI is there a way to let know the MQSI XML parser to not interpret the incoming data.
I have been trying different options like casting the base64 data as BLOB and also with CCSID 1208, casting as BITSTREAM.. but no luck so far.
If anybody has managed to solve this problem please do let me know.
Thanks in advance
Sri |
|
| Back to top |
|
|
|
|
