IBM i Global

 View Only
  • 1.  Easily handle CLOB/BLOB fields in an RPGLE program

    Posted Fri July 15, 2022 10:11 AM
      |   view attached
    Trying to take the data from a Table (PF) in a library and convert the data into my ext described file using DDS.
    (Included the DSPFFD of the file I'm working on)

    Never worked with direct table files (PF in a library.) Getting CPF428A error message.

    Basically the file contains BLOBs, and I've no idea how to process the file.

    I have found things like adding

    DCL-S MYCLOB SQLTYPE(CLOB:1000);
    DCL-S MYBLOB SQLTYPE(BLOB:500);
    DCL-S MYDBCLOB SQLTYPE(DBCLOB:400);


    These convert into:


    DCL-DS MYCLOB;
    MYCLOB_LEN UNS(10);
    MYCLOB_DATA CHAR(1000) CCSID(*JOBRUNMIX);
    END-DS MYCLOB;



    DCL-DS MYDBCLOB;
    MYDBCLOB_LEN UNS(10);
    MYDBCLOB_DATA GRAPH(400);
    END-DS MYDBCLOB;



    DCL-DS MYBLOB;
    MYBLOB_LEN UNS(10);
    MYBLOB_DATA CHAR(500) CCSID(*HEX);
    END-DS MYBLOB;


    What the heck do I do with these fields? How are they used within the SQLLERPG program.

    ------------------------------
    Phillip Knox
    ------------------------------

    Attachment(s)

    txt
    TBLACCDENT FFD.txt   28 KB 1 version


  • 2.  RE: Easily handle CLOB/BLOB fields in an RPGLE program

    IBM Champion
    Posted Fri July 15, 2022 01:10 PM
    Just run an SQL SELECT statement and read the columns into your MYCLOB (though, pick a better name!) just as you would any other database read.  The LEN field will contain the length of the data, the DATA field will contain the actual data.

    ------------------------------
    Scott Klement
    Director
    Profound Logic Software
    Oak Creek WI
    ------------------------------



  • 3.  RE: Easily handle CLOB/BLOB fields in an RPGLE program

    Posted Fri July 15, 2022 02:13 PM

    Can you give some examples please? I'm not familiar with what you mean.

     

    Thank you,

     

    Motor City Systems

    Phillip Knox  
    Software Engineer

    Ph 313.324.6376

    phillip@motorcity.systems

    Motorcity.Systems

     

     

     

     






  • 4.  RE: Easily handle CLOB/BLOB fields in an RPGLE program

    IBM Champion
    Posted Fri July 15, 2022 10:18 PM
    Dear Phillip

    You should find some SQL SELECT samples for reading CLOB/BLOB/DBCLOB here :
    https://techchannel.com/SMB/03/2006/blobs-clobs-rpg
    https://itpscan.ca/blog/iSeries/clobs.php

    You will find more when Googling with "ibm i rpg read clob".

    As for your question on *POINTER in some fields, I think you should not care for them as they should be handled under the cover for you by IBM i.

    ------------------------------
    Right action is better than knowledge; but in order to do what is right, we must know what is right.
    -- Charlemagne

    Satid Singkorapoom
    ------------------------------



  • 5.  RE: Easily handle CLOB/BLOB fields in an RPGLE program

    Posted Fri July 15, 2022 07:02 PM
    Scott,

    Looking at the data in the table there are four fields with *POINTER in them.
    Is there a special way to handle these fields?

    Thanks

    ------------------------------
    Phillip Knox
    ------------------------------



  • 6.  RE: Easily handle CLOB/BLOB fields in an RPGLE program

    IBM Champion
    Posted Mon July 18, 2022 02:12 AM
    The SQL precompiler converts the LOB stand alone fields into a data structure consisting of the length of the data (_LEN) and the a fixed length field with the data (_DATA).
    With RPG (opcodes and functions) you access the data structure subfield, i.e. _DATA and if you change the (length of) the data you have to determine the length and write it into the _LEN sub-field.

    MyCLOB_Data = 'ABC_DEF_GHI';
    MyCLOB_Len = %Len(%Trim(MyCLOB_Data);

    MyText = %Subst(MyCLOB_Data, 4, 3);

    Within embedded SQL commands you use the name of your original variable, i.e. the name of the data structure.
    Exec SQL Set :MyCLOB = 'ABC_DEF_GHI';
    Exec SQL Set :MyText = Substr(:MyCLOB, 4, 3);

    When using STRSQL for displaying the data you will see for LOB Data only *POINTER. STRSQL is an outdated tool which was not enhanced since release V5R3M0. You should use the ACS (IBM i Access Client Solutions) - Run SQL Script facility instead.

    ------------------------------
    Birgitta Hauser
    Database and Software Engineer
    Selfemployed - Modernization-Education-Consulting on IBM i
    Kaufering
    +49 170 5269964
    ------------------------------