| Author |
Message
|
| newbee001 |
 Posted: Wed Aug 17, 2016 9:21 am Post subject: Handle XML in Java |
 |
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
Hi,
I've a requirement to insert 'xml' data from Java compute node to the DB2 table column of data type 'xml'.
Please guide me on how to write the code to insert xml data directly.
Thanks in advance. |
|
| Back to top |
|
 |
| mqjeff |
| Posted: Wed Aug 17, 2016 9:28 am Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
Steps - Read the documentation on the JavaCompute node
- Read the documentation on mapping from IIB types to database types
- determine what types are supported
- Read the documentation on performing database updates in a JavaCompute node
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 17, 2016 9:28 am Post subject: Re: Handle XML in Java |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| newbee001 wrote: |
| I've a requirement to insert 'xml' data from Java compute node to the DB2 table column of data type 'xml'. |
Why? Why from the JCN rather than the Compute node or (if you're a JDBC user) the Database node?
| newbee001 wrote: |
| Please guide me on how to write the code to insert xml data directly. |
What code have you tried? What happened? Why wasn't that what you wanted?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| newbee001 |
| Posted: Wed Aug 17, 2016 10:40 am Post subject: |
|
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
Hi,
Thanks jeff for your suggestion.
yes vitor, its a jdbc user and hence need to develop in java..
below is my xml structure
Properties
MQMD
XMLNSC
<employees>
<employee1>
<name>tom</name>
...
...
</employee1>
<otheremployees>
<empdetails><name>kevin</name>... <name>Ross</name></empdetails>
</otheremployees>
</employees>
Code is written as below -
MbElement root = inMessage.getRootElement();
MbElement otheremployees = rootMsg.getLastChild().getFirstChild();
byte[] mybytes=(byte[]) otheremployees.getValue();
String msgBdy = new String(mybytes);
I'm getting null pointer exception here.. 'empdetails' xml is not converted to byte format. Any suggestions pls?
Thanks in advance |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 17, 2016 11:27 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| newbee001 wrote: |
| yes vitor, its a jdbc user and hence need to develop in java.. |
So why not use the Database node?
| newbee001 wrote: |
| I'm getting null pointer exception here.. 'empdetails' xml is not converted to byte format. Any suggestions pls? |
I'm by no measure the best person to speak about Java. Having said that, are you sure getValue works on complex XML elements and, once you get it working, how do you plan to get a byte array into an XML type column?
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| newbee001 |
| Posted: Wed Aug 17, 2016 11:51 am Post subject: |
|
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
|
| Back to top |
|
|
| mqjeff |
| Posted: Wed Aug 17, 2016 12:00 pm Post subject: |
|
|
Grand Master
Joined: 25 Jun 2008
Posts: 17447
|
An MbElement doesn't always have a value.
Your code
| Code: |
| byte[] mybytes=(byte[]) otheremployees.getValue(); |
assumes that it does.
An MbElement isn't a serializable object. You need to take an extra step to get it as a bitstream.
_________________
chmod -R ugo-wx / |
|
| Back to top |
|
|
| newbee001 |
| Posted: Wed Aug 17, 2016 12:03 pm Post subject: |
|
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
yes jeff, the pbm is resolved after changing the code as below -
String ccsid = inMessage.getRootElement().getFirstElementByPath("Properties/CodedCharSetId").getValueAsString();
Integer ccsidValue = new Integer(ccsid);
byte[] mybytes=(byte[]) otheremployees.toBitstream(null, null, null, 0, ccsidValue, 0);
String str = new String(mybytes);
however getting exception while sending byte[] to DB2 column having 'xml' datatype.
As posted in previous msg, can I send xml as byte[] from JCN to Db2 column having 'xml' datatype? pls clarify. |
|
| Back to top |
|
|
| Vitor |
| Posted: Wed Aug 17, 2016 12:10 pm Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| newbee001 wrote: |
| Database node will accept only ODBC datasource name. Please correct me if i'm wrong. |
No, I'm wrong, would have sworn it took a JDBC not an ODBC.
we have JDBC provider defined for DB connection and JCN seems the better choice..
Yes it is, as that link indicates.
| newbee001 wrote: |
| BLOB in ESQL is equiv to byte[] in Java. |
As indicated here
| newbee001 wrote: |
| My question is, If i send xml in byte[] from Java to the DB2 column having XML datatype, will it work? |
Try it and see. Let us know how it works out for you. 
I personally doubt that a byte array will match the "internal representation" mentioned here, and I'm interested to see if the byte array counts as a binary data type (as described in that same link). Certainly the IIB data center indicates that it ought to work.
I wonder about the CCSID that the byte stream has vis a vis the database.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| newbee001 |
| Posted: Wed Aug 17, 2016 1:17 pm Post subject: |
|
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
I'm getting below Db2 exception by passing xml in string/byte[] format from JCN -
SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=XMLSTR
however am able to run below SQL successfully from JCN -
stmt.executeUpdate("insert into emp(xmlin) values('<test><name>sss</name></test>')");
In the below code, str has the same value - <test><name>sss</name></test>.. however not sure why if this passed as a parameter to SQL query is failing in JCN.. any thoughts?
String ccsid = inMessage.getRootElement().getFirstElementByPath("Properties/CodedCharSetId").getValueAsString();
Integer ccsidValue = new Integer(ccsid);
byte[] mybytes=(byte[]) otheremployees.toBitstream(null, null, null, 0, ccsidValue, 0);
String str = new String(mybytes); |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 17, 2016 1:43 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| newbee001 wrote: |
yes jeff, the pbm is resolved after changing the code as below -
| Code: |
String ccsid = inMessage.getRootElement().getFirstElementByPath("Properties/CodedCharSetId").getValueAsString();
Integer ccsidValue = new Integer(ccsid);
byte[] mybytes=(byte[]) otheremployees.toBitstream(null, null, null, 0, ccsidValue, 0);
String str = new String(mybytes); |
|
Your code is  dangerous. 
You are transforming the XML into a byte[] with anonymous (but known CCSID). (InputRoot.Properties.CodedCharSetId). I'd just have mandated UTF-8 with ccsid 1208 ...
Then you are creating a String from the byte[] using the platform's default ccsid??? and just pray to God that they are the same???
You should use the form new String (byte[] mybytes, String CharsetName)...
| Code: |
| String str = new String (mybytes, "UTF-8"); |
Now can you tell me the Java name of the charset defined by the CCSID on the InputRoot???
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| newbee001 |
| Posted: Wed Aug 17, 2016 3:34 pm Post subject: |
|
|
Novice
Joined: 04 Aug 2016
Posts: 10
|
Thanks, the problem is resolved.
Is this correct? pls advise.
byte[] mybytes=(byte[]) emp.toBitstream(null, null, null, 546, 1208, 0);
String empStr = new String(mybytes,"UTF-8"); |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Wed Aug 17, 2016 9:20 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
| newbee001 wrote: |
Thanks, the problem is resolved.
Is this correct? pls advise.
byte[] mybytes=(byte[]) emp.toBitstream(null, null, null, 546, 1208, 0);
String empStr = new String(mybytes,"UTF-8"); |
Yes this looks right   
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Thu Aug 18, 2016 5:57 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| Vitor wrote: |
| I wonder about the CCSID that the byte stream has vis a vis the database. |
*smug*
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Aug 18, 2016 5:07 pm Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20696
Location: LI,NY
|
He is not talking about it. He transformed the byte[] into a String. My guess is that he is sending a String downstream to the DB and the DB must know how to handle a Java String...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
|
|
