IBM i

 View Only
Expand all | Collapse all

Problems with JSON_TABLE and CCSID in SQLRPGLE

  • 1.  Problems with JSON_TABLE and CCSID in SQLRPGLE

    Posted Fri April 29, 2022 09:06 AM
    Hi.

    I have a problem with one program, and it is related with the Source File CCSID.
    I need to generate a JSON field.
    In the original program, it was an INSERT from a common table (with diferent columns) into a column in a table, defined as CLOB to contain JSON generated with the source table columns.

    This is an example of the program, that shows the problem, and has the same problem.:

    **free
    
    ctl-opt dftactgrp(*no) option(*srcstmt:*NODEBUGIO);
    
    dcl-s jsonv varchar(256) ccsid(1145);
    dcl-s text varchar(34) ccsid(1145);
    
    TEXT='TEST, AND TEXT';
    exec sql set :jsonv=json_object('test_text' value :text);
    
    return;    ​

    Compiled with:

    CRTSQLRPGI OBJ(TESTJSON) SRCFILE(LET) SRCMBR(TESTJSON) OPTION(*EVENTF) REPLACE(*YES) DBGVIEW(*SOURCE) LANGID(ESP) CVTCCSID(*JOB)



    The problem is that when I compile this code coming from a source file with CCSID 284, it works fine.

    But when I compile it from a diferent source file, that has CCSID 65535, then the program fails. 


    These are the errors:

    Derived operands not valid for operator JSON_OBJECT. Reason code 12. 
    Character conversion between CCSID 65535 and CCSID 1208 not valid.   
      12 -- The CCSIDs (Coded Character Set Identifiers) of the operands cannot
    be made compatible.                                                        
    
    Message . . . . :   Character conversion between CCSID 65535 and CCSID 1208   
      not valid.                                                                  
    Cause . . . . . :   Character or graphic conversion has been attempted for    
      data that is not compatible. There is no conversion defined between CCSID   
      65535 and CCSID 1208.                                                       
        If one CCSID is 65535, the other CCSID is a graphic CCSID. Conversion is  
      not defined between 65535 and a graphic CCSID.                              
        If this is a CONNECT statement, conversion is not defined between the     
      default application requester SBCS CCSID and the application server SBCS    
      CCSID.  If the second CCSID is 0, the application server did not return its 
      default SBCS CCSID.  An application server other than DB2 for IBM i may not 
      support a CCSID of 65535.                                                   
    Recovery  . . . :   Ensure that all character or graphic comparisons,       
      concatenation, or assignments are between columns or host variables with  
      compatible CCSID values.                                                  
        If this is a CONNECT statement, change either the SBCS CCSID of the     
      application requester or the application server, so conversion between the
      CCSID values is defined.                   
                                   


    And, in the program, I get a SQLSTT 57017 - Character conversion is not defined.
    I've tried the following options:

    • Changing the job CCSID. Fail.
    • Changing the fields CCSID in the program. FAIL
    • Compile with diferent combinations of the parameters LANGID and CVTCCSID. FAIL

    The only thing that works fine, is to change the source file CCSID (I've copied it to another source file created with CCSID 284).

    So, I know one solution could be to change the 65535 into 284 (or 1145 that fits better our language settings).
    But, any idea for other solution?

    Thanks!



    ------------------------------
    Íñigo Redín
    ------------------------------


  • 2.  RE: Problems with JSON_TABLE and CCSID in SQLRPGLE

    Posted Mon May 02, 2022 04:30 AM



    In another forum someone has given me the answer.
    Just define the first part of JSON_TABLE as a variable in the program.

    So, the new version of the program could be:

    **free
    
    ctl-opt dftactgrp(*no) option(*srcstmt:*NODEBUGIO);
    
    dcl-s jsonv varchar(256) ;
    dcl-s text varchar(34) ;
    dcl-s key varchar(10) ;
    
    TEXT='TEST, AND TEXT';
    key = 'test_text';
    exec sql set :jsonv=json_object(:key value :text);
    
    return;   


    And I don't need to include the CCSID in the definition of the variables.


    Anyway, i think it's an strange behaviour.



    ------------------------------
    Íñigo Redín
    ------------------------------