| |
|
RSS Feed - WebSphere MQ Support
|
RSS Feed - Message Broker Support
|
  |
|
| xml and db2 |
« View previous topic :: View next topic » |
| Author |
Message
|
| GLL0905 |
 Posted: Tue Sep 02, 2003 1:29 am Post subject: xml and db2 |
 |
|
Apprentice
Joined: 25 Jun 2001
Posts: 40
Location: gauteng, sa
|
Hi everyone,
i am trying to store part of an xml message as data into a database. the message is too big to fit into a varchar column, so i am trying to use a 1MB blob. i get an error that i am trying to insert a null value into a not null column. the xml and code follows:
xml:
<?xml version="1.0" encoding="UTF-8"?><Message><SystemId>WAS1</SystemId><Body><Content><exceptionDetail><timeStamp>2003.08.15 05:16:59</timeStamp><message>External Reference Error</message></exceptionDetail></Content></Body></Message>
| Code: |
DECLARE bContent BLOB;
SET bContent = CAST(BITSTREAM(InputRoot.XML.Message.Body.Content)
AS BLOB CCSID InputRoot.MQMD.CodedCharSetId ENCODING InputRoot.MQMD.Encoding);
INSERT INTO Database.GLL0905.MESSAGEB (CONTENTB) VALUES (bContent); |
How do i store everything inside <Content> into a blob/clob column?
Why is a clob type not supported in mqsi when it is in db2.
why does the following statement result in an empty row in the database?
INSERT INTO GLL0905.MESSAGEC(CONTENTC) VALUES('<Message Type="audit">')
I am running mq 5.3, mqsi 2.1, db2 7.2 on win2000.
Thanks |
|
| Back to top |
|
 |
| Craig B |
| Posted: Tue Sep 02, 2003 7:26 am Post subject: |
|
|
Partisan
Joined: 18 Jun 2003
Posts: 316
Location: UK
|
Hi,
Looking at your ESQL it is worth noting that the ESQL BITSTREAM function cannot return the BLOB representation for specific fields and folders. It can only return a bitstream for the message body itself, and it can only do this if the message body has not been altered through the message flow. So if your flow has been through a compute node before reaching the ESQL, then a zero length BLOB will be created. So from this, it means you cannot use the bitstream function to return you just the "Content" XML tag and its children. If you have already passed through a compute node in your message flow then this would explain why it is zero length when inserted into the table. Or if you are running WMQI V2.1 CSD03 then there was a problem with the BITSTREAM function that meant a zero length BLOB was returned, and this was corrected in CSD04.
However, since the BITSTREAM function will not give you what you require we should not spend too much time into looking why this was a zero length blob.
Because of the aforementioned limitations in the BITSTREAM function, the ASBITSTREAM function was introduced in WMQI V2.1 CSD03. This can create a BLOB for a field/folder in the message tree, and can do this on newly created message trees as well. Therefore you may want to consider using the ASBITSTREAM function instead. In your ESQL you were casting the results to a BLOB anyway, and the BITSTREAM/ASBITSTREAM functions return a BLOB, so this was not needed.
If you want to store this as binary data, then you do not need the CAST function in your ESQL, and can just let this return a BLOB. If you want to store the XML data as character, then you can use the CAST with ENCODING and CCSID parms and CAST the result of ASBITSTREAM to CHAR.
Since you want to deal with a specific field/folder, then you will need to specify the final parameter on the ASBITSTREAM function that specifies FolderBitStream as the parser option.
| Code: |
SET bContent = ASBITSTREAM(InputRoot.XML.Message.Body.Content OPTIONS FolderBitStream)
|
OR
| Code: |
SET bContent = ASBITSTREAM(InputRoot.XML.Message.Body.Content, InputRoot.MQMD.Encoding, InputRoot.MQMD.CodedCharSetId,'','','',FolderBitStream)
|
However, if you are running on WMQI V2.1 CSD04 or below, and your broker is on AIX, then there is a problem with the FolderBitStream constant that means it is treated as RootBitStream. This problem was resolved in CSD05.
Hope this helps.
_________________
Regards
Craig |
|
| Back to top |
|
|
| GLL0905 |
| Posted: Wed Sep 10, 2003 3:04 pm Post subject: re: xml and db2 |
|
|
Apprentice
Joined: 25 Jun 2001
Posts: 40
Location: gauteng, sa
|
Hi Craig,
I have run a user trace with the following in place:
code in compute node:
| Code: |
DECLARE bContent BLOB;
SET bContent = ASBITSTREAM(InputRoot.XML.Message.Body.Content OPTIONS FolderBitStream);
INSERT INTO Database.GLL0905.MESSAGE(CONTENTB) VALUES(bContent); |
xml message:
| Code: |
| <Message Type="audit" Level="0"><Header><SystemId>WAS1</SystemId><ApplicationId>SRS</ApplicationId><EventTimestamp>2002-11-25 13:35:26.236</EventTimestamp><UserId>gll0905</UserId><ModuleId>Sales</ModuleId><ComponentId>ProductAgreement</ComponentId></Header><Body><Content><exDetail>20</exDetail></Content><Context>Context Info</Context></Body></Message> |
After putting the message through:
Database column CONTENTB is blank i.e. a null/blank row has been inserted into the column.
The trace shows the value of InputRoot.XML.Message.Body.Content and bContent as the same :
X'3c436f6e74656e743e3c657844657461696c3e32303c2f657844657461696c3e3c2f436f6e74656e743e'
I am not sure if what i want to achieve is possible:
store a large part (+1MB) of an xml message in a BLOB or CLOB column, whichever will work?
any suggestions would be greatly appreciated.
gll0905 |
|
| Back to top |
|
|
| kirani |
| Posted: Wed Sep 10, 2003 10:34 pm Post subject: |
|
|
Jedi Knight
Joined: 05 Sep 2001
Posts: 3779
Location: Torrance, CA, USA
|
Code suggested by Criag should work for you. Since one of your XML tag name is Body, which is a keyword, so I think you should try "Body" instead. So your code would be:
| Code: |
DECLARE bContent BLOB;
SET bContent = ASBITSTREAM(InputRoot.XML.Message."Body".Content OPTIONS FolderBitStream);
INSERT INTO Database.GLL0905.MESSAGE(CONTENTB) VALUES(bContent);
|
_________________
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 |
|
|
| GLL0905 |
| Posted: Tue Sep 16, 2003 12:59 am Post subject: re: xml and db2 |
|
|
Apprentice
Joined: 25 Jun 2001
Posts: 40
Location: gauteng, sa
|
Hi,
I have tried the change you suggested in the code, namely "Body".
I still get a null value returned. I created a new message with Content as the root tag. I then tried to store the whole message into the database:
| Code: |
| INSERT INTO Database.GLL0905.MESSAGE(CONTENTB) VALUES("InputRoot"."BLOB"."BLOB"); |
This also returns a null? |
|
| Back to top |
|
|
|
|
|
| |
|
Page 1 of 1 |
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
|
|
