CODEWITHSUNDEEP
sqljsonibm-midrangerpgle
Íñigo
Íñigo

Reputation: 63

Problems with SQLRPGLE and CCSID in JSON_TABLE

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:

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!

Upvotes: 4

Views: 1490

Answers (1)

Charles
Charles

Reputation: 23791

The right answer is not to use CCSID 65535 for anything that doesn't actually contain binary data...

I was thinking that the correct usage of the CCSID control-spec might solve your issue. But from what I can tell, that is not the case.

I think you're actually running into an issue with how the SQL Pre-compiler handles literals in a source file with CCSID(65535).

Removing the literal in the SQL statement, stopped the run-time error for me.

**free

ctl-opt dftactgrp(*no) option(*srcstmt:*NODEBUGIO);

dcl-s jsonv varchar(256) ccsid(1145);
dcl-s text varchar(34) ccsid(1145);
dcl-s key varchar(10) ccsid(1145);

TEXT='TEST, AND TEXT';
key = 'test_text';
exec sql set :jsonv=json_object(:key value :text);

return;

The CRTSQLRPGI command has an new "Conversion CCSID . . . CVTCCSID" parameter, but that appears to be specifically for UTF-8 stream file sources

You could open a case with IBM, since it doesn't appear that the pre-compiler is honoring the ctl-opt ccsid(*char:1145); . But I suspect they will tell you they are not going to fix it and that you shouldn't have your source file defined with CCSID(65535).

Upvotes: 6

Related Questions