• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • RPG And The BLOB

    December 13, 2016 Hey, Mike

    I’m trying to use a SELECT/INTO embedded SQL statement in an RPG program that accesses a table that includes a BLOB(20K) column. The INTO target is an externally defined data structure based on the table. However, I get compiler errors unless I remove the BLOB column from the table. What’s up with that?

    —Four Hundred Guru reader

    The problem is that RPG doesn’t have a native data type equivalent of a BLOB (or the other large object types CLOB/DBCLOB, for that matter.) The reason for this is that large object types can be up to 2GB in size, far greater than RPG’s current maximum variable size of 16MB.

    Consider the following table definition containing a BLOB:

    CREATE OR REPLACE TABLE QGPL.TEST (
    MY_KEY INT NOT NULL PRIMARY KEY,
    MY_DATA VARCHAR(32) NOT NULL,
    MY_IMAGE BLOB(20K) NOT NULL)
    RCDFMT TESTR 
    

    Within the RPG program is an externally described data structure dsTest (referencing the above table) and the following embedded SQL statement:

    SELECT *
      INTO :dsTest
      FROM Test
     WHERE My_Key=:My_Key;
    

    When using an externally described data structure containing a BLOB, the RPG compiler gives an ugly warning:

    RNF7575 The data structure has fields with an unknown datatype.
    The fields are ignored.
    

    This warning indicates that the RPG compiler doesn’t know what to do with the BLOB data type column so it ignores it. The data structure definition in the compiler listing doesn’t contain MY_IMAGE. This means that the embedded SQL statement is going to retrieve three column values but the externally described data structure only offers two values for storage. Because the data structure doesn’t match the SELECT statement’s field list, the RPG compiler may generate any number of errors related to data type mismatches and the like, depending on the table’s definition.

    If you don’t need to access the BLOB data in the RPG program, one way around this is to simply code the data structure and the SELECT INTO by hand and omit and large object data type columns. However, if there are a large number of columns in the table, this option is unattractive.

    Another option is to create a view on the table that omits the BLOB column and then reference the view in the externally described data structure. Yet another option is move the BLOB into its own table so that developers can happily continue to work with their externally defined data structures.

    If the RPG program needs the BLOB data, you will have to manually define your data structure. This example only uses a 20K length BLOB, so it can be loaded into a single RPG variable (defined using the special SQLTYPE):

    DdsTest           DS
    D  My_Key                       10I 0
    D  My_Data                      32    VARYING
    D  MY_IMAGE                           SQLTYPE(VARBINARY:20480)
    

    Technically, you don’t have to use the SQLTYPE as shown above, in reality the compiler just defines MY_IMAGE as 20480A VARYING CCSID(65535).

    If your BLOB size is larger than 16MB, then you will have to use a “locator” variable to reference the BLOB data. A locator variable is just a pointer to a location within DB2’s vast world of memory management where DB2 keeps track of the large object data so that the RPG program doesn’t have to:

    DdsTest           DS
    D  My_Key                       10I 0
    D  My_Data                      32    VARYING
    D  MY_IMAGE                           SQLTYPE(BLOB_LOCATOR)
    

    Using locators is not allowed unless the transaction isolation level is set to a value other than *NONE. The SET OPTION statement is one way to change this setting:

    Exec SQL
        SET OPTION COMMIT=*CHG;
    

    Since DB2 is “housing” the large object data on behalf of the RPG program, all operations on the BLOB need to be done using embedded SQL:

    Exec SQL
        SET :IMAGE_SIZE=LENGTH(:MY_IMAGE);
    

    In summary, that is the unattractive manner in which an RPG program can work with a large object data column up to 2GB in size.

    –Michael Sansoterra

    RELATED STORY

    Native Regular Expressions In DB2 For i 7.1 And 7.2

     

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Profound Logic Software:  NOW ON DEMAND! Webinar: Agile Modernization with Node.js. Fresche:  IBM i staffing for all of your IT needs. Request a FREE estimate. 1-800-361-6782 System i Developer:  RPG & DB2 Summit - March 21-23, 2017 in Orlando. Register now!

    IT Spending And Staffing Show The Effects Of Managed Services The IBM i Year In Review

    One thought on “RPG And The BLOB”

    • George Varvaressos says:
      January 17, 2019 at 4:53 am

      I have extracted an SAA Timestamp from an IBM i journal

      2009-06-02-13.15.56.083280

      How do I edit it to get

      2009-06-02 13:15:56

      Thanks.

      Reply

    Leave a Reply Cancel reply

Volume 16, Number 27 -- December 13, 2016
THIS ISSUE SPONSORED BY:

WorksRight Software
T.L. Ashford
UCG Technologies

Table of Contents

  • RPG And The BLOB
  • How Do You Do That with RDi? Part 2: Compile

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • To Comfort The Afflicted And Afflict The Comfortable
  • How FalconStor Is Reinventing Itself, And Why IBM Noticed
  • Guru: When Procedure Driven RPG Really Works
  • Vendors Fill In The Gaps With IBM’s New MFA Solution
  • IBM i PTF Guide, Volume 27, Number 27
  • With Power11, Power Systems “Go To Eleven”
  • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
  • Izzi Buys CNX, Eyes Valence Port To System Z
  • IBM i Shops “Attacking” Security Concerns, Study Shows
  • IBM i PTF Guide, Volume 27, Number 26

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle