MQSeries.net :: View topic - Insert CDATASection into DB

MQSeries.net

Tech Exchange

Education

Certifications

Library

Info Center

SupportPacs

FAQÂ Â

Usergroups

RSS Feed - WebSphere MQ Support

RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Insert CDATASection into DB

Insert CDATASection into DB

« View previous topic :: View next topic »

Author

Message

WBI_user

Posted: Fri Nov 09, 2007 8:40 am Post subject: Insert CDATASection into DB

Partisan

Joined: 07 Aug 2001
Posts: 386

I have a message which I want to insert into the DB
<?xml version="1.0" encoding="UTF-8"?>
<LOG_MSG>
<TIMESTMP>2007-10-10 <?TIMESTAMP>
<MSG_DATA><![CDATA[000000148001 687762T ]]></MSG_DATA>
<MSG>12345</MSG>
</LOG_MSG>

ESQL in my DB node is
INSERT INTO Database.MY_LOG (TIMESTAMP, MSG_DATA) VALUES
(Root.XMLNS.LOG_MSG.TIMESTAMP,
Root.XMLNS.LOG_MSG.MSG_DATA,
Root.XMLNS.LOG_MSG.MSG);

I got an error
BIP5004E: XML parsing error ('Invalid character (Unicode: 0x1C) ') enc
ountered in MSG_DATA.
The reason for using the CDATA was we know that MSG_DATA can contain anything. But I thought that the ![CDATA[.. will tell the XML parsar to stay away from the data. I must be wrong.

I then change the ESQL to
INSERT INTO Database.MY_LOG (TIMESTAMP, MSG_DATA) VALUES
(Root.XMLNS.LOG_MSG.TIMESTAMP,
Root.XMLNS.LOG_MSG.MSG_DATA.(XML.CDataSection),
Root.XMLNS.LOG_MSG.MSG);

But I got error
BIP2498E: An error occurred whilst navigating to the '5'th path element of the field reference at the given location.See further messages for details of the error.
Correct the syntax of your ESQL expression in node ''My.MSG_INSERT.Main'', around line and column ''10.3'', then redeploy the message flow.

I assume that it is complaining about the .(XML.CDataSection),

Can some one help me on this ?

elvis_gn

Posted: Fri Nov 09, 2007 9:00 am Post subject:

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi WBI_user,

Use ASBITSTREAM and CAST it to CHAR....that you give you a string to put into Db.

Regards.

shalabh1976

Posted: Fri Nov 09, 2007 9:09 am Post subject:

Partisan

Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India

You need to put "(XML.CDATASection)" in front of the field identifier in XML
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate

WBI_user

Posted: Fri Nov 09, 2007 10:09 am Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

I tried
Root.XMLNS.LOG_MSG.(XML.CDataSection).MSG_DATA and
Root.XMLNS.LOG_MSG."(XML.CDataSection)".MSG_DATA

I still got "BIP2498E An error occurred whilst navigating to the '4'th path element of the field reference at the given location." in both cases.

shalabh1976

Posted: Fri Nov 09, 2007 10:14 am Post subject:

Partisan

Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India

Try:
Root.XMLNS.LOG_MSG.(XML.CDataSection)MSG_DATA
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate

WBI_user

Posted: Fri Nov 09, 2007 10:32 am Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

Thanks shalabh1976, but I still have the same error with
Root.XMLNS.LOG_MSG.(XML.CDataSection)MSG_DATA
From the user trace:
An error occurred whilst navigating to the '4'th path element of the field reference at the given location.

elvis_gn

Posted: Fri Nov 09, 2007 11:09 am Post subject:

Padawan

Joined: 08 Oct 2004
Posts: 1905
Location: Dubai

Hi WBI_user,

Quote:

<?xml version="1.0" encoding="UTF-8"?>
<LOG_MSG>
<TIMESTMP>2007-10-10 <?TIMESTAMP>
<MSG_DATA><![CDATA[000000148001 687762T ]]></MSG_DATA>
<MSG>12345</MSG>
</LOG_MSG>

I hope this is not your exact xml input....please paste your correct xml and your current code (don't type, do a copy-paste)...

Also, give details about your Broker version..

shalabh1976 wrote:

Root.XMLNS.LOG_MSG.(XML.CDataSection)MSG_DATA

This should have worked...to the utmost you could try it with "MSG_DATA"...

Regards.

WBI_user

Posted: Fri Nov 09, 2007 12:17 pm Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

Hi elvis_gn, you are right, I have to remove some sensitive data from the input message. I also have to make it small to fit in here. I noticed that I have a mistake in <?TIMESTAMP> which should be </TIMESTAMP>

However it seems that I am having problrm with the ESQL and not the input message. If there is a nedd, I'll work on getting a reasonable size input in the forum.

Hereis my full ESQL (cut and paste from my DB node)

INSERT INTO Database.MSG_LOG (MSGID, TIMESTAMP, MSG_HEADER, MSG_DATA, MESSAGE) VALUES
(Root.XMLNS.LOG_MSG.MSG_ID,
Root.XMLNS.LOG_MSG.TIMESTAMP,
Root.XMLNS.LOG_MSG.MSG_HEADER,
Root.XMLNS.LOG_MSG.(XML.CDataSection)MSG_DATA,
Root.XMLNS.LOG_MSG.MSG_BODY);

Hereis the trace output: It is complaining about the syntax of my ESQL

2007-11-09 14:52:10.488762 3617 RecoverableException BIP2488E: ('.MSG_INSERT_DB_MF_MSG_INSERT.Main', '6.3') Error det
ected whilst executing the SQL statement ''INSERT INTO Database.MSG_LOG ( MSGID, TIMESTAMP, MSG_HEADER, MSG_DATA, MESSAGE )
VALUES (Root.XMLNS.LOG_MSG.MSG_ID, Root.XMLNS.LOG_MSG.TIMESTAMP, Root.XMLNS.LOG_MSG.MSG_HEADER, Root.XMLNS.LOG_MSG.(XML.CData
Section)MSG_DATA, Root.XMLNS.LOG_MSG.MSG_BODY ) ''.
The message broker detected an error whilst executing the given statement. An exception has been thrown to cut short the SQL program.
See the following messages for details of the error.
2007-11-09 14:52:10.488772 3617 RecoverableException BIP2498E: ('.MSG_INSERT_DB_MF_MSG_INSERT.Main', '10.3') : An erro
r occurred whilst navigating to the '4'th path element of the field reference at the given location.
See further messages for details of the error.
Correct the syntax of your ESQL expression in node ''.MSG_INSERT_DB_MF_MSG_INSERT.Main
'', around line and column ''10.3'', then redeploy the message flow.
2007-11-09 14:52:10.488779 3617 ParserException BIP5009E: XML Parsing Errors have occurred.
Errors have occurred during parsing of XML.
Review further error messages for an indication to the cause of the errors.
2007-11-09 14:52:10.488787 3617 ParserException BIP5004E: XML parsing error ('Invalid character (Unicode: 0x1C) ') enc
ountered on line 1 column 2035 while parsing element 'MSG_DATA'.

I also tried
INSERT INTO Database.MSG_LOG (MSGID, TIMESTAMP, MSG_HEADER, MSG_DATA, MESSAGE) VALUES
(Root.XMLNS.LOG_MSG.MSG_ID,
Root.XMLNS.LOG_MSG.TIMESTAMP,
Root.XMLNS.LOG_MSG.MSG_HEADER,
Root.XMLNS.LOG_MSG.(XML.CDataSection)"MSG_DATA",
Root.XMLNS.LOG_MSG.MSG_BODY);

The trace output is the same because the )"MSG_DATA" is treated as MSG_DATA.

WBI_user

Posted: Fri Nov 09, 2007 12:26 pm Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

Sorry forget to mention that I am running WNB V6 CSD5 on AIX 53

shalabh1976

Posted: Fri Nov 09, 2007 12:55 pm Post subject:

Partisan

Joined: 18 Jul 2002
Posts: 381
Location: Gurgaon, India

Give me at least a small input file which is causing the problem because I want to look at the data in the CDATA section.
_________________
Shalabh
IBM Cert. WMB V6.0
IBM Cert. MQ V5.3 App. Prog.
IBM Cert. DB2 9 DB Associate

WBI_user

Posted: Mon Nov 12, 2007 1:51 pm Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

I used a good message and it works. The trace output is no longer complaining about the ESQL parametr. So the problem is caused by the message content. But I thought this is what CDATASection is for. Why is the parsar touching the content of my message ?
I am not sure how to cut and paste the bad message into the forum will work because unicode 0x1c which the trace complained about is a non printable and it will probably just show up as blank.

kimbert

Posted: Mon Nov 12, 2007 4:02 pm Post subject:

Jedi Council

Joined: 29 Jul 2003
Posts: 5542
Location: Southampton

Hi WBI_user,

1. The XMLNS parser is a very robust and well-tested parser. It is most unlikely that the parser is doing the wrong thing here.
2. The error reported in the user trace is simply saying that the message tree does not contain the element being referred to. That could easily be caused by a bad input message. You should ensure that your message flow can cope with bad input as well as good.
3. The error in your 'bad' input message was not within the CData section - it was in the preceding 'timestamp' element. So I don't see what the CData section can do about that.

I suspect that you should be taking elvis_gn's advice and using ASBITSTREAM.

WBI_user

Posted: Mon Nov 12, 2007 9:21 pm Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

Hi Kimbert, But the error
"BIP5004E: XML parsing error ('Invalid character (Unicode: 0x1C) ') enc
ountered on line 1 column 2035 while parsing element 'MSG_DATA'. " is
pointing to MSG_DATA which is the CDATASection.

I am not sure if <?TIMESTAMP> is what caused you to think that the timestamp field is wrong. But I have already said that it is a mistake I made. Can you point out to me why you think that the error is not within the CDATASection.

Also I agree with you 100% that the XMLNS parser is well tested. So I do not believe that it is the parser casuing the problem.

My bottom line is to store the CDATASection into the the DB.
In my initial post I said
"The reason for using the CDATA was we know that MSG_DATA can contain anything. But I thought that the ![CDATA[.. will tell the XML parsar to stay away from the data. I must be wrong. "

Is it true that I am worng ? Is there a way to tell the parser to leave the data alone and let it go without throwing a parser exception?

kimbert

Posted: Tue Nov 13, 2007 12:41 am Post subject:

Jedi Council

Joined: 29 Jul 2003
Posts: 5542
Location: Southampton

A CData section allows the element value to contain < and & ( and a couple of other characters ). It does not allow you to put illegal characters into the XML document. See http://www.w3.org/TR/2006/REC-xml-20060816/#charsets.
0x1C is not allowed anywhere in an XML document. If you want to represent that character in an element value, you must encode the string as either hexBinary or base64Binary.

WBI_user

Posted: Tue Nov 13, 2007 6:47 am Post subject:

Partisan

Joined: 07 Aug 2001
Posts: 386

Thanks Kimbert, I'll look into your suggested solution. Thanks for pointing out that CDatasection cannot contain illegal characters. I'll pursue with the message generator to see how the illegal character got into the CDATASection of the message they provided.

Display posts from previous:

Page 1 of 1

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » Insert CDATASection into DB

Jump to:

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

Protected by Anti-Spam ACP