IC SunsetThe developerWorks Connections platform will be sunset on December 31, 2019. On January 1, 2020, this forum will no longer be available. More details available on our FAQ.
Topic
  • 6 replies
  • Latest Post - ‏2014-10-08T15:37:49Z by Damery
Damery
Damery
84 Posts

Pinned topic Is there a modern MQ XML SQLRPGLE example

‏2013-09-23T18:43:27Z | mq rpgle sql sqlrpgle xml

I am looking for any up to date example/articles (os version = 7.1), for taking an XML document from MQ message Queue and handling that data in RPGLE or SQLRPGLE to populate Tables.

I keep running into 2007 articles on XML and RPG, I imagine we have made some advances.

I am wondering if I need to use pointers and user space because of XML size issues with XML-INTO? (65535 byte max?) read that somewhere

I read something about an XMLTABLE option with using SQL wondered if that might help parsing XML into Table fields?

High level is getting XML message from MQ and inserting or updating tables

Mid level is using a service program off of MQ to control commitment of XML message so if Table is not updated completly rollback to MQ and try again.

  • NickLawrence
    NickLawrence
    18 Posts
    ACCEPTED ANSWER

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T15:21:16Z  
    • Damery
    • ‏2014-10-08T14:52:03Z

    I am using RPG Host variables and that is probably some of the issue

    dcl-s inputFld1 Char(10) Inz('LW346935AL');

    dcl-s outputXML SQLTYPE(CLOB:1000) ccsid(037);//IBM037

    then I use SQL to create my XML CLOB

             DECLARE XMLCursor CURSOR FOR
               SELECT XMLSERIALIZE(
                 XMLELEMENT(
                 NAME "MY_XML",
                   XMLFOREST(:inputFLD1 AS "FIELD_ONE"
                           , :inputDate AS "DATE"
                           , :inputFLD2 AS "FIELD_TWO")
                   ) AS CLOB(1K) CCSID 1208 EXCLUDING XMLDECLARATION
                 ) AS "MY_XML_RESULT"
               FROM SYSIBM.SYSDUMMY1;

    FETCH XMLCursor INTO :outputXML;

    and the data is scrambled.

    I thought at 1 point it looked right and I just had to figure out how to get the data from the CLOB, after that only the date remained correct.

    The data is not scrambled, it's encoded.

    I think you have (accidentally) done something similar to this:

    CREATE VARIABLE bindata CHAR(10) CCSID 65535;
    SET bindata = 'LW346935AL';

    VALUES
     XMLDOCUMENT(
       XMLELEMENT(NAME "root", bindata
         OPTION XMLBINARY BASE64
       )
    );

     

    The resulting document looks like this:

    <root>0+bz9Pb58/XB0w==</root>

     

    The problem is that bindata is (for practical purposes) binary data. Binary data gets encoded in an XML document using base64 (by default). When the document is processed, the application converts the base64 character data back into binary data.

     

    Make sure your host variables have the correct CCSIDS.

    There is an "EXEC SQL DECLARE x VARIABLE CCSID xxx"  statement in embedded SQL that can help with this.

     

    See also: https://www.ibm.com/developerworks/community/forums/html/topic?id=0083ff67-ac40-4348-baea-aaa546425e79&ps=25

    Updated on 2015-05-05T21:57:26Z at 2015-05-05T21:57:26Z by NickLawrence
  • NickLawrence
    NickLawrence
    18 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2013-09-24T15:47:26Z  

    SQL support for XML has been significantly enhanced in DB2 for i 7.1

    There is an SQL/XML programmers guide in the infocenter.http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzasp/rzaspprintthis.htm

    I am not an expert on MQ, but I know that there are several built in functions in DB2 for i, and these are described in the SQL reference http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf

    In addition:

    There is a developerWorks article that has examples for using XMLTABLE and RPG:

    http://www.ibm.com/developerworks/ibmi/library/i-using-rpg/index.html#!

    There is also a whitepaper that includes examples of how to use XML with RPG:
    https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler?contentId=K$63TzTFkZwiPCA$cnt&roadMapId=IbOtoNReUYN4MDADrdm&roadMapName=Education+resources+for+IBM+i+systems&locale=en_US#!

  • Damery
    Damery
    84 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T12:23:11Z  

    SQL support for XML has been significantly enhanced in DB2 for i 7.1

    There is an SQL/XML programmers guide in the infocenter.http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzasp/rzaspprintthis.htm

    I am not an expert on MQ, but I know that there are several built in functions in DB2 for i, and these are described in the SQL reference http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/db2/rbafz.pdf

    In addition:

    There is a developerWorks article that has examples for using XMLTABLE and RPG:

    http://www.ibm.com/developerworks/ibmi/library/i-using-rpg/index.html#!

    There is also a whitepaper that includes examples of how to use XML with RPG:
    https://www-304.ibm.com/partnerworld/wps/servlet/ContentHandler?contentId=K$63TzTFkZwiPCA$cnt&roadMapId=IbOtoNReUYN4MDADrdm&roadMapName=Education+resources+for+IBM+i+systems&locale=en_US#!

    I am trying to use XMLELEMENT with XMLFOREST to build an XML document but my character host variables get scrambled when I run it. I try to create an XMLSERIALIZED CLOB CCSID 037 but when I run the SQL the result is all good except my character data is converted to something else....not sure how to manage that.

    Any Help?

    Example data in and data out

    INPUTFLD1 = 'LW346935AL'
    INPUTDATE = '2012-04-02'
    INPUTFLD2 = '          '
     
    <?xml version="1.0" encoding="IBM037"?>
    <MY_XML>
    <FIELD_ONE>0+bz9Pb58/XB0w==</FIELD_ONE>
    <DATE>2012-04-02</DATE>
    <FIELD_TWO>QEBAQEBAQEBAQA==</FIELD_TWO>
    </MY_XML>                                           
  • NickLawrence
    NickLawrence
    18 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T14:41:22Z  
    • Damery
    • ‏2014-10-08T12:23:11Z

    I am trying to use XMLELEMENT with XMLFOREST to build an XML document but my character host variables get scrambled when I run it. I try to create an XMLSERIALIZED CLOB CCSID 037 but when I run the SQL the result is all good except my character data is converted to something else....not sure how to manage that.

    Any Help?

    Example data in and data out

    INPUTFLD1 = 'LW346935AL'
    INPUTDATE = '2012-04-02'
    INPUTFLD2 = '          '
     
    <?xml version="1.0" encoding="IBM037"?>
    <MY_XML>
    <FIELD_ONE>0+bz9Pb58/XB0w==</FIELD_ONE>
    <DATE>2012-04-02</DATE>
    <FIELD_TWO>QEBAQEBAQEBAQA==</FIELD_TWO>
    </MY_XML>                                           

    I think you should look at the data type of the host variables (in SQL).

    The value for the text node in FIELD_ONE looks like base64 to me. In a serialized XML document (which must be character by definition), binary data is encoded in a character format. (Either hex or base64). Then the application converts the data from either base64 or hex to binary as part of parsing and understanding the XML document.

    If you publish binary data in an XML document, SQL does the conversion to base64 or hex for you.

    I think it is somewhat rare to encode binary data directly in an XML document, more common is to send some location information (URL) that informs the receiver where the binary data is. That approach avoids excessive encoding and decoding of binary values. Not all interfaces in SQL fully support binary data in 7.1 and 7.2, for example XMLTABLE does not support binary output columns.

  • Damery
    Damery
    84 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T14:52:03Z  

    I think you should look at the data type of the host variables (in SQL).

    The value for the text node in FIELD_ONE looks like base64 to me. In a serialized XML document (which must be character by definition), binary data is encoded in a character format. (Either hex or base64). Then the application converts the data from either base64 or hex to binary as part of parsing and understanding the XML document.

    If you publish binary data in an XML document, SQL does the conversion to base64 or hex for you.

    I think it is somewhat rare to encode binary data directly in an XML document, more common is to send some location information (URL) that informs the receiver where the binary data is. That approach avoids excessive encoding and decoding of binary values. Not all interfaces in SQL fully support binary data in 7.1 and 7.2, for example XMLTABLE does not support binary output columns.

    I am using RPG Host variables and that is probably some of the issue

    dcl-s inputFld1 Char(10) Inz('LW346935AL');

    dcl-s outputXML SQLTYPE(CLOB:1000) ccsid(037);//IBM037

    then I use SQL to create my XML CLOB

             DECLARE XMLCursor CURSOR FOR
               SELECT XMLSERIALIZE(
                 XMLELEMENT(
                 NAME "MY_XML",
                   XMLFOREST(:inputFLD1 AS "FIELD_ONE"
                           , :inputDate AS "DATE"
                           , :inputFLD2 AS "FIELD_TWO")
                   ) AS CLOB(1K) CCSID 1208 EXCLUDING XMLDECLARATION
                 ) AS "MY_XML_RESULT"
               FROM SYSIBM.SYSDUMMY1;

    FETCH XMLCursor INTO :outputXML;

    and the data is scrambled.

    I thought at 1 point it looked right and I just had to figure out how to get the data from the CLOB, after that only the date remained correct.

  • NickLawrence
    NickLawrence
    18 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T15:21:16Z  
    • Damery
    • ‏2014-10-08T14:52:03Z

    I am using RPG Host variables and that is probably some of the issue

    dcl-s inputFld1 Char(10) Inz('LW346935AL');

    dcl-s outputXML SQLTYPE(CLOB:1000) ccsid(037);//IBM037

    then I use SQL to create my XML CLOB

             DECLARE XMLCursor CURSOR FOR
               SELECT XMLSERIALIZE(
                 XMLELEMENT(
                 NAME "MY_XML",
                   XMLFOREST(:inputFLD1 AS "FIELD_ONE"
                           , :inputDate AS "DATE"
                           , :inputFLD2 AS "FIELD_TWO")
                   ) AS CLOB(1K) CCSID 1208 EXCLUDING XMLDECLARATION
                 ) AS "MY_XML_RESULT"
               FROM SYSIBM.SYSDUMMY1;

    FETCH XMLCursor INTO :outputXML;

    and the data is scrambled.

    I thought at 1 point it looked right and I just had to figure out how to get the data from the CLOB, after that only the date remained correct.

    The data is not scrambled, it's encoded.

    I think you have (accidentally) done something similar to this:

    CREATE VARIABLE bindata CHAR(10) CCSID 65535;
    SET bindata = 'LW346935AL';

    VALUES
     XMLDOCUMENT(
       XMLELEMENT(NAME "root", bindata
         OPTION XMLBINARY BASE64
       )
    );

     

    The resulting document looks like this:

    <root>0+bz9Pb58/XB0w==</root>

     

    The problem is that bindata is (for practical purposes) binary data. Binary data gets encoded in an XML document using base64 (by default). When the document is processed, the application converts the base64 character data back into binary data.

     

    Make sure your host variables have the correct CCSIDS.

    There is an "EXEC SQL DECLARE x VARIABLE CCSID xxx"  statement in embedded SQL that can help with this.

     

    See also: https://www.ibm.com/developerworks/community/forums/html/topic?id=0083ff67-ac40-4348-baea-aaa546425e79&ps=25

    Updated on 2015-05-05T21:57:26Z at 2015-05-05T21:57:26Z by NickLawrence
  • Damery
    Damery
    84 Posts

    Re: Is there a modern MQ XML SQLRPGLE example

    ‏2014-10-08T15:37:49Z  

    The data is not scrambled, it's encoded.

    I think you have (accidentally) done something similar to this:

    CREATE VARIABLE bindata CHAR(10) CCSID 65535;
    SET bindata = 'LW346935AL';

    VALUES
     XMLDOCUMENT(
       XMLELEMENT(NAME "root", bindata
         OPTION XMLBINARY BASE64
       )
    );

     

    The resulting document looks like this:

    <root>0+bz9Pb58/XB0w==</root>

     

    The problem is that bindata is (for practical purposes) binary data. Binary data gets encoded in an XML document using base64 (by default). When the document is processed, the application converts the base64 character data back into binary data.

     

    Make sure your host variables have the correct CCSIDS.

    There is an "EXEC SQL DECLARE x VARIABLE CCSID xxx"  statement in embedded SQL that can help with this.

     

    See also: https://www.ibm.com/developerworks/community/forums/html/topic?id=0083ff67-ac40-4348-baea-aaa546425e79&ps=25

    You were right it is encoding or as I call it scrambled.

    In my instance I was able to create SQL host variables

    dcl-s XMLinputFLD1 SQLTYPE(CLOB:10);

    then convert my RPGLE CHAR(10) data into CLOB

    XMLinputFLD1_Len = %Len(inputFLD1);
    XMLinputFLD1_Data = inputFLD1;     

    I then replaced my RPG host variable with an SQL type host variable and the data seems to hold

    ...

    XMLFOREST(:XMLinputFLD1 AS "FIELD_ONE"

    ...

    I had tried a couple casting statements and they didnt work so I tried this SQLTYPE host variable and it is working...for now.