ASG
IBM
Zystems
Cressida
Icon
Netflexity
 
  MQSeries.net
Search  Search       Tech Exchange      Education      Certifications      Library      Info Center      SupportPacs      LinkedIn  Search  Search                                                                   FAQ  FAQ   Usergroups  Usergroups
 
Register  ::  Log in Log in to check your private messages
 
RSS Feed - WebSphere MQ Support RSS Feed - Message Broker Support

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JAVA BLOB to Oracle Clob

Post new topic  Reply to topic Goto page 1, 2  Next
 JAVA BLOB to Oracle Clob « View previous topic :: View next topic » 
Author Message
scravr
PostPosted: Wed Dec 27, 2017 4:39 am    Post subject: JAVA BLOB to Oracle Clob Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
fjb_saper
PostPosted: Wed Dec 27, 2017 5:01 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
View user's profile Send private message Send e-mail
scravr
PostPosted: Thu Dec 28, 2017 5:29 am    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
fjb_saper
PostPosted: Thu Dec 28, 2017 6:04 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
View user's profile Send private message Send e-mail
scravr
PostPosted: Thu Dec 28, 2017 10:19 am    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
Location: NY NY USA 10021

I am looking for sample code getting msg properties and how to do java conversion.
Back to top
View user's profile Send private message Send e-mail MSN Messenger
fjb_saper
PostPosted: Fri Dec 29, 2017 6:27 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
View user's profile Send private message Send e-mail
scravr
PostPosted: Tue Jan 02, 2018 9:23 am    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
fjb_saper
PostPosted: Tue Jan 02, 2018 9:45 am    Post subject: Reply with quote

Grand High Poobah

Joined: 18 Nov 2003
Posts: 20696
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
View user's profile Send private message Send e-mail
Vitor
PostPosted: Tue Jan 02, 2018 9:55 am    Post subject: Reply with quote

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
View user's profile Send private message
scravr
PostPosted: Tue Jan 02, 2018 10:59 am    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
Vitor
PostPosted: Tue Jan 02, 2018 11:05 am    Post subject: Reply with quote

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
View user's profile Send private message
scravr
PostPosted: Tue Jan 02, 2018 11:42 am    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
scravr
PostPosted: Tue Jan 02, 2018 3:47 pm    Post subject: Reply with quote

Partisan

Joined: 03 Apr 2003
Posts: 388
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
View user's profile Send private message Send e-mail MSN Messenger
timber
PostPosted: Wed Jan 03, 2018 2:57 am    Post subject: Reply with quote

Grand Master

Joined: 25 Aug 2015
Posts: 1280

I've sort of lost the big picture somewhere along the line. Please can you restate the problem.
Back to top
View user's profile Send private message
zpat
PostPosted: Wed Jan 03, 2018 3:55 am    Post subject: Reply with quote

Jedi Council

Joined: 19 May 2001
Posts: 5849
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
View user's profile Send private message
Display posts from previous:   
Post new topic  Reply to topic Goto page 1, 2  Next Page 1 of 2

MQSeries.net Forum Index » WebSphere Message Broker (ACE) Support » JAVA BLOB to Oracle Clob
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
 
 


Theme by Dustin Baccetti
Powered by phpBB © 2001, 2002 phpBB Group

Copyright © MQSeries.net. All rights reserved.