• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Let’s Start Over from the Beginning

    September 23, 2009 Hey, Ted

    We have RPG programs that use SQL cursors to sequentially retrieve data. If a program cancels and I call it again, the program resumes processing a cursor where it left off. I have to sign off and back on in order to restart from the top. Why doesn’t the program start over from the beginning of the returned data set?

    –Lynne

    The behavior you’re witnessing comes from three contributing factors, Lynne. First, your program was compiled to close the cursor when the activation group is destroyed. Second, your program is running in the default activation group. Third, you are not checking the open of the cursor to determine whether it succeeds or fails.

    Fortunately, this is an easy problem to fix.

    Let’s look at a program like the ones Lynne is talking about.

    Fqsysprt   o    f  132        printer
    F
    D zInput          ds                  inz
    D  zCustNumber                   6p 0
    D  zLastName                     8a
    D  zInitials                     3a
    D  zBalanceDue                   7p 2
    D  zCreditDue                    7p 2
    
    D  Ratio          s              3p 0
    
     /free
         exec sql
            declare c1 cursor for
                  SELECT cusnum, lstnam, init, baldue, cdtdue
                    FROM qiws/qcustcdt
                   ORDER BY 1;
    
         exec sql
            open c1;
    
         dow '1';
            exec sql
               fetch c1 into :zInput;
            if sqlstt >= '02000';
               leave;
            endif;
            eval(h) Ratio = zCreditDue / zBalanceDue * 100;
            except pline;
         enddo;
    
         *inlr = *on;
     /end-free
    
    Oqsysprt   e            pline       1
    O                       zCustNumber
    O                       zLastName        +   1
    O                       zInitials        +   1
    O                       zCreditDue    j  +   1
    O                       zBalanceDue   j  +   1
    O                       Ratio         j  +   1
    

    Notice the eval within the do-while loop. I’ve included this line of code in order to make the program cancel.

    Here’s the result set from running the query. This is the data that the program reads as input.

    CUSNUM   LSTNAM    INIT    BALDUE     CDTDUE
    192837   Lee       F L     489.50        .50
    389572   Stevens   K L      58.75       1.50
    392859   Vine      S S     439.00        .00
    397267   Tyron     W E        .00        .00
    475938   Doe       J W     250.00     100.00
    583990   Abraham   M T     500.00        .00
    593029   Williams  E D      25.00        .00
    693829   Thomas    A N        .00        .00
    839283   Jones     B D     100.00        .00
    846283   Alison    J S      10.00        .00
    938472   Henning   G K      37.00        .00
    938485   Johnson   J A   3,987.50      33.50
    

    The first time I call the program, I get the following output, followed by escape message MCH1211, which tells me that the program attempted to divide by zero.

    192837 Lee      F L       .50     489.50    0
    389572 Stevens  K L      1.50      58.75    3
    392859 Vine     S S       .00     439.00    0
    

    The second time I call the program, I get the following output before I get another MCH1211.

    475938 Doe      J W    100.00     250.00   40
    583990 Abraham  M T       .00     500.00    0
    593029 Williams E D       .00      25.00    0
    

    Notice that the second run of the program did not begin with the first record of the result set.

    The program was compiled to close the cursor when the activation group is destroyed. The system destroys named activation groups when the last program in the activation group ends. However, the default activation group, which is intended for use only with OPM programs, is destroyed only when the job ends. Therefore, the cursor remained open between invocations.

    If I had checked the SQL status variable, SQLSTT, after the open during the first call, I would have found that SQLSTT contained a value of five zeros, meaning that the open succeeded. But SQLSTT would have had a value of 24502 after the open in the second call, meaning that the cursor was already open in the activation group.

    So, how do I fix the problem?

    The simplest fix is to change the program so that it closes the cursor when the module ends. I do that by adding the following code to the top of the calcs in the RPG program.

    exec sql                         
       set option closqlcsr=*endmod; 
    

    You should also consider running the program in a named activation group. You can easily do this by adding an H spec to the program.

     H dftactgrp(*no) actgrp(??????)
    

    I’ll leave it to you to think about what activation group name you should replace the questions marks with. If an RPG program is a standalone application, you can use *NEW.

    Also, I encourage you to check the status of the open and to end the program gracefully if it has a value greater than or equal to 02000.

    exec sql
       open c1;
    if sqlstt >= '02000';
       // do something to handle the failed open
    

    For one error-handling approach that works well, see the article Error Checking and Embedded SQL.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    ARCAD Software

    Embrace VS Code for IBM i Development

    The IBM i development landscape is evolving with modern tools that enhance efficiency and collaboration. Ready to make the move to VS Code for IBM i?

    Join us for this webinar where we’ll showcase how VS Code can serve as a powerful editor for native IBM i code and explore the essential extensions that make it possible.

    In this session, you’ll discover:

    • How ARCAD’s integration with VS Code provides deep metadata insights, allowing developers to assess the impact of their changes upfront.
    • The role of Git in enabling seamless collaboration between developers using tools like SEU, RDi, and VS Code.
    • Powerful extensions for code quality, security, impact analysis, smart build, and automated RPG conversion to Free Form.
    • How non-IBM i developers can now contribute to IBM i projects without prior knowledge of its specifics, while ensuring full control over their changes.

    The future of IBM i development is here. Let ARCAD be your guide!

    Watch Now

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Manta Technologies:  Fall Sale on i training courses! Order by October 15 and SAVE 25%
    CCSS:  Webinar, Sept. 23 - Rapidly Web Enable your IBM i 5250 Applications in a Cost Conscious Market
    COMMON:  Celebrate our 50th anniversary at annual conference, May 2 - 6, 2010, in Orlando

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    PowerTech to Resell Pat Townsend’s Encryption Offering IBM to Mothball a Whole Bunch of Stuff with Power7

    Leave a Reply Cancel reply

Volume 9, Number 29 -- September 23, 2009
THIS ISSUE SPONSORED BY:

WorksRight Software
East Coast Computer
Halcyon Software

Table of Contents

  • WDSC vs. RDi
  • Let’s Start Over from the Beginning
  • The Cost of Not Backing Up

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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