I am accessing a IBMi running DB2 from a java JVM. I have a stored procedure that returns a CLOB and I want to access the clob from Java.
The sql for the stored procedure is:
CREATE OR REPLACE PROCEDURE ZZSP (
IN fromjson VARCHAR(16000) CCSID 1208,
out tojson clob(10000000) CCSID 1208,
out last_id decimal(20, 0),
out sha VARCHAR(20) CCSID 1208,
out version VARCHAR(20) CCSID 1208)
LANGUAGE RPGLE
SPECIFIC ZZSP
DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
COMMIT ON RETURN YES
EXTERNAL NAME 'ZZSP'
PARAMETER STYLE SQL;
The stored procedure can be accessed from 'run sql scripts' in IBMi client access solutions:
create variable MYCLOB5 clob(10000000) ccsid 1208;
CALL ZZSP ('{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}',myClob5,?,?,?);
values myClob5;
When I do this, the 'values myClob5' holds a json string that is almost identical to the passed in json string (because that's what the stored procedure does). I am thus assuming that the rpg code that this calls (also called ZZSP) is working.
However when I try and access this from java I run into a problem. I can use this code to get data out of a resultset, but not out of the statement. Prior to the call we have set up parameters in the callable statement:
void doit(CallableStatement s){
cs.setString(1, json)
cs.registerOutParameter(2, Types.CLOB)
cs.registerOutParameter(3, Types.DECIMAL)
cs.registerOutParameter(4, Types.VARCHAR)
cs.registerOutParameter(5, Types.VARCHAR)
// setup the statement with the sql 'call ZZSP(?,?,?,?,?)', and execute it
val jsonOutClob = s.getClob(2)
val json = jsonOutClob.getSubString(1, jsonOutClob.length().toInt)
...
}
While all the other data returned from the stored procedure (outParameters 3,4,5) return the correct value, the data in the clob is 'garbage': it doesn't look like a string to me. The data I expect is something like '{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}', but the actual data is something like '?zɕƿk??𒅨z?????????????⣁zտЀ'. It's also interesting that the length of the clob is half the length of the expected string. The bytes in the returned string start with List(3f, f1, 85, a3, a4, f1, 95, a2, a3, f1, a3, a4, a2, 7f, 7a, 7f, c9, 95,...)
I have tried other methods on the statement: getCharacterStream for example, and it returns the same data.
It feels that this is a character encoding issue, but I don't know how to address it. It also feels that it might be something around the 'parameter style' which I have set to 'sql'. We have tried general as well and with that the 'iac' program also returns garbage.
I am in week two of this problem... we had a load of other issues in the RPG code which have been fixed, but for this one I am tearing my hair out.
Can anyone advise me how to get the data out of a stored procedure when the data is a 'clob'? If that is impossible, could you advice me how to get a large string (perhaps a megabyte) out of a stored procedure?
Tech stack:
Edited in to answer Charles question:
The rpg program starts like this. This is constantly being changed because we are experimenting with how to make the toJson have the correct CCSID. The two /Set ccsid (*char : *UTF8) are obviously not both needed. We've tried all four permutation (none/first/second/both). This is the code that 'works' when we call the program from IAC.
**free
ctl-opt option(*srcStmt:*noDebugIO) dftactgrp(*no) actgrp('Somegroup') CCSID(*char:*jobRun);
exec SQL
set option commit = *none;
/Set ccsid (*char : *UTF8)
dcl-s toJsontemplate sqltype(CLOB:10000000);
/restore CCSID(*char)
dcl-pi *n;
fromJson varChar(16000) ccsid(*utf8);
/Set ccsid (*char : *UTF8)
toJson likeds(toJsontemplate);
/restore CCSID(*char)
id packed(20);
sha varChar(20) ccsid(*utf8);
version varChar(20) ccsid(*utf8);
end-pi;
The place where the variable is set is looks like this
exec SQL
set :tojson = json_object(
'returnStatus' : trim(:returnStatus),
'fetchKey' : json_object(...
Following the advice in the answers I used opt-ctl to set ccsid global.
**free
ctl-opt option(*srcStmt:*noDebugIO) dftactgrp(*no) actgrp('Somegroup') CCSID(*char:*UTF8);
exec SQL
set option commit = *none;
dcl-s toJsontemplate sqltype(CLOB:10000000);
dcl-pi *n;
fromJson varChar(16000) ccsid(*utf8);
toJson likeds(toJsontemplate);
id packed(20);
sha varChar(20) ccsid(*utf8);
version varChar(20) ccsid(*utf8);
end-pi;
This made absolutely no difference. I think the variable tojson holds utf8 data (unless there is a bug in RPG) as opt-ctl sets the ccsid for the entire module. I think it is now clear (since the IAC can actually read the clob correctly) that this is some sort of negotiation issue between the Jdbc and the stored procedure. IAC does it properly and gets the correct data, and my code doesn't and gets the wrong data.
Any idea where I can go from here?
This was much harder than expected. In the end I gave up doing it directly: I could not get the result from the clob into java in the manner I expected.
However 'there's always a way in IT'. So we ended up doing the same as we had working in IAC:
We used a global variable ('clob5' in this example). A global variable is defined in 'some library', but each session gets it's own value so we have no parallelism issues. The variable is defined once and then left alone
When we called the procedure in the stored procedure we use the same command we used
CALL ZZSP ('{"returnStatus":" ","fetchKey":{"inp1":1,"seq1":2},"status":"X"}',myClob5,?,?,?);
In order to get the value out of the variable we used (using the same connection)
SELECT myclob5 from SYSIBM.SYSDUMMY1