• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • A First Look At SQL Descriptors

    September 15, 2015 Paul Tuohy

    SQL descriptors allow for incredible flexibility when it comes to constructing dynamic SQL statements and/or processing the results of a dynamic SQL statement. But, since this is an introduction, let’s look at how they can be used in constructing dynamic SQL statements and leave their use in processing results for a later article.

    Assume we want to embed the following SQL statement in an RPG program:

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

    The difficulty we have is that comparison values may or may 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.

    This difficulty can be tackled in one of three ways:

    1. Use static SQL.

    2. Construct a dynamic statement that includes the comparison values.

    3. Construct a dynamic statement that uses parameter markers and an SQL descriptor.

    The following sample programs perform the same multi-row fetch for the required select statement. The difference in the programs is how the statement is constructed and executed.

    Using Static SQL

    The following piece of code shows a program using static SQL to perform the select. The solution is to always compare against the three columns regardless of whether or not a comparison value is provided.

    The test against the department code is for an equal comparison. Since it is not possible to have a generic “equal to any value”, a BETWEEN comparison is used instead of an equal comparison. Two work fields (p_fromDept and p_fromDept) are used to set the required range: Which will be lowest possible value to highest possible value if a department code is not requested; and from the requested department to the requested department if a department code is provided.

    It is a little simpler with the two dates since the comparison is for greater than or equal to. Just default the comparisons values to lowest possible date and use the requested date, if required.

         d getRows         s             10i 0 inz(%elem(data))
         d gotRows         s             10i 0
         d data            ds                  qualified dim(500)
         d  workDept                      3a
         d  empno                         6a
         d  firstName                    12a   varying
         d  lastName                     15a   varying
    
         d getDept         s              3a
         d getHired        s               d
         d getBirth        s               d
    
         d setParm         ds                  inz
         d  p_fromDept                    3a   inz(*loval)
         d  p_toDept                      3a   inz(*hiVal)
         d  p_hired                        d
         d  p_birth                        d
    
          /free
           if (getDept <> *blanks);
              p_fromDept = getDept;
              p_toDept = getDept;
           endIf;
    
           if (getHired <> d'0001-01-01');
              p_hired = getHired;
           endIf;
    
           if (getBirth <> d'0001-01-01');
              p_birth = getBirth;
           endIf;
    
           exec SQL
              declare C1 scroll cursor for
                 select workDept, empno, firstname, lastname
                 from employee
                 where workdept between 
                          :p_fromDept and :p_toDept and
                       hiredate >= :getHired and
                       birthdate >= :getBirth
                 order by workDept, empno ;
    
           exec SQL
              open C1 ;
    
           exec SQL
              fetch first from C1 for :getRows rows into :data;
    
           gotRows = SQLErrd(3);
    
           exec SQL
              close C1;
    
           *inLR = *on;
          /end-Free
    

    Although at first glance this may seem like an elegant solution, it isn’t. We are asking the query engine to perform unnecessary work:

    • Always compare the columns, regardless of whether or not they are required.
    • Potentially, perform comparisons that we know will return all rows.
    • For the Department, perform a BETWEEN comparison instead of an EQUAL comparison.

    Once upon a time, we might have been able to make an argument that using static SQL would be faster than using dynamic SQL, but that is no longer the case. So, on to the second solution.

    Construct a Dynamic Statement

    This next piece of code shows a program that constructs a dynamic SQL statement. The logic of the program constructs the required “where” clause, including the comparison values as part of the statement that is prepared.

         d getRows         s             10i 0 inz(%elem(data))
         d gotRows         s             10i 0
         d data            ds                  qualified dim(500)
         d  workDept                      3a
         d  empno                         6a
         d  firstName                    12a   varying
         d  lastName                     15a   varying
    
         d getDept         s              3a
         d getHired        s               d
         d getBirth        s               d
    
         d myStatement     s           2000a   varying
         d myWhere         s            100a   varying
         d pad_And         s              5a   varying
    
         d QUOTE           c                   ''''
         d ADD_AND         c                   ' and '
         d ADD_WHERE       c                   ' where '
    
          /free
           if (getDept <> *blanks);
              myWhere = 'workDept = ' + QUOTE + getDept + QUOTE + ' ';
              pad_And = ADD_AND;
           endIf;
    
           if (getHired <> d'0001-01-01');
              myWhere += pad_And + 'hiredate >= ' + QUOTE +
                         %char(getHired) + QUOTE + ' ';
              pad_And = ADD_AND;
           endIf;
    
           if (getBirth <> d'0001-01-01');
              myWhere += pad_And + 'birthdate >= ' + QUOTE +
                         %char(getBirth) + QUOTE + ' ';
              pad_And = ADD_AND;
           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
                 open C1 ;
    
              exec SQL
                 fetch first from C1 for :getRows rows into :data;
    
              gotRows = SQLErrd(3);
    
              exec SQL
                 close C1;
           endIf;
    
           *inLR = *on;
          /end-Free
    

    While this is manageable, it is not the easiest code to write and debug. (I have lost count of the number of times I have left out a QUOTE).

    Although not in this case, there is also the potential for injection attacks if long generic host variables are used for the replacement values.

    Construct a Dynamic Statement Using an SQL Descriptor

    The preference with dynamic SQL statements is to use parameter markers whenever possible. In this case, the difficulty is that there may be one, two, three, or no parameter markers, and there is no consistency as to which marker is for which parameter. Add to this the difficulty that the number of host variables that would be provided on the OPEN statement would need to correspond to the number of markers in the statement.

    An SQL descriptor helps us overcome these problems. SQL descriptors once required the inclusion of a special data structure (the SQLDA) and a knowledge of the fields within the structure. But V5R4 introduced a much easier way of handling SQL descriptors.

    The basic principle of using an SQL descriptor with parameter markers is:

    • Allocate a Descriptor.
    • Add a where condition to the SQL statement for each required parameter, using a parameter marker for the parameters.
    • Add the comparison value to the Descriptor for each required parameter. As well as the value, we must also provide the type and (if required) length of each parameter.
    • Open the cursor using the SQL descriptor.

    The important point to note is that the comparisons (if they are to be included) are added in sequence from left to right in the statement and that the corresponding values are added to the descriptor.

    The next piece of code shows a program that constructs a dynamic SQL statement using parameter markers and uses an SQL descriptor to provide the required parameter values. Please refer to the following callouts for details when looking at this next piece of code.

    A. Although a literal can be used for the descriptor name, it allows more flexibility if a host variable is used to provide the name. If we decide to change the name at a later stage, we only have to change it in one place.

    B. Named constants are used to provide the components of the WHERE clause. Each component has the required parameter marker–no need for those QUOTES.

    C. We ALLOCATE the descriptor and specify the maximum number of parameters–three in this case.

    D. If a comparison is to be made against the department code we add the comparison to the where clause and add the parameter value to the descriptor.

    E. The SET DESCRIPTOR statement sets a value for the required parameter number. This is the sequence of the marker in the constructed statement. The TYPE indicates the data type (1 is character). LENGTH is the length of the data being provided. Data is the value to be used in the comparison.

    F. Repeat the process for the Hire Date and Date of Birth.

    G. TYPE is 9 for a date/time/timestamp and DATE_TIME_INTERVAL_CODE of 1 indicates that the value provided is a date.

    H. SET DESCRIPTOR with COUNT is used to indicate how many parameters were added to the descriptor.

    I. When opening the cursor the “populated” SQL descriptor is used to provide the required parameter values.

    J. Deallocate the descriptor when we are finished with it.

      d getRows         s             10i 0 inz(%elem(data))
         d gotRows         s             10i 0
         d data            ds                  qualified dim(500)
         d  workDept                      3a
         d  empno                         6a
         d  firstName                    12a   varying
         d  lastName                     15a   varying
    
         d getDept         s              3a
         d getHired        s               d
         d getBirth        s               d
    
         d myStatement     s           2000a   varying
         d myWhere         s            100a   varying
         d pad_And         s              5a   varying
         d parmNo          s             10i 0
    (A)  d descName        s             20a   varying inz('TEST_DESC')
    
         d ADD_AND         c                   ' and '
         d ADD_WHERE       c                   ' where '
    (B)  d ADD_DEPT        c                   ' workDept = ? '
         d ADD_HIRE        c                   ' hiredate >= ? '
         d ADD_BIRTH       c                   ' birthdate >= ? '
    
          /free
           exec SQL
    (C)       allocate descriptor local :descName with max 3;
    
           if (getDept <> *blanks);
    (D)       myWhere = ADD_DEPT;
              pad_And = ADD_AND;
              parmNo += 1;
              exec SQL
                 set descriptor :descName
    (E)             value :parmNo type = 1,
                                  length = 3,
                                  data = :getDept;
           endIf;
    
           if (getHired <> d'0001-01-01');
    (F)       myWhere += pad_And + ADD_HIRE;
              pad_And = ADD_AND;
              parmNo += 1;
              exec SQL
                 set descriptor :descName
    (G)             value :parmNo type = 9,
                                  datetime_interval_code = 1,
                                  data = :getHired;
           endIf;
    
           if (getBirth <> d'0001-01-01');
    (F)       myWhere += pad_And + ADD_BIRTH;
              pad_And = ADD_AND;
              parmNo += 1;
              exec SQL
                 set descriptor :descName
    (G)             value :parmNo type = 9,
                                  datetime_interval_code = 1,
                                  data = :getBirth;
           endIf;
    
           if (parmNo > 0);
              myWhere = ADD_WHERE + myWhere;
           endIf;
    
           myStatement = 'select workDept, empno, firstname, lastname ' +
                         ' from employee ' +
                         myWhere +
                         ' order by workDept, empno';
    
           exec SQL
    (H)       set descriptor :descName count = :parmNo;
    
           exec SQL
              prepare D1 from :myStatement;
    
           if SQLCode = 0;
              exec SQL
                 declare C1 scroll cursor for D1;
    
              exec SQL
    (I)          open C1 using SQL descriptor :descName;
    
              exec SQL
                 fetch first from C1 for :getRows rows into :data;
    
              gotRows = SQLErrd(3);
    
              exec SQL
                 close C1;
           endIf;
    
           exec SQL
    (J)       deallocate descriptor local :descName;
    
           *inLR = *on;
          /end-Free
    

    Although still somewhat cumbersome (as dynamic SQL always is), using an SQL descriptor allows us to code the use of a parameter and the provision of the value in one place.

    Some Other Considerations

    An SQL descriptor can be allocated as LOCAL, meaning it is scoped to the program, or GLOBAL, meaning it is scoped to the SQL session and may be used in other programs.

    Refer to the description of SET DESCRIPTOR in the SQL reference manual for a full list of the data type codes available for TYPE.

    This article showed how an SQL descriptor may be used to set variable parameters in a dynamic SQL statement. This is just one of the many uses of SQL descriptors: for example, they can also be used to retrieve information from a dynamic statement where the select clause is variable. But that is another article!

    Paul Tuohy is CEO of ComCon, an iSeries consulting company, and is one of the co-founders of System i Developer, which hosts the RPG & DB2 Summit conferences. He is an award-winning speaker who also speaks regularly at COMMON conferences, and is the author of “Re-engineering RPG Legacy Applications,” “The Programmers Guide to iSeries Navigator,” and the self-study course called “iSeries Navigator for Programmers.” Send your questions or comments for Paul to Ted Holt via the IT Jungle Contact page.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    ProData Computer Services:  Zero in on the data YOU need NOW with DBU, RDB Connect and SQL/Pro.
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars.
    System i Developer:  Session Grid Posted: RPG & DB2 Summit - Chicago, October 20-22

    Reader Feedback On A Hypothetical Future IBM i System Hacker Defends DEF CON Talk on IBM i Vulns

    Leave a Reply Cancel reply

Volume 15, Number 18 -- September 15, 2015
THIS ISSUE SPONSORED BY:

ProData Computer Services
CCSS
WorksRight Software

Table of Contents

  • A First Look At SQL Descriptors
  • Another Reason To Use Unrequired Correlation Names
  • RCAC In DB2 For i, Part 3: Advanced Topics

Content archive

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

Recent Posts

  • Public Preview For Watson Code Assistant for i Available Soon
  • COMMON Youth Movement Continues at POWERUp 2025
  • IBM Preserves Memory Investments Across Power10 And Power11
  • Eradani Uses AI For New EDI And API Service
  • Picking Apart IBM’s $150 Billion In US Manufacturing And R&D
  • FAX/400 And CICS For i Are Dead. What Will IBM Kill Next?
  • Fresche Overhauls X-Analysis With Web UI, AI Smarts
  • Is It Time To Add The Rust Programming Language To IBM i?
  • Is IBM Going To Raise Prices On Power10 Expert Care?
  • IBM i PTF Guide, Volume 27, Number 20

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