• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Edit Result Sets in Run SQL Scripts

    October 7, 2019 Paul Tuohy

    Before getting into the detail in this article, I want it to be clear that I do NOT (in any way) advocate the direct editing of data in a production database. But when it comes to a test database, then the ability to directly edit data is invaluable.

    Back in the days of System i Navigator, you could right click on a table, select the Edit option and a window would open containing the contents of the table. You could directly edit the contents of any cell. Rows could be inserted or deleted using the Rows option on the menu.

    The same Edit context menu option is not available when you right click on a table in Access Client Solutions (ACS).

    But there is an alternative, for editing data, in Run SQL Scripts. This alternative offers one major advantage, over the System i Developer option, and one minor disadvantage.

    In order to be able to edit the data in a result set, you enter an SQL SELECT statement and at the end of the statement, you add FOR UPDATE. For example:

    select * from employee for update;
    

    The result set will, as usual, be displayed in the panel at the bottom of the Run SQL Scripts window or is a separate window, depending on your settings.

    Now, all you have to do is double click on a cell in the result set and the contents of the cell become editable.

    The major advantage you get with this approach is that the result set, for editing, is based on the select statement. This means you can edit a subset of any data in a table (columns and/or rows) as opposed to editing the full table.

    select empno, firstnme, lastname
    from employee
    where workdept = 'D11'
    for update;
    

    The minor disadvantage is that, if you want to insert or delete a row, you must use an SQL INSERT or DELETE statement.

    All in all, I think this edit option is a step forward.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Access Client Solutions, ACS, FHG, Four Hundred Guru, IBM i, Run SQL Scripts, SQL, System i Navigator

    Sponsored by
    ARCAD Software

    WEBINAR
    Unit Test Automation: Secure Application Quality on IBM i
    April 15, 12 p.m. EDT, 5 p.m. BST

    Unit testing is arguably the most effective element of your testing strategy, driving the quality of your application as it is being developed.  Yet how can unit testing benefit legacy applications on IBM i – and especially those containing ‘monolithic’ sections of code?

    In this Webinar we will demonstrate how specialized unit test automation can safeguard application quality and generate reusable test assets for both modular and monolithic code.

    You will learn how to automate the IBM i unit testing process within a standard DevOps stack., including RDi, JUnit and Jenkins.

    Using ARCAD iUnit, you’ll see:

    • Automated test creation for modules, programs and service programs
    • Code coverage results
    • Mock capability – simulates key components like data, files programs
    • Test execution history
    • Version control with Git

    Register today and secure the quality of your IBM i application.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Automation For The Masses – Here Come The Bots Monoliths, Microservices, And IBM i Modernization: Part 1

    6 thoughts on “Guru: Edit Result Sets in Run SQL Scripts”

    • John Rusling says:
      October 7, 2019 at 9:39 am

      Hi Paul, there must be a certain version of ACS Run sql scripts this applies to as when i double-click i don’t get the edit option?

      Reply
      • Peter says:
        May 20, 2020 at 11:30 am

        Hi John, I have same issue. Is it version related ?
        I am at :
        Version: 1.1.6.1
        Build id: 6431
        October 19, 2016 9:29:49 AM EDT

        Reply
    • David Larsen says:
      October 7, 2019 at 12:46 pm

      Paul, Thank you for that tip. Even though it seems so simple, it is immeasurably valuable and another tool that saves time.

      Reply
    • Bill Pahl says:
      October 8, 2019 at 5:18 pm

      This is great! I get tired of typing the whole thing again with an UPDATE table SET… construct.

      Reply
    • Nigel Tufnel says:
      March 27, 2020 at 9:17 am

      Awesome tidbit! Does anyone know of a way to change “run SQL scripts” to dark mode?

      Reply
    • Laura says:
      July 6, 2020 at 3:41 pm

      I was able to do the update but now I can’t. What is wrong of my setup?

      Reply

    Leave a Reply Cancel reply

TFH Volume: 29 Issue: 57

This Issue Sponsored By

  • ARCAD Software
  • Profound Logic Software
  • WorksRight Software
  • Computer Keyes
  • Manta Technologies

Table of Contents

  • Sometimes Even DIYers Need A Little Help
  • Monoliths, Microservices, And IBM i Modernization: Part 1
  • Guru: Edit Result Sets in Run SQL Scripts
  • Automation For The Masses – Here Come The Bots
  • Power7 And Power7+ Will Truly Be Dead At The End Of 2020

Content archive

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

Recent Posts

  • Query Supervisor Gives Database Engineers New Power
  • IBM Unveils New and Improved IBM i Services
  • 3 Takeaways from the 2021 PowerTech Security Report
  • Four Hundred Monitor, April 14
  • IBM i PTF Guide, Volume 23, Number 15
  • Big Blue Unveils Spring 2021 IBM i Technology Refreshes
  • Thoroughly Modern: Innovative And Realistic Approaches To IBM i Modernization
  • Guru: Web Services, DATA-INTO and DATA-GEN, Part 2
  • Back To The Future With A New IBM i Logo
  • IBM i PTF Guide, Volume 23, Number 14

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.