IBM i Global

IBM i 

A space for professionals working with IBM’s integrated OS for Power systems to exchange ideas, ask questions, and share expertise on topics like RPG and COBOL development, application modernization, open source integration, system administration, and business continuity.


#Power


#IBMi
 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

    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
    ------------------------------

    Original Message


  • 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

     

     

     

     




    Original Message


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

    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
    ------------------------------

    Original Message


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

    Original Message


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

    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
    ------------------------------

    Original Message


Related Content