• 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
    TL Ashford

    TLAForms – Moving Forms Forward

    Transform IBM i spool files into vibrant, full-color documents with TLAForms. Create great looking forms using fonts, images, barcodes, and data from spool files and IBM i files via SQL. Print to any printer (no IPDS needed) or generate PDFs for automatic emailing or archiving. Prepare for the future of document creation.

    Learn more and download a free trial at tlashford.com or contact us at 800.541.4893

    TLAForms Features:

    • Add spool file data to forms with expanded font selection including MICR for check printing
    • Add bar codes to forms including 2-dimensional symbols – PDF417, QR Code, DataMatrix, etc
    • Import Color Graphics
    • Apply Color Overlays
    • ACTIONS conditions data and objects on the form and how the form is generated
    • Use SQL to integrate data from IBM i files
    • Create Tables
    • Generate forms with No Programming!
    • Print to any printer on IBM i, server or locally-connected printers
    • Generate forms as PDF and automatically email and archive.
    • Full Video Training library to become an expert at your own pace!

    Request a free, 30-day trail at tlashford.com/demo

    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

  • IBM i Delivers Sizable Benefits, Forrester Consulting Reports
  • SBOMs Will Come to IBM i, Eventually
  • IBM i Backup Provider Storagepipe Snapped Up By Thrive
  • Four Hundred Monitor, June 7
  • IBM i PTF Guide, Volume 25, Number 23
  • Power10 Boosts NVM-Express Flash Performance
  • Fortra Completes Postmortem Of GoAnywhere Vulnerability
  • Guru: Binding Directory Entries
  • How Does Your Infrastructure Spending Stack Up To The World?
  • IBM i PTF Guide, Volume 25, Number 22

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 © 2023 IT Jungle