• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Read Once, Update Many

    November 30, 2011 Ted Holt

    Using SQL rather than native I/O to query and manipulate the database is more than replacing one syntactical regulation with another. It requires a different way of thinking. Nevertheless, corresponding features do differ, and today I show one way that the SQL update differs from a native update.

    Consider the following scenario from a project in which I was recently involved.

    I needed to read a database table (physical file) from top to bottom. After I retrieve each row (record), I called one or both of two high-level language programs, using parameters to pass data values from the table and to receive other data values in return. Depending on the values in the returned data, I had to update certain columns (fields). In other words, many different updates had to take place.

    To give you some idea of what I’m talking about, here is a much simplified RPG program that uses native I/O.

    FSomeFile  uf   e             disk    prefix(A_) usropn
    
    D CurrentTime     s               z   inz(*sys)
    D Status          s              1a
    
     /free
         *inlr = *on;
    
         open SomeFile;
    
         dow '1';
            read SomeRec;
            if %eof();
               leave;
            endif;
         //// do something here that sets Status
         //// and may or may not affect Balance
            A_RunTime = CurrentTime;
            if Status = '1';
               update SomeRec %fields(A_Balance: A_RunTime);
            else;
               update SomeRec %fields(A_RunTime);
            endif;
         enddo;
    
         close *all;
         return;
    

    In this simple program, there are two updates. The system will only carry out one of them for each record in the file, depending on the value of the status field. Notice that the runtime field is updated in either case.

    In my more complex example, I needed to carry out multiple updates. To return to my simplified example, I needed to do this:

            if Status = '1';
               update SomeRec %fields(A_Balance);
            endif;
            update SomeRec %fields(A_RunTime);
    

    But native I/O won’t allow more than one update for a single read. To make this code run, I would have to add an input operation before the second update.

    Updating a single row is one way that SQL differs from native I/O. When you update a table through a cursor, you can update as many times as you like, like this:

    D CurrentTime     s               z   inz(*sys)
    
    D WorkRec         ds                  qualified inz
    D   CustomerType                 1a
    D   TAmount                      7p 2
    D   Bal                          7p 2
    
    D Status          s              1a
    D Balance         s              7p 2
    
     /free
         *inlr = *on;
    
         exec sql
            declare c1 cursor for
               select c.custype, c.transamt, c.balance
                 from SomeFile as c
                  for update of balance, runtime;
    
         exec sql
            open c1;
    
         dow '1';
            exec sql
               fetch c1 into :WorkRec;
            if SqlState >= '02000';
               leave;
            endif;
    
         //// do whatever to change status and balance
    
            if Status = '1';
               exec sql
                  update SomeFile as c
                     set c.Balance = :Balance
                   where current of c1;
            endif;
            exec sql
               update SomeFile as c
                  set c.RunTime = :CurrentTime
                where current of c1;
         enddo;
    
         exec sql
            close c1;
         return;
    

    Notice the two back-to-back UPDATE commands. The fact that SQL allowed two updates for one fetch helped me in two ways. First, it eliminated the need to write a lot of messy conditional logic. Second, it permitted me to write a few, short UPDATE commands, rather than a lot of long ones.



                         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
    Krengeltech

    When it comes to consuming web APIs on your IBM i, your options often boil down to one of two things:

    First, you end up having to rely on a variety of open source and non-RPG solutions. This adds developer complexity, taking away time that could have been better spent invested in other projects. Of course, open source software is free, but generally comes at the cost of no professional support, which adds an element of risk in your production environment. RXS is completely professionally supported, and is complemented by a staff of trained IBM i developers who can address your nuanced development challenges, head on.

    Second, if you choose not to pursue an open-source solution, you’re often left having to shake up your current program architecture with proprietary software, external dependencies, and partial RPG implementations – many of which are sub-par compared to RPG-XML Suite’s wide range of features. RXS aims to simplify the efforts of developers with tools like code generators, useful commands, and subprocedures written in 100% RPG – no Java. Because they are entirely RPG, the RXS subprocedures are easy to add to new or existing ILE programs and architecture, helping to cut your development time. RPG-XML Suite offers powerful capabilities in an accessible, easy-to-implement format.

    With RPG-XML Suite, you can accomplish a variety of complex tasks, such as:

    • Calling REST and SOAP web services from your IBM i
    • Offering APIs from your IBM i
    • Creating JSON & XML
    • Parsing JSON & XML
    • Text manipulation, Base64 encoding/decoding, CCSID handling, hashing and encryption functions, and more.

    To try RXS for yourself, we recommend a free proof of concept, which not only gives you access to all of RPG-XML Suite’s subprocedures and utilities but also includes a tailor-made software demonstration that can be used as a starting point for your future API implementations.

    For a free proof of concept, contact us at sales@krengeltech.com, or visit our website for more information.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Micro Focus:  RUMBA for iSeries, the world's most used terminal emulation software
    The 400 School:  Fall Training Sale – Discounts up to 40%! RPG IV COBOL CL Admin Security
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Starsoft Taps Veryant to Port IBM i COBOL to Open Systems TSM and IBM i: Not What It Could Be

    Leave a Reply Cancel reply

Volume 11, Number 36 -- November 30, 2011
THIS ISSUE SPONSORED BY:

Bytware
ProData Computer Services
WorksRight Software

Table of Contents

  • Read Once, Update Many
  • Another IFS Interface
  • Why Are My Batch Jobs Running at Priority 20?

Content archive

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

Recent Posts

  • IBM i 7.3 TR12: The Non-TR Tech Refresh
  • IBM i Integration Elevates Operational Query and Analytics
  • Simplified IBM i Stack Bundling Ahead Of Subscription Pricing
  • More Price Hikes From IBM, Now For High End Storage
  • Big Blue Readies Power10 And IBM i 7.5 Training for Partners
  • IBM Delivers More Out-of-the-Box Security with IBM i 7.5
  • Groundhog Day For Malware
  • IBM i Community Reacts to IBM i 7.5
  • Four Hundred Monitor, May 11
  • IBM i PTF Guide, Volume 24, Number 19

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.