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
  • 18 replies
  • Latest Post - ‏2015-08-06T17:38:36Z by canutri
canutri
canutri
11 Posts

Pinned topic SQLSTATE 57017 in RPG using SQL UDF

‏2015-04-09T15:36:43Z |

Hi,

I have an SQL UDF that is working when used in Run SQL Scripts, but when used in an RPG program with embedded SQL it fails with an SQLSTATE = 57017 and SQLCODE = -332.  The meaning for 57017 is "Character conversion is not defined". 

I found this post which does resolve the error, but I'd rather not change the RPG program's job CCSID.

Has anyone else encountered and tackled this issue?

Thank you,

Daron

  • NickLawrence
    NickLawrence
    69 Posts
    ACCEPTED ANSWER

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-18T23:38:59Z  
    • canutri
    • ‏2015-05-14T10:51:03Z

    Having changed the UDTF to HTTPPOSTBLOB, casting literals to CCSID 1208, parsed the input document as XML and encoding for UTF-8, I still am receiving an error when calling from my RPG program.  I've reduced the UDTF to eliminate function parameters and only return one column in the table result.  In the soap request, I've hard-coded the parameters for the web service to minimize what could be the cause of the CCSID conversion error.  I also added explicit CCSID 37 where applicable.

    At this point, I don't understand why the error is resulting with:  Character conversion between CCSID 65535 and CCSID 1200 not valid.

    Here is the UDTF and soap request UDF as I currently have them:

    CREATE OR REPLACE FUNCTION DlwDev.MaxPartDetail()
      RETURNS TABLE (StandardLabel VARCHAR(32) CCSID 37)
      LANGUAGE SQL
      SPECIFIC DlwDev."MaxPartDetail" 
      READS SQL DATA
      DETERMINISTIC
    
    RETURN 
                    
    SELECT
            "StandardLabel"
    FROM
            XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/'  AS "soapenv",
                                   'http://schemas.xmlsoap.org/soap/encoding/'                      AS "soapenc",
                                   'http://www.w3.org/2001/XMLSchema'          AS "xsd",
                                   'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                                   'http://services.max.madweb.madico.com'     AS "p282"
                                   ),
                     'soapenv:Envelope/soapenv:Body/p282:PartDetailResponse/return/PartDetailArray'
                    PASSING 
                    XMLPARSE(
                            DOCUMENT 
                            --
                            SYSTOOLS.HTTPPOSTBLOB(
                                    --- URL ---
                                    CAST(CAST('http://Dev400:9081/MaxServ/services/Parts' AS CHAR(1024) CCSID 1208) AS VARCHAR(2048)),
                                    --- Header ---
                                    CAST(CAST('<httpHeader>
                                                             <header name="SOAPAction" value="http://Dev400:9081/MaxServ/services/Parts/PartDetail"/>
                                                             <header name="Content-Type" value="application/xml"/>
                                                       </httpHeader>' AS CHAR(4092) CCSID 1208) AS CLOB(10K)),
                                    --- Message ---
                                    XMLSERIALIZE(MaxPartSoapReq() AS BLOB(2G) INCLUDING XMLDECLARATION)
                            )
                            --
                    )
                    COLUMNS
                    "seqno" FOR ORDINALITY,
                    "StandardLabel" VARCHAR(32) CCSID 37 PATH 'StandardLabel'
            ) AS RESULT
      ;
    
    CREATE OR REPLACE FUNCTION DlwDev.MaxPartSoapReq()
      RETURNS XML
      LANGUAGE SQL
      RETURN
        XMLDOCUMENT(
    
        ---- Soap Envelope ----
        XMLELEMENT(NAME "soapenv:Envelope",
         XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                       'http://services.bpcs.madweb.madico.com'    AS "q0",
                           'http://www.w3.org/2001/XMLSchema'           AS "xsd",
                           'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"
         ),
         ---- Soap Body ----
         XMLELEMENT(NAME "soapenv:Body",
          XMLELEMENT(NAME "q0:PartDetail",
           XMLFOREST(CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) AS "q0:partNo",
                             CAST('eq' AS VARCHAR(32) CCSID 1208)                 AS "q0:queryString"
               )  -- XMLFOREST
          ) -- q0:PartDetail
         ) -- soapenv:Body
        ) -- soapenv:Envelope
        ) -- XMLDOCUMENT
      ;
    

     

    You might also want to check that the CCSID of the RPG source code is not 65535. That is another way that a literal might be tagged with a 65535 CCSID.

  • B.Hauser
    B.Hauser
    320 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-04-09T16:35:45Z  

    Could you please post the parameter definition of your SQL UDF?

    And the parameter definition of your host variables and the UDF call in your RPG program.

    Birgitta

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-04-09T22:48:26Z  

    Sometimes this results from a host variable having a ccsid of 65535.

    Have you tried

    DECLARE :hostvar VARIABLE CCSID 37

    for your character parameters of the UDF? (Use whatever CCSID is correct for you application)

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-04-10T02:57:16Z  

    Sometimes this results from a host variable having a ccsid of 65535.

    Have you tried

    DECLARE :hostvar VARIABLE CCSID 37

    for your character parameters of the UDF? (Use whatever CCSID is correct for you application)

    Hi Birgitta and Nick,

    The UDF return parameter definition is:

    CREATE OR REPLACE FUNCTION IsValidLabelType (
          LabelType VARCHAR(1),
          ItemNumber VARCHAR(15))
      RETURNS VARCHAR(1)
    

    The host variable definition and embedded sql invocation is:

         D isValid         S              1    Varying
         D lblType         S              1    Varying
         D itemNum         S             15    Varying
    
    
             EXEC SQL
               DECLARE :labelType, :itemNo, :isValid VARIABLE CCSID 37;
             EXEC SQL
               SET :isValid = IsValidLabelType(:labelType, :itemNo);
    

    I've included Nick's suggestion to assign the CCSID for the host variables, but that didn't work as I have it above.

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-01T14:43:03Z  
    • canutri
    • ‏2015-04-10T02:57:16Z

    Hi Birgitta and Nick,

    The UDF return parameter definition is:

    <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr">CREATE OR REPLACE FUNCTION IsValidLabelType ( LabelType VARCHAR(1), ItemNumber VARCHAR(15)) RETURNS VARCHAR(1) </pre>

    The host variable definition and embedded sql invocation is:

    <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr"> D isValid S 1 Varying D lblType S 1 Varying D itemNum S 15 Varying EXEC SQL DECLARE :labelType, :itemNo, :isValid VARIABLE CCSID 37; EXEC SQL SET :isValid = IsValidLabelType(:labelType, :itemNo); </pre>

    I've included Nick's suggestion to assign the CCSID for the host variables, but that didn't work as I have it above.

    Hi Birgitta and Nick,

    I've been away on vacation and this is still a problem.

    Do you have any more insight to what my issue may be?  Is there anything more I could provide to enable you to assist?

    Thank you,

    Daron

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-04T16:03:04Z  
    • canutri
    • ‏2015-05-01T14:43:03Z

    Hi Birgitta and Nick,

    I've been away on vacation and this is still a problem.

    Do you have any more insight to what my issue may be?  Is there anything more I could provide to enable you to assist?

    Thank you,

    Daron

    Have you considered explicitly indicating the CCSID for the returned VARCHAR on the function?

    In some cases, DB2 assumes the data is in the job CCSID, which might not be the case in your example.

    Have a look at the RETURNS clause on the CREATE FUNCTION (external) syntax: http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcfsce.htm?lang=en

     

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-05T12:30:48Z  

    Have you considered explicitly indicating the CCSID for the returned VARCHAR on the function?

    In some cases, DB2 assumes the data is in the job CCSID, which might not be the case in your example.

    Have a look at the RETURNS clause on the CREATE FUNCTION (external) syntax: http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzcfsce.htm?lang=en

     

    I think I've isolated the source of the problem to have started when I introduced ( some 3 UDF's deep in my process) HTTPPOSTCLOB used to call a web service.  I had no success trying to apply CCSID value on any of the RETURN or input values (see attached UDF).  While searching on the CCSID issue, I came across a link suggesting that having a job CCSID of 65535 is a holdover from prior IBM i OS and that it was recommended to change the system/job CCSID to one that is appropriate to the application environment.

    Unless something enlightening turns up, I may consider changing the User's profile to CCSID 37.  The application here only has 2 primary users and about 4 secondary users.

    Attachments

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-05T21:48:12Z  
    • canutri
    • ‏2015-05-05T12:30:48Z

    I think I've isolated the source of the problem to have started when I introduced ( some 3 UDF's deep in my process) HTTPPOSTCLOB used to call a web service.  I had no success trying to apply CCSID value on any of the RETURN or input values (see attached UDF).  While searching on the CCSID issue, I came across a link suggesting that having a job CCSID of 65535 is a holdover from prior IBM i OS and that it was recommended to change the system/job CCSID to one that is appropriate to the application environment.

    Unless something enlightening turns up, I may consider changing the User's profile to CCSID 37.  The application here only has 2 primary users and about 4 secondary users.

    You have a few options here:

    The "CLOB" versions of the HTTP functions accept and return data in CCSID 1208. If you have a host variable that is not tagged with a CCSID, and the default CCSID ends up as 65535, a conversion cannot be performed.

     

    Using a BLOB avoids CCSID conversions and is often preferable when working with XML.

    If using RPG code and 7.1, you should look at this post, it has tripped up several developers in the past.

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

     

    If you are going to serialize XML to a CLOB, I would suggest using CCSID 1208. This is UTF-8 and should be accepted by a wider audience than CCSID 37.

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-08T17:21:09Z  

    You have a few options here:

    The "CLOB" versions of the HTTP functions accept and return data in CCSID 1208. If you have a host variable that is not tagged with a CCSID, and the default CCSID ends up as 65535, a conversion cannot be performed.

     

    Using a BLOB avoids CCSID conversions and is often preferable when working with XML.

    If using RPG code and 7.1, you should look at this post, it has tripped up several developers in the past.

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

     

    If you are going to serialize XML to a CLOB, I would suggest using CCSID 1208. This is UTF-8 and should be accepted by a wider audience than CCSID 37.

    Given your advice to using BLOB to avoid CCSID conversions, to make the necessary adjustments to the my ficnton I've gone back to my source reference - Accessing web services using IBM DB2 for i HTTP UDFs and UDTFs.  My appreciation to you and you team for the work provided in this resource.

    After changing my function - the original function using CLOB was attached in a previous post - to use the HTTPPOSTBLOB casting the function's RequestMsg parameter as BLOB, I'm now getting the following error when running the function separately in a Run SQL Scripts window.

    Java stored procedure or user-defined function SYSTOOLS.HTTPPOSTBLOB, specific name HTTPPOSTBLOBNONXML aborted with an exception "Server returned HTTP response code: 500 for URL: http://Dev400:9081/MaxServ/services/Parts"

    The modified function follows:

    CREATE OR REPLACE FUNCTION DlwDev.MaxPartDetail(
        partNo VARCHAR(25),
            queryOperand VARCHAR(16))
      RETURNS TABLE (PartNo VARCHAR(25),
                     PartDescription VARCHAR(64),
                     StandardLabel VARCHAR(32))
      LANGUAGE SQL
      SPECIFIC DlwDev."MaxPartDetail" 
      READS SQL DATA
      DETERMINISTIC
      RETURN 
                            
    SELECT
            "PartNo",
            "PartDescription",
            "StandardLabel"
    FROM
            XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                                   'http://schemas.xmlsoap.org/soap/encoding/' AS "soapenc",
                                   'http://www.w3.org/2001/XMLSchema' AS "xsd",
                                   'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                                   'http://services.max.madweb.madico.com' AS "p282"
                                   ),
                     'soapenv:Envelope/soapenv:Body/p282:PartDetailResponse/return/PartDetailArray'
                    PASSING 
                    XMLPARSE(
                            DOCUMENT 
                            --
                            SYSTOOLS.HTTPPOSTBLOB(
                                    --- URL ---
                                    'http://Dev400:9081/MaxServ/services/Parts',
                                    --- Header ---
                                    '<httpHeader>
                                       <header name="SOAPAction" value="http://Dev400:9081/MaxServ/services/Parts/PartDetail"/>
                                       <header name="Content-Type" value="application/xml"/>
                                     </httpHeader>',
                                    --- Message ---
                                    CAST('<soapenv:Envelope
                                       xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/"
                                       xmlns:q0="http://services.max.madweb.madico.com"
                                       xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                                       xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                                       <soapenv:Body>
                                         <q0:PartDetail>'
                                           || '<partNo>' || TRIM(partNo) || '</partNo>'
                                           || '<queryString>eq</queryString>
                                         </q0:PartDetail>
                                       </soapenv:Body>
                                     </soapenv:Envelope>' AS BLOB(2G))
                            )
                            --
                    )
                    COLUMNS
                    "seqno" FOR ORDINALITY,
                    "PartNo" VARCHAR(15) PATH 'PartNo',
                    "PartDescription" VARCHAR(30) PATH 'PartDescription',
                    "StandardLabel" VARCHAR(32) PATH 'StandardLabel'
            ) AS RESULT
      ;
    

    Can you see what I'm doing wrong in my attempts to use the BLOB method?

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-08T17:35:09Z  
    • canutri
    • ‏2015-05-08T17:21:09Z

    Given your advice to using BLOB to avoid CCSID conversions, to make the necessary adjustments to the my ficnton I've gone back to my source reference - Accessing web services using IBM DB2 for i HTTP UDFs and UDTFs.  My appreciation to you and you team for the work provided in this resource.

    After changing my function - the original function using CLOB was attached in a previous post - to use the HTTPPOSTBLOB casting the function's RequestMsg parameter as BLOB, I'm now getting the following error when running the function separately in a Run SQL Scripts window.

    Java stored procedure or user-defined function SYSTOOLS.HTTPPOSTBLOB, specific name HTTPPOSTBLOBNONXML aborted with an exception "Server returned HTTP response code: 500 for URL: http://Dev400:9081/MaxServ/services/Parts"

    The modified function follows:

    <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr">CREATE OR REPLACE FUNCTION DlwDev.MaxPartDetail( partNo VARCHAR(25), queryOperand VARCHAR(16)) RETURNS TABLE (PartNo VARCHAR(25), PartDescription VARCHAR(64), StandardLabel VARCHAR(32)) LANGUAGE SQL SPECIFIC DlwDev."MaxPartDetail" READS SQL DATA DETERMINISTIC RETURN SELECT "PartNo", "PartDescription", "StandardLabel" FROM XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv", 'http://schemas.xmlsoap.org/soap/encoding/' AS "soapenc", 'http://www.w3.org/2001/XMLSchema' AS "xsd", 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi", 'http://services.max.madweb.madico.com' AS "p282" ), 'soapenv:Envelope/soapenv:Body/p282:PartDetailResponse/return/PartDetailArray' PASSING XMLPARSE( DOCUMENT -- SYSTOOLS.HTTPPOSTBLOB( --- URL --- 'http://Dev400:9081/MaxServ/services/Parts', --- Header --- '<httpHeader> <header name="SOAPAction" value="http://Dev400:9081/MaxServ/services/Parts/PartDetail"/> <header name="Content-Type" value="application/xml"/> </httpHeader>', --- Message --- CAST('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:q0="http://services.max.madweb.madico.com" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <soapenv:Body> <q0:PartDetail>' || '<partNo>' || TRIM(partNo) || '</partNo>' || '<queryString>eq</queryString> </q0:PartDetail> </soapenv:Body> </soapenv:Envelope>' AS BLOB(2G)) ) -- ) COLUMNS "seqno" FOR ORDINALITY, "PartNo" VARCHAR(15) PATH 'PartNo', "PartDescription" VARCHAR(30) PATH 'PartDescription', "StandardLabel" VARCHAR(32) PATH 'StandardLabel' ) AS RESULT ; </pre>

    Can you see what I'm doing wrong in my attempts to use the BLOB method?

    You are passing character data as (serialized) XML and not including an encoding declaration.

    I would suggest you cast the literal to CCSID 1208 (UTF8), add an encoding declaration for UTF-8, and then cast it to a BLOB.

    (Another option is to parse the input document as XML and then serialize it to BLOB, with that approach, the parse will fail if the xml document is not well formed - rather than sending bad data to the server)

    A return code of 500 means an unexpected error happened on the server side (basically a function check). But frequently this type of error occurs when the input message is bad in some way.

    Updated on 2015-05-08T18:33:34Z at 2015-05-08T18:33:34Z by NickLawrence
  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-08T17:52:46Z  

    You are passing character data as (serialized) XML and not including an encoding declaration.

    I would suggest you cast the literal to CCSID 1208 (UTF8), add an encoding declaration for UTF-8, and then cast it to a BLOB.

    (Another option is to parse the input document as XML and then serialize it to BLOB, with that approach, the parse will fail if the xml document is not well formed - rather than sending bad data to the server)

    A return code of 500 means an unexpected error happened on the server side (basically a function check). But frequently this type of error occurs when the input message is bad in some way.

    I'd like to fully understand/clarify your recommendations to:

    1)  Cast the literal to CCSID 1208 (UTF8), add an encoding declaration for UTF-8, and then cast it to a BLOB

    Would that be represented by this:

    CAST(CAST('<soapenv:Envelope ...> ... </soapenv:Envelope>' AS CHAR(4096) CCSID 1208) AS BLOB(2G))
    

    I'm not certain what needs to be done to "add an encoding declaration for UTF-8".

    2)  Parse the input document as XML and then serialize it to BLOB

    Would that be the same steps involved as depicted in Listing 16 for the build_soap_req() function and consumed Listing 17 with XMLSERIALIZE?

    I believe I see the value of ensuring the XML is well formed.

    Updated on 2015-05-08T17:54:56Z at 2015-05-08T17:54:56Z by canutri
  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-08T18:39:43Z  
    • canutri
    • ‏2015-05-08T17:52:46Z

    I'd like to fully understand/clarify your recommendations to:

    1)  Cast the literal to CCSID 1208 (UTF8), add an encoding declaration for UTF-8, and then cast it to a BLOB

    Would that be represented by this:

    <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr">CAST(CAST('<soapenv:Envelope ...> ... </soapenv:Envelope>' AS CHAR(4096) CCSID 1208) AS BLOB(2G)) </pre>

    I'm not certain what needs to be done to "add an encoding declaration for UTF-8".

    2)  Parse the input document as XML and then serialize it to BLOB

    Would that be the same steps involved as depicted in Listing 16 for the build_soap_req() function and consumed Listing 17 with XMLSERIALIZE?

    I believe I see the value of ensuring the XML is well formed.

    The encoding declaration  tells a parser which ccsid the data is in.

    
    <?xml version="1.0" encoding="UTF-8" ?>
    

     

    When you send your  BLOB data, should make sure the data really is in the specified encoding, and the encoding is declared in the document. UTF-8 is usually a choice that will be supported by the receiver.

     

    The XMLSERIALIZE function has an option to add the encoding declaration for you.

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-14T10:51:03Z  

    The encoding declaration  tells a parser which ccsid the data is in.

    <pre class="pre codeblock" dir="ltr"> <?xml version="1.0" encoding="UTF-8" ?> </pre>

     

    When you send your  BLOB data, should make sure the data really is in the specified encoding, and the encoding is declared in the document. UTF-8 is usually a choice that will be supported by the receiver.

     

    The XMLSERIALIZE function has an option to add the encoding declaration for you.

    Having changed the UDTF to HTTPPOSTBLOB, casting literals to CCSID 1208, parsed the input document as XML and encoding for UTF-8, I still am receiving an error when calling from my RPG program.  I've reduced the UDTF to eliminate function parameters and only return one column in the table result.  In the soap request, I've hard-coded the parameters for the web service to minimize what could be the cause of the CCSID conversion error.  I also added explicit CCSID 37 where applicable.

    At this point, I don't understand why the error is resulting with:  Character conversion between CCSID 65535 and CCSID 1200 not valid.

    Here is the UDTF and soap request UDF as I currently have them:

    CREATE OR REPLACE FUNCTION DlwDev.MaxPartDetail()  RETURNS TABLE (StandardLabel VARCHAR(32) CCSID 37)
      LANGUAGE SQL
      SPECIFIC DlwDev."MaxPartDetail" 
      READS SQL DATA
      DETERMINISTIC
    
    RETURN 
                    
    SELECT
            "StandardLabel"
    FROM
            XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/'  AS "soapenv",
                                   'http://schemas.xmlsoap.org/soap/encoding/'                      AS "soapenc",
                                   'http://www.w3.org/2001/XMLSchema'          AS "xsd",
                                   'http://www.w3.org/2001/XMLSchema-instance' AS "xsi",
                                   'http://services.max.madweb.madico.com'     AS "p282"
                                   ),
                     'soapenv:Envelope/soapenv:Body/p282:PartDetailResponse/return/PartDetailArray'
                    PASSING 
                    XMLPARSE(
                            DOCUMENT 
                            --
                            SYSTOOLS.HTTPPOSTBLOB(
                                    --- URL ---
                                    CAST(CAST('http://Dev400:9081/MaxServ/services/Parts' AS CHAR(1024) CCSID 1208) AS VARCHAR(2048)),
                                    --- Header ---
                                    CAST(CAST('<httpHeader>
                                                             <header name="SOAPAction" value="http://Dev400:9081/MaxServ/services/Parts/PartDetail"/>
                                                             <header name="Content-Type" value="application/xml"/>
                                                       </httpHeader>' AS CHAR(4092) CCSID 1208) AS CLOB(10K)),
                                    --- Message ---
                                    XMLSERIALIZE(MaxPartSoapReq() AS BLOB(2G) INCLUDING XMLDECLARATION)
                            )
                            --
                    )
                    COLUMNS
                    "seqno" FOR ORDINALITY,
                    "StandardLabel" VARCHAR(32) CCSID 37 PATH 'StandardLabel'
            ) AS RESULT
      ;
    
    CREATE OR REPLACE FUNCTION DlwDev.MaxPartSoapReq()  RETURNS XML
      LANGUAGE SQL
      RETURN
        XMLDOCUMENT(
    
        ---- Soap Envelope ----
        XMLELEMENT(NAME "soapenv:Envelope",
         XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                       'http://services.bpcs.madweb.madico.com'    AS "q0",
                           'http://www.w3.org/2001/XMLSchema'           AS "xsd",
                           'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"
         ),
         ---- Soap Body ----
         XMLELEMENT(NAME "soapenv:Body",
          XMLELEMENT(NAME "q0:PartDetail",
           XMLFOREST(CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) AS "q0:partNo",
                             CAST('eq' AS VARCHAR(32) CCSID 1208)                 AS "q0:queryString"
               )  -- XMLFOREST
          ) -- q0:PartDetail
         ) -- soapenv:Body
        ) -- soapenv:Envelope
        ) -- XMLDOCUMENT
      ;
    

     

    Updated on 2015-05-14T11:02:39Z at 2015-05-14T11:02:39Z by canutri
  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-18T23:38:59Z  
    • canutri
    • ‏2015-05-14T10:51:03Z

    Having changed the UDTF to HTTPPOSTBLOB, casting literals to CCSID 1208, parsed the input document as XML and encoding for UTF-8, I still am receiving an error when calling from my RPG program.  I've reduced the UDTF to eliminate function parameters and only return one column in the table result.  In the soap request, I've hard-coded the parameters for the web service to minimize what could be the cause of the CCSID conversion error.  I also added explicit CCSID 37 where applicable.

    At this point, I don't understand why the error is resulting with:  Character conversion between CCSID 65535 and CCSID 1200 not valid.

    Here is the UDTF and soap request UDF as I currently have them:

    <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr">CREATE OR REPLACE FUNCTION DlwDev.MaxPartDetail() RETURNS TABLE (StandardLabel VARCHAR(32) CCSID 37) LANGUAGE SQL SPECIFIC DlwDev."MaxPartDetail" READS SQL DATA DETERMINISTIC RETURN SELECT "StandardLabel" FROM XMLTABLE(XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv", 'http://schemas.xmlsoap.org/soap/encoding/' AS "soapenc", 'http://www.w3.org/2001/XMLSchema' AS "xsd", 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi", 'http://services.max.madweb.madico.com' AS "p282" ), 'soapenv:Envelope/soapenv:Body/p282:PartDetailResponse/return/PartDetailArray' PASSING XMLPARSE( DOCUMENT -- SYSTOOLS.HTTPPOSTBLOB( --- URL --- CAST(CAST('http://Dev400:9081/MaxServ/services/Parts' AS CHAR(1024) CCSID 1208) AS VARCHAR(2048)), --- Header --- CAST(CAST('<httpHeader> <header name="SOAPAction" value="http://Dev400:9081/MaxServ/services/Parts/PartDetail"/> <header name="Content-Type" value="application/xml"/> </httpHeader>' AS CHAR(4092) CCSID 1208) AS CLOB(10K)), --- Message --- XMLSERIALIZE(MaxPartSoapReq() AS BLOB(2G) INCLUDING XMLDECLARATION) ) -- ) COLUMNS "seqno" FOR ORDINALITY, "StandardLabel" VARCHAR(32) CCSID 37 PATH 'StandardLabel' ) AS RESULT ; </pre> <pre class="html dw" data-editor-lang="js" data-pbcklang="html" dir="ltr">CREATE OR REPLACE FUNCTION DlwDev.MaxPartSoapReq() RETURNS XML LANGUAGE SQL RETURN XMLDOCUMENT( ---- Soap Envelope ---- XMLELEMENT(NAME "soapenv:Envelope", XMLNAMESPACES('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv", 'http://services.bpcs.madweb.madico.com' AS "q0", 'http://www.w3.org/2001/XMLSchema' AS "xsd", 'http://www.w3.org/2001/XMLSchema-instance' AS "xsi" ), ---- Soap Body ---- XMLELEMENT(NAME "soapenv:Body", XMLELEMENT(NAME "q0:PartDetail", XMLFOREST(CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) AS "q0:partNo", CAST('eq' AS VARCHAR(32) CCSID 1208) AS "q0:queryString" ) -- XMLFOREST ) -- q0:PartDetail ) -- soapenv:Body ) -- soapenv:Envelope ) -- XMLDOCUMENT ; </pre>

     

    You might also want to check that the CCSID of the RPG source code is not 65535. That is another way that a literal might be tagged with a 65535 CCSID.

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-22T15:54:13Z  

    You might also want to check that the CCSID of the RPG source code is not 65535. That is another way that a literal might be tagged with a 65535 CCSID.

    The source file is CCSID 37 as viewed with DSPPF.  The*MODULE is CCSID 37 but the *SRVPGM ends up as CCSID 65535 when viewed with DSPMOD and DSPSRVPGM respectively.  I've ensured my profile and job are CCSID 37 but still ends up with a *SRVPGM of 37.  I might have to take this issue over to the RPG forum.

    What literals are you specifically referring to?  The literals in the SYSTOOLS.HTTPPOSTBLOB, or elsewhere?

  • NickLawrence
    NickLawrence
    69 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-27T03:39:20Z  
    • canutri
    • ‏2015-05-22T15:54:13Z

    The source file is CCSID 37 as viewed with DSPPF.  The*MODULE is CCSID 37 but the *SRVPGM ends up as CCSID 65535 when viewed with DSPMOD and DSPSRVPGM respectively.  I've ensured my profile and job are CCSID 37 but still ends up with a *SRVPGM of 37.  I might have to take this issue over to the RPG forum.

    What literals are you specifically referring to?  The literals in the SYSTOOLS.HTTPPOSTBLOB, or elsewhere?

    A string literal in a statement such as CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) is going to assume '3905921-60100L' is in the CCSID of the file (prior to the cast).

     

    You might have to narrow down the problem to an implicit or explicit cast that fails, and then go from there.

  • CRPence@vnet.ibm.com
    64 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-05-28T17:25:42Z  

    A string literal in a statement such as CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) is going to assume '3905921-60100L' is in the CCSID of the file (prior to the cast).

     

    You might have to narrow down the problem to an implicit or explicit cast that fails, and then go from there.

    I think you mean to suggest the literal [string constant] will have the CCSID of the statement rather than of any file(s). That typically would be the CCSID of the source from which the embedded statement was coded, or the CCSID of the job for a dynamic statement.?

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-07-28T14:58:26Z  

    A string literal in a statement such as CAST('3905921-60100L' AS VARCHAR(32) CCSID 1208) is going to assume '3905921-60100L' is in the CCSID of the file (prior to the cast).

     

    You might have to narrow down the problem to an implicit or explicit cast that fails, and then go from there.

    Hi Nick,

    I wanted to follow up this with an update.  In an earlier post, I had said that I would consider changing the user's profile to be CCSID 37, but I ended up modifying the CL program to temporarily change the job CCSID to 37 then back to the *SYSVAL at the end of the job execution.  I didn't like this approach because I new that I would encounter the same problems when I used the function in another application.  Sure enough that did happen and some time was spent trying to figure why the function wasn't returning the desired data.

    In the mean time, I had discovered that my connection in RDi did not specify a CCSID value and was using the *SYSVAL.  Changing the connection CCSID to 37 and re-compiling the program solved the riddle.

    Thank you for the help as you've provided much insight for the use of HTTP function capabilities.

    Daron

  • canutri
    canutri
    11 Posts

    Re: SQLSTATE 57017 in RPG using SQL UDF

    ‏2015-08-06T17:38:36Z  
    • canutri
    • ‏2015-07-28T14:58:26Z

    Hi Nick,

    I wanted to follow up this with an update.  In an earlier post, I had said that I would consider changing the user's profile to be CCSID 37, but I ended up modifying the CL program to temporarily change the job CCSID to 37 then back to the *SYSVAL at the end of the job execution.  I didn't like this approach because I new that I would encounter the same problems when I used the function in another application.  Sure enough that did happen and some time was spent trying to figure why the function wasn't returning the desired data.

    In the mean time, I had discovered that my connection in RDi did not specify a CCSID value and was using the *SYSVAL.  Changing the connection CCSID to 37 and re-compiling the program solved the riddle.

    Thank you for the help as you've provided much insight for the use of HTTP function capabilities.

    Daron

    It appears that changing the RDi connection CCSID value did not have any effect.  The problem continues and not sure what gave us this false positive result.  :(