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

    Raz-Lee Security is the leader in security and compliance solutions that guard business-critical information on IBM i servers. We are committed to providing the best and most comprehensive solutions for compliance, auditing, and protection from threats and ransomware. We have developed cutting-edge solutions that have revolutionized analysis and fortification of IBM i servers.

    Raz-Lee’s flagship iSecurity suite of products is comprised of solutions that help your company safeguard and monitor valuable information assets against intrusions. Our state-of-the-art products protect your files and databases from both theft and extortion attacks. Our technology provides visibility into how users access data and applications, and uses sophisticated user tracking and classification to detect and block cyberattacks, unauthorized users and malicious insiders.

    With over 35 years of exclusive IBM i security focus, Raz-Lee has achieved outstanding development capabilities and expertise. We work hard to help your company achieve the highest security and regulatory compliance.

    Key Products:

    • AUDIT
    • FIREWALL
    • ANTIVIRUS
    • ANTI-RANSOMWARE
    • MULTI-FACTOR AUTHENTICATION
    • AP-JOURNAL
    • DB-GATE
    • FILESCOPE
    • COMPLIANCE MANAGER
    • FIELD ENCRYPTION

    Learn about iSecurity Products at https://www.razlee.com/isecurity-products/

    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

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

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.