• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: SUBSET on EXECUTE and OPEN

    June 15, 2020 Paul Tuohy

    The DB2 for i Enhancements in IBM i 7.3 Technology Refresh 8 / IBM i 7.4 Technology Refresh 2 just made dynamic SQL, embedded in RPG, a lot easier to use. It includes an enhancement entitled USING SUBSET on EXECUTE and OPEN with Extended indicators. This enhancement provides a simple and effective solution to a problem that I first highlighted in an article I wrote back in September of 2015. (See A First Look At SQL Descriptors.)

    The “problem” we are dealing with is how to handle a variable number of host variables being used in a dynamic SQL statement. In the aforementioned article, I used the following SQL statement as an example:

    select workDept, empno, firstname, lastname from employee
       where workDept  = ?  and
             hireDate  >= ? and
             birthDate >= ? 
       order by workDept, empno
    

    The difficulty was that comparison values might or might not be provided for the department code, hire date and birth date. Therefore, the where clause (if it is to be included) might be against any combination of the three columns.

    There were three possible coding solutions, each with its own caveats

    • Use static SQL, which (most of the time) will result in the query engine performing unnecessary and inefficient work.
    • Construct a WHERE clause that includes the comparison values, which results in convoluted, error-prone string handling with the (small) potential for an SQL injection attack.
    • Use an SQL Descriptor, which (although better than the previous solutions) is not intuitive and requires a lot of attention to detail, especially for handling the data types of the host variables.

    Code examples of these three solutions may be found in the A First Look At SQL Descriptors article. The new SUBSET option provides a much more eloquent solution.

    SUBSET

    The new SUBSET makes use of SQL indicators (an integer in RPG) associated with the host variable. You will have used one of these SQL indicators if you have ever dealt with null values for columns. Giving a value of -7 to an SQL indicator (for a host variable) is the equivalent of assigning an SQLIND_UNASSIGNED value to the variable.

    This is an example of a program using dynamic SQL to generate the required SQL statement and using the new SUBSET option to assign the required values from host variables. The program defines a cursor for the generated statement and uses a multi row fetch to retrieve the rows. Please refer to the call outs in the code below.

           dcl-s getRows         int(10) inz(%elem(data));
           dcl-s gotRows         int(10);
           dcl-ds data qualified dim(500);
             workDept  char(3);
             empno     char(6);
             firstName varchar(12);
             lastName  varchar(15);
           end-ds;
    
    (A)    dcl-s getDept     char(3);
           dcl-s getHired    date;
           dcl-s getBirth    date;
    
    (B)    dcl-s getDept_ind int(5); 
           dcl-s getHired_ind int(5); 
           dcl-s getBirth_ind int(5); 
    
    (C)    dcl-s myStatement varchar(2000);
           dcl-s myWhere     varchar(100);
           dcl-s pad_And     varchar(5);
    
           dcl-C ADD_AND   ' and ';
           dcl-C ADD_WHERE ' where ';
    (D)    dcl-C ADD_DEPT  ' workDept = ? ';
           dcl-C ADD_HIRE  ' hiredate >= ? ';
           dcl-C ADD_BIRTH ' birthdate >= ? ';
    
           if (getDept <> *blanks);
    (E)      myWhere = ADD_DEPT;
             pad_And = ADD_AND;
             getDept_ind = 0;
           else;
    (F)      getDept_ind = -7;
           endIf;
    
           if (getHired <> d'0001-01-01');
    (G)      myWhere += pad_And + ADD_HIRE;
             pad_And = ADD_AND;
             getHired_ind = 0;
           else;
             getHired_ind = -7;
           endIf;
    
           if (getBirth <> d'0001-01-01');
    (G)      myWhere += pad_And + ADD_BIRTH;
             pad_And = ADD_AND;
             getBirth_ind = 0;
           else;
             getBirth_ind = -7;
           endIf;
    
           if (myWhere <> '');
              myWhere = ADD_WHERE + myWhere;
           endIf;
    
           myStatement = 'select workDept, empno, firstname, lastname ' +
                         ' from employee ' +
                         myWhere +
                         ' order by workDept, empno';
    
           exec SQL
              prepare D1 from :myStatement;
    
           if SQLCode = 0;
              exec SQL
                 declare C1 scroll cursor for D1;
    
              exec SQL
    (H)          open C1 using SUBSET :getDept  :getDept_ind,
                                      :getHired :getHired_ind, 
                                      :getBirth :getBirth_ind;
    
              exec SQL
                 fetch first from C1 for :getRows rows into :data;
    
              gotRows = SQLErrd(3);
    
              exec SQL
                 close C1;
           endIf;
    
           *inLR = *on;
    

    (A) Variables that may contain the required comparison values in the where clause. They would usually be passed as parameters.

    (B) SQL indicators associated with the host variables that will be used in the OPEN statement.

    (C) Work variables used in the construction of the dynamic SQL statement.

    (D) Named constants are used to provide the components of the WHERE clause. Each component has the required parameter marker.

    (E) If the statement is to include a comparison against the department code, then add the comparison to the WHERE clause and set the associated indicator to 0 (to indicate there is a value).

    (F) If the statement will NOT include a comparison against the department code, then set the associated indicator to -7 (to indicate that the variable is to be ignored).

    (G) Repeat the same process for the other two possible comparison values.

    (H) When opening the cursor, you specify all of the host variables in the list and you precede that list with SUBSET. The host variables will have a value or they will be SQLIND_UNASSIGNED. Variables with SQLIND_UNASSIGNED are ignored for assignment. The important thing to note is the sequence of the host variable names must correspond to the sequence they assigned in the WHERE clause.

    The new SUBSET feature make the proper construct (using parameter markers) of a dynamic SQL statement a lot easier and, therefore, makes dynamic SQL a lot easier to use.

    Paul Tuohy, IBM Champion and author of Re-engineering RPG Legacy Applications, is a prominent consultant and trainer for application modernization and development technologies on the IBM Midrange. He is currently CEO of ComCon, a consultancy firm in Dublin, Ireland, and partner at System i Developer. He hosts the RPG & DB2 Summit twice per year with partners Susan Gantner and Jon Paris.

    RELATED STORIES

    A First Look At SQL Descriptors

    Guru: Alternate SQL Row-Selection Criteria Revisited

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, DB2 for i, FHG, Four Hundred, IBM i, IBM i 7.3 Technology Refresh 8, IBM i 7.4 Technology Refresh 2, RPG, SQL

    Sponsored by
    TL Ashford

    TL Ashford writes software to generate Barcode Labels and Forms for the IBM i.

    Our software products are the most feature-rich, cost-effective solutions available!

    TLAForms converts traditional IBM i spool files into high quality, professional forms that are output as PDF documents. A form is associated with a spool file and the form is designed. TLAForms then ‘watches’ the IBM i output queue for a new spool file to be generated. When a new spool file is generated, TLAForms reads the data from the spool file, places the data on the form, and outputs a PDF document. The PDF documents can be archived to the IFS, automatically emailed, and printed.

    Features:

    • Select Data directly from an IBM i Spool File
    • Burst IBM i Spool Files based on page or Spool File data
    • Add spool file data to form as Text using a wide variety of fonts and colors (the MICR font for printing checks is included in the software)
    • Add spool file data to form as bar code – including 2-dimensional bar codes PDF-417 and QRCode
    • Configure SQL statements to retrieve and use IBM i File data on forms
    • Utilize Actions to show or hide objects on the form based on data within the spool file
    • Import Color Graphics
    • Use Color Overlays
    • Create Tables
    • Forms can be archived to the IFS
    • Forms can be emailed automatically
    • Forms can be printed to any IBM i, Network or Windows printer
    • Forms are automatically generated when a new spool file is generated in the IBM i output queue
    • NO PROGRAMMING required
    • On-Line Video Training Library

    Learn more about TLAForms at www.tlashford.com/TLA2/pages/tlaforms/overview.html

    Barcode400 is native IBM i software to design and print labels directly from your IBM i in minutes! Compliance and RFID labeling is easy using Barcode400’s tools and templates.

    Features:

    • Software resides on the IBM i
    • IBM i security and Backup
    • Labels are centrally located on the IBM i
    • Label formats are available to all users the instant they are saved – even in remote facilities
    • GUI designer (Unlimited Users)
    • Generate Labels as PDF documents!
    • Print to 100’s of thermal transfer printers
    • Print to HP and compatible printers
    • Print labels interactively – No Programming Necessary!

    OR Integrate into existing application programs to automatically print labels – Barcode400 has been integrated with nearly every ERP and WMS software available on the IBM i, including thousands of in-house written applications.

    • On-Line Video Training Library
    • Free Compliance Labels
    • Generate Checks using the MICR font
    • RFID Support (optional)
    • History / Reprint Utility
    • Integration Assistant
    • Low Cost (no tiered pricing)

    Learn more about Barcode400 at www.tlashford.com/TLA2/pages/bc400labels/overview.html

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Four Hundred Monitor, June 15 Why You Need To Implement Exit Point Security – Now

    One thought on “Guru: SUBSET on EXECUTE and OPEN”

    • Danny Rodius says:
      October 21, 2020 at 3:51 am

      Good article. Got a first use for it an stumbled already to a problem. When used as a method of filling a subfile and closing the cursor, even with the CLOSQLCSR(*ENDMOD), we are getting an SQL0501 error saying the cursor is still open or in use. Any idea of solving this.

      Reply

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 37

This Issue Sponsored By

  • Fresche Solutions
  • MITEC
  • TL Ashford
  • Kisco Information Systems
  • WorksRight Software

Table of Contents

  • More Open Source Databases Coming To IBM i
  • Why You Need To Implement Exit Point Security – Now
  • Guru: SUBSET on EXECUTE and OPEN
  • Four Hundred Monitor, June 15
  • IBM i PTF Guide, Volume 22, Number 24

Content archive

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

Recent Posts

  • SolarWinds Hack Raises Concern for IBM i Shops
  • Can You Build Data Integrity Without Securing IBM i Systems?
  • Rocket Rebrands, Updates, and Discounts Terminal Emulator
  • Four Hundred Monitor, January 27
  • IBM i PTF Guide, Volume 23, Number 4
  • Taking The Full Measure Of Power Servers
  • It’s Time For An Application Healthcheck
  • Guru: What’s Your RDi Preference?
  • As I See It: How Did Cyber Security Get So Bad?
  • Here Are the New IBM i Champions for Power

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 © 2021 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.