ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Using Clob Fields in SQL

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Using Clob Fields in SQL

    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))));​

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

    Comment


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

      Click image for larger version

Name:	image.png
Views:	392
Size:	38.5 KB
ID:	158768

      Comment


      • #4
        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.

        Comment


        • #5
          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.)

          Comment


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

            Comment


            • #7
              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.

              Code:
              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;

              Code:
              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.

              Comment

            Working...
            X