Using Clob Fields in SQL

[tbrantley]

Has anyone tried using a clob field inside of SQL?

I'm moving the data from the YAJL copyBufStr function into a clob variable.

Once I try to use the clob variable inside the SQL statement I get the error below.

Length in varying-length, LOB, or XML host variable not valid.

Cause . . . . . : Host variable JSONDATA was specified. The value in the
length portion of the variable length, LOB, or XML host variable is either
negative or greater than the declared length. If the host variable is
graphic the length should be the number of DBCS characters. The host
variable number is 2. The specified length is -1065379967. The variable is
declared to have length 100000.

Recovery . . . : Change the length portion of the varying-length, LOB, or
XML host variable to a valid positive number or zero. Try the request again.

dcl-s jsonData SQLTYPE(CLOB:100000) inz;

jsonData = yajl_copyBufStr();

EXEC SQL
SELECT RESPONSE_MESSAGE, RESPONSE_HTTP_HEADER
INTO :rspData, :rspHdr
FROM TABLE(QSYS2.HTTP_POST_VERBOSE(
CAST(trim(:myUrl) AS VARCHAR(500)),
CAST(trim(:jsonData) AS CLOB(100000)),
CAST(trim(:myHdr) AS VARCHAR(2000))));

[JonBoy]

My first thought is that the returned string is > 100,000 long. Have you checked that?

[tbrantley]

Yes I've checked that in debugger and the returned string is 35430.

[JonBoy]

Just noticed that the relevant part of your SQL makes no sense.

jsonData is a CLOB - so why are you casting it? Also a trim of a varying length field doesn't make much sense although it shouldn't do any harm.

[tbrantley]

Scott Klement helped me with a solution!

See below his comments below.

When you use SQLTYPE(CLOB:100000) (as you did), the SQL precompiler will generate an RPG data structure under the covers. So if you look at the code it generated (for example, in the compile listing) you'll see something like this:

DCL-DS
JSONDATA; JSONDATA_LEN UNS(10);
JSONDATA_DATA CHAR(100000);
END-DS;

So you need to set two fields to populate the CLOB, not just one...

jsonData_data = yajl_copyBufStr();
jsonData_Len = %len(%trimr(jsonData_data));

That's the way the SQL precompiler works (which, imho, is kinda clumsy -- but, hard to change it at this point, too many people are using it.)

[JonBoy]

Yup - I'd forgotten that they don't use real varchar under the covers. Glad you found the solution.

[Vectorspace]

Your method will work in this case, because JSON doesn't care about trailing blanks. If you did care about trailing blanks, then %len(%trimr()) would effectively remove them. To resolve that you would have to read the varchar data into a varchar variable so you can accurately extract its length.

There is another method:

The CLOB DS and the varchar actually have the exact same structure in memory - 4 unsigned int bytes for length and then x bytes of data. So to avoid having to manually set the length, what I like to do is pointer overlay a varchar(x:4) over it. Then RPGLE can reference the varchar and SQL the CLOB and it's the same thing.

dcl-c jsonDataLength const (100000);
dcl-s jsonData SqlType(CLOB:jsonDataLength);
dcl-s jsonDataV Varchar(jsonDataLength:4) based(@jsonDataV);
dcl-s @jsonDataVPointer inz(%addr(jsonData));​

jsonDataV = yajl_copyBufStr();

exec sql update myTable set jsonCol = :jsonData where keyCol=1;

Though it has also just occurred to me that instead of messing with pointers, you could put them both into an anonymous DS and use pos() to overlay;

dcl-c jsonDataLength const (100000);​
dcl-ds *n;
  jsonData SqlType(CLOB:jsonDataLength);
  jsonDataV Varchar(jsonDataLength:4) pos(1);
end-ds;

jsonDataV = yajl_copyBufStr();

exec sql update myTable set jsonCol = :jsonData where keyCol=1;

Disclaimer - I have not tested the DS approach.

[Vectorspace]

See this old thread for a more detailed discussion on the above: https://code400.com/forum/forum/iser...ar-onto-a-clob

[Vectorspace]

IDEAs submitted to IBM to improve this:

https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3732

https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3733