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
  • 2 replies
  • Latest Post - ‏2014-10-13T14:00:24Z by Damery
Damery
Damery
71 Posts

Pinned topic IS THIS XML CCSID ISSUE?

‏2014-10-07T21:09:29Z | ccsid clob xmlelement xmlforest xmlserialize

I am passing in 3 fields 2 CHAR(10) and 1 DATE and I am trying to create an XML object. I use the parm fields as host variables in my SQL script 

           SELECT XMLSERIALIZE(
             XMLELEMENT(
               NAME "MY_XML",
                 XMLFOREST(:inputFLD1 AS "FIELD_ONE"
                         , :inputDate AS "DATE"
                         , :inputFLD2 AS "FIELD_TWO")
             ) AS CLOB(1K) CCSID 037 INCLUDING XMLDECLARATION
           )
           FROM SYSIBM.SYSDUMMY1;

DECLARE CURSOR 

FETCH CURSOR into host variable

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

the date works fine but the 2 CHAR(10) fields are all garbled.

any ideas?

Update: Here are my inputs and outputs

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>                                           
Updated on 2014-10-08T12:14:31Z at 2014-10-08T12:14:31Z by Damery
  • NickLawrence
    NickLawrence
    69 Posts
    ACCEPTED ANSWER

    Re: IS THIS XML CCSID ISSUE?

    ‏2014-10-08T21:16:39Z  

    This is basically the same question as the one you posted in the other thread: https://www.ibm.com/developerworks/community/forums/html/topic?id=cd591dae-6ebc-4d14-940e-b32b56646b7f#81918b8e-a612-4878-bcef-5e30583a2456

    I checked with the SQL pre-compiler team.

    In 7.1 the CCSID on your RPG standalone declare is not enough to tell SQL that the data is CCSID 37. (The rules for determining which CCSID is the default are talked about in the infocenter - you probably are defaulting to 65535) This was enhanced/corrected in 7.2.

    The SQL way to avoid this problem in both releases is to code an EXEC SQL DECLARE :outputXML VARIABLE CCSID 37

    If you publish character data that should not be translated (a.k.a. binary) in an XML document, DB2 will encode it in base64 (by default).

  • NickLawrence
    NickLawrence
    69 Posts

    Re: IS THIS XML CCSID ISSUE?

    ‏2014-10-08T21:16:39Z  

    This is basically the same question as the one you posted in the other thread: https://www.ibm.com/developerworks/community/forums/html/topic?id=cd591dae-6ebc-4d14-940e-b32b56646b7f#81918b8e-a612-4878-bcef-5e30583a2456

    I checked with the SQL pre-compiler team.

    In 7.1 the CCSID on your RPG standalone declare is not enough to tell SQL that the data is CCSID 37. (The rules for determining which CCSID is the default are talked about in the infocenter - you probably are defaulting to 65535) This was enhanced/corrected in 7.2.

    The SQL way to avoid this problem in both releases is to code an EXEC SQL DECLARE :outputXML VARIABLE CCSID 37

    If you publish character data that should not be translated (a.k.a. binary) in an XML document, DB2 will encode it in base64 (by default).

  • Damery
    Damery
    71 Posts

    Re: IS THIS XML CCSID ISSUE?

    ‏2014-10-13T14:00:24Z  

    This is basically the same question as the one you posted in the other thread: https://www.ibm.com/developerworks/community/forums/html/topic?id=cd591dae-6ebc-4d14-940e-b32b56646b7f#81918b8e-a612-4878-bcef-5e30583a2456

    I checked with the SQL pre-compiler team.

    In 7.1 the CCSID on your RPG standalone declare is not enough to tell SQL that the data is CCSID 37. (The rules for determining which CCSID is the default are talked about in the infocenter - you probably are defaulting to 65535) This was enhanced/corrected in 7.2.

    The SQL way to avoid this problem in both releases is to code an EXEC SQL DECLARE :outputXML VARIABLE CCSID 37

    If you publish character data that should not be translated (a.k.a. binary) in an XML document, DB2 will encode it in base64 (by default).

    Correct, my solution for 7.1 was to use an SQLTYPE host variable and replaced using my RPG type host variables with the SQL type in my SQL and that solved this problem so far.

    We will see when I start mixing in external source XML.

    I hope we upgrade to 7.2 too