| Author |
Message
|
| scravr |
 Posted: Wed Dec 27, 2017 4:39 am Post subject: JAVA BLOB to Oracle Clob |
 |
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
HI to ALL,
I am sure this topic comeup many many times before, but still sometimes simple becomes difficault.
This is with MQ7.5 and IIB10:
I have multiple flows puting XML msg (via ESQL Node) to a queue. One of the elements is BLOB: (it could be one of following 3 types)
| Code: |
SET myBlob = CAST(ASBITSTREAM(inRef.DFDL OPTIONS RootBitStream SET inRef.Properties.MessageSet TYPE inRef.Properties.MessageType FORMAT inRef.Properties.MessageFormat) AS BLOB CCSID inRef.Properties.CodedCharSetId);
SET myBlob = CAST(ASBITSTREAM(inRef.XMLNSC OPTIONS RootBitStream SET inRef.Properties.MessageSet TYPE inRef.Properties.MessageType FORMAT inRef.Properties.MessageFormat) AS BLOB CCSID inRef.Properties.CodedCharSetId); |
Or just the input blol on MQInput node.
This process cannot be changed. It needs to stay as is.
Then a generic flow gets these xml msgs and load to Oracle DB in ESQL. All elements are insert as CHAR (the blob element is defined as oracle Clob):
| Code: |
DECLARE message CHARACTER '';
DECLARE msgInBlob BLOB '';
...
...
SET msgInBlob = inRef.MSG_BLOB;
SET message = CAST(msgInBlob AS CHARACTER CCSID InputRoot.Properties.CodedCharSetId);
...
...
INSERT INTO Database.oracle-table (........
|
I am trying to replace the generic INSERT ESQL flow by a stand alown JAVA utility (not a JCN; just simple java/mq util):
| Code: |
1. Open queue for get with:
int qOpenGetOptions = MQC.MQOO_FAIL_IF_QUIESCING + MQC.MQOO_INPUT_SHARED;
2. Get msg:
...
...
MQMessage msg = new MQMessage();
MQGetMessageOptions qmo = null;
gmo = new MQGetMessageOptions();
gmo.options = MQC.MQGMO_ALL_MSGS_AVAILABLE + MQC.MQGMO_NO_WAIT + MQC.MQGMO_FAIL_IF_QUIESCING + MQC.MQGMO_SYNCPOINT;
myQ.get(msg, gmo);
...
...
3. read msg bogy:
String xmlRecord = msg.readString(msg.getMessageLength());
4. Parshing XML with javax.xml.parsers.Document.* to get individual element.
5. Build PreparedStatement with each element. For the BLOC:
...
...
String MSG_OB = eElement.getElementsByTagName("MYBLOB").item(i).getTextContent();
Clob msgObClob = dbc.getClob();
msgObClob.setString(1, MSG_OB);
...
preparedStatement.setClob(22, msgObClob);
...
... |
The problem I have is BLOB is not converted to CHAR (or CLOB) when insert to oracle. msgObClob is un-readable.
what I am missing or convertion?
I tried getting properties (encoding, ccsid....) from msg, but nothing is showing on this function:
| Code: |
public static void getProp(MQMessage Msg)
{
Enumeration props = null;
try {
props = Msg.getPropertyNames("m%");
if (props != null) {
while (props.hasMoreElements()) {
String propName = (String) props.nextElement();
Object propObject;
propObject = Msg.getObjectProperty(propName);
String propValue = propObject.toString();
}
}
Enumeration<String> e = (Enumeration<String>) Msg.getPropertyNames("M%");
while (e.hasMoreElements()) {
String name = e.nextElement();
System.out.println(name + "=" + Msg.getStringProperty(name));
}
} catch (MQException e1) {
e1.printStackTrace();
}
}
|
How can I get ccsid, encoding, or other property to convert BLOC to Clob in JAVA?
Thanks
MA |
|
| Back to top |
|
 |
| fjb_saper |
| Posted: Wed Dec 27, 2017 5:01 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Well Oracle doesn't care about the CCSID of a BLOB.
So if you store stuff as a BLOB you need to have a field in the DB where you set CCSID and another for Encoding (endianness)... So that when you retrieve the data you know what CCSID to use...
Otherwise you should always have the incoming message's CCSID on InputRoot.Properties.CodedCharSetId
Have fun 
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| scravr |
| Posted: Thu Dec 28, 2017 5:29 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
What I am looking for is a sample of how to get CCSID plus encoding from message itself, and a sample code of how to convert BLOB to Char/Clob.
I am not sure if the open or get oprions are correct. Anyone can verify?
I am not looking to add new elements to DB. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Thu Dec 28, 2017 6:04 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| scravr wrote: |
What I am looking for is a sample of how to get CCSID plus encoding from message itself, and a sample code of how to convert BLOB to Char/Clob.
I am not sure if the open or get oprions are correct. Anyone can verify?
I am not looking to add new elements to DB. |
Well it looks like IBM considers CCSID and Encoding to me more part of the message metadata than of the payload. As a consequence you will find them on the InputRoot.Properties and if applicable also on the transport header (MQMD, HTTPHeader etc...)
You will not find them on the message as the information transported should be agnostic of CCSID or and encoding... My understanding is that the InputRoot.Properties value will reflect the value in the transport header.
However you might want to verify how that plays out in case of header chaining (MQRFH) etc...
Have fun
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| scravr |
| Posted: Thu Dec 28, 2017 10:19 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
| I am looking for sample code getting msg properties and how to do java conversion. |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Fri Dec 29, 2017 6:27 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
Just as you did in ESQL
First you extract the BLOB int a byte[], then you use the new String(byte[] msgbytes, String codepage) to render as a String....
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Jan 02, 2018 9:23 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
This really does not help.
I think it because msg is generate by unix and my java test is debug on Windows
I am looking to see how translate regardless of os.
Someone done this before? |
|
| Back to top |
|
|
| fjb_saper |
| Posted: Tue Jan 02, 2018 9:45 am Post subject: |
|
|
Grand High Poobah
Joined: 18 Nov 2003
Posts: 20756
Location: LI,NY
|
| scravr wrote: |
This really does not help.
I think it because msg is generate by unix and my java test is debug on Windows
I am looking to see how translate regardless of os.
Someone done this before? |
You cannot translate regardless of OS because the strings are not portrayed the same. For instance in Unix the EOL is LF (x0A) whereas in Windows it is CR/LF or x0D0A... and for multi-byte characters little or big endian might also come in play...
So if you have a BLOB in the input message you need to transform it into CHAR or String using the CCSID and Encoding of the input message....
So lookup what you have as methods on the MbElement for that... or revert to what I described in my previous post...
So if you save a String into the CLOB you might be good, but saving a BLOB into the CLOB and hoping it might automagically appear as a String is certain disaster ...
_________________
MQ & Broker admin |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 02, 2018 9:55 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| fjb_saper wrote: |
| scravr wrote: |
This really does not help.
I think it because msg is generate by unix and my java test is debug on Windows
I am looking to see how translate regardless of os.
Someone done this before? |
You cannot translate regardless of OS because the strings are not portrayed the same. |
Also most if not all Unix machines have processors with a different endieness than Windows.
This is why messages / databases have the concept of CCSID and encoding. If it didn't matter at an OS level it wouldn't be necessary.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Jan 02, 2018 10:59 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
I think you need to look into this thread from beggining.
I am looking for sample of how to get ccsid and use it.
...
...
... |
|
| Back to top |
|
|
| Vitor |
| Posted: Tue Jan 02, 2018 11:05 am Post subject: |
|
|
Grand High Poobah
Joined: 11 Nov 2005
Posts: 26093
Location: Texas, USA
|
| scravr wrote: |
| I think you need to look into this thread from beggining. |
I did. I took your comment to be asking how to code in an OS-agnostic way without reference to the CCSID / Encoding information.
| scravr wrote: |
I am looking for sample of how to get ccsid and use it.
|
Which had been provided by my worthy associate and you seemed to be ignoring in your perceived quest for an OS-agnostic method.
_________________
Honesty is the best policy.
Insanity is the best defence. |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Jan 02, 2018 11:42 am Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
I got the msg.Encoding into integer and
String text = new String (BYTES, Encoding);
Generated chins chars.
Again msg generated on unix and java debug on windows.
What i am missing? |
|
| Back to top |
|
|
| scravr |
| Posted: Tue Jan 02, 2018 3:47 pm Post subject: |
|
|
Partisan
Joined: 03 Apr 2003
Posts: 391
Location: NY NY USA 10021
|
Lets make it simple:
How java code should look like if it run on same unix os as esql flow generating the msg? |
|
| Back to top |
|
|
| timber |
| Posted: Wed Jan 03, 2018 2:57 am Post subject: |
|
|
Grand Master
Joined: 25 Aug 2015
Posts: 1292
|
| I've sort of lost the big picture somewhere along the line. Please can you restate the problem. |
|
| Back to top |
|
|
| zpat |
| Posted: Wed Jan 03, 2018 3:55 am Post subject: |
|
|
Jedi Council
Joined: 19 May 2001
Posts: 5866
Location: UK
|
If your message is XML, and the MQMD.Format is MQSTR - you should code MQGMO_CONVERT on the MQ GET.
Assuming the message is a string and any binary data is encoded as BASE64 that is.
_________________
Well, I don't think there is any question about it. It can only be attributable to human error. This sort of thing has cropped up before, and it has always been due to human error. |
|
| Back to top |
|
|
|
|
