• 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
    Raz-Lee Security

    With COVID-19 wreaking havoc, cybercriminals are taking advantage of the global impact that it has had on our families, our businesses and our societies. It is more important now than ever to ensure that IT systems are protected, so that when all of this is behind us, we can get back to business as usual as quickly as possible.

    iSecurity Anti-Ransomware protects organizations against ransomware attacks and other kinds of malware that may access and change business-critical data on your IBM i. It even protects against zero-day attacks. Anti-Viruses can only report on the damage an attack has caused, but not stop it.

    iSecurity Anti-Ransomware has been recently enhanced with a Self-Test feature that allows you to simulate a ransomware attack on your IBM i. The simulated attack is limited to the test folder and cannot harm any other folders or files. This new feature lets organizations see how they are protected against known or unknown ransomware.

    Key Features:

    • Real-time scanning for known and unknown ransomware threats.
    • Blocks and disconnects the intruder.
    • Instantaneously sends alerts to SIEM as well as the offending computer.
    • Self-Test for attack simulation
    • Classification of the attack based on log.
    • Automatic updates with the most current ransomware definitions.

    Contact us at https://www.razlee.com/anti-ransomware

    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

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

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 © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.