• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Parameterized RUNSQLSTM

    June 21, 2002 Timothy Prickett Morgan

    Hey, Ted:

    Can I pass parameters to an SQL statement in a source member before executing the Run SQL Statement (RUNSQLSTM) command? I’m sure there must be a simple way, but I’ve never seen any examples of this.

    For example, today I might like to have the SQL statement select company 005. Tomorrow I might want it to select company 007.

    — Darrell

    What you want is the ability to put placeholders in the SQL source and specify replacement arguments on the RUNSQLSTM command, as you can do with the Start Query Management Query (STRQMQRY) command.

    It may be worth the effort to write an RPG, COBOL, or C program that will build or update a source member for you.

    Another option is to use Qshell’s stream editor, sed. Most source-code editors I’ve used are for interactive editing, but batch editors do exist, although they’re rare. Sed is such a batch editor.

    As an exercise to prove the concept, I created a source member called UPDTCUSPAT (PAT to indicate pattern) with an SQL command in it. I put CCC as a placeholder for the company number:

    update customer         
       set creditlim = 0 
       where company = CCC  
    

    I copied source member UPDTCUSPAT to source member UPDTCUS in order to create a source member to hold the SQL statement that will be executed.

    I developed the necessary sed command in an interactive Qshell session:

    sed "s%CCC%002%g" 
       /qsys.lib/tholts.lib/sqlsrc.file/updtcuspat.mbr 
       >/qsys.lib/tholts.lib/sqlsrc.file/updtcus.mbr  
    

    This command tells Qshell to read updtcuspat, replacing every occurrence of CCC with 002, and to put the output into updtcus. That is, the stream editor doesn’t change the updtcuspat source member, but makes an updated copy of it.

    Then I wrote a CL program that takes a company number as a parameter, builds the sed command, executes the sed command to build UPDTCUS, and finally runs RUNSQLSTM:

    pgm &company                                               
                                                                   
    dcl &lib        *char  10 value('tholts')                  
    dcl &file       *char  10 value('sqlsrc')                  
    dcl &patternmbr *char  10 value('updtcuspat')              
    dcl &sqlcmdmbr  *char  10 value('updtcus')                 
    dcl &company    *char   3                                  
    dcl &shellcmd   *char 256                                  
                                                                   
    chgvar var(&shellcmd) +                                    
      value('sed "s%CCC%' *cat &company *cat '%g"' *bcat +
               '/qsys.lib/' *cat +                           
               &lib *tcat '.lib/' *cat +                     
               &file *tcat '.file/' *cat +                   
               &patternmbr *tcat '.mbr' *bcat +              
               '>/qsys.lib/' *cat +                          
               &lib *tcat '.lib/' *cat +                     
               &file *tcat '.file/' *cat +                   
               &sqlcmdmbr *tcat '.mbr')                      
    QSH       CMD(&SHELLCMD)                           
    RUNSQLSTM  SRCFILE(&LIB/&FILE) SRCMBR(&SQLCMDMBR) +
                              COMMIT(*NONE)                       
    

    It works, but I haven’t tried it in a production environment.

    — Ted

    Sponsored By
    TRAMENCO

    Introducing a New Source for Training and Mentoring. Follow this link to a vital new source for how-to technical information: www.tramenco.com.

    Unlike companies that offer training as an afterthought, The Training and Mentoring Company (Tramenco) is dedicated to just one thing: Advancing your career by giving you the skills you need to solve real-world business problems.

    You get the best information from the world’s leading experts–Howard Arner, Kelly Conklin, Don Denoncourt, Susan Gantner, Skip Marchesani, Glen Marchesani, Shannon O’Donnell, Craig Pelke, and Richard Shaler.

    Choose from a menu of training options to fit your needs: onsite seminars, public seminars, mentoring, consulting, books, CBTs, and Web-based training.

    And make plans to attend the 2002 iSeries Connection Conference, the multi-day, multi-track conference that was the only sold-out iSeries training event this year, co-sponsored by the Education Connection and Tramenco.

    For more information about Tramenco’s career enhancing opportunities, call (800) 421-8031 or go to www.tramenco.com.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: mgo_rc, Volume 2, Number 48 -- June 21, 2002

    Sponsored by
    OCEAN User Group

    OCEAN TechCon25 Online

    It’s an Exciting Time for IBM i !

    July 16 & 17, 2025 – ONLINE

    Two virtual days of learning, presented by an outstanding group of IBM’ers and IBM Champions, featuring leading-edge topics.

    FREE for OCEAN members!

    Register NOW!

    Annual (12-month) Individual OCEAN Memberships are $80 and a Corporate Membership is $250. A Corporate Membership would allow your entire company to have full access to the OCEAN website & video library and to attend OCEAN events at member rates. Act now because rates are increasing on August 1, 2025.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    LAYOUT: An Improved DSPFFD Searching Message Text

    Leave a Reply Cancel reply

MGO Volume: 2 Issue: 48

This Issue Sponsored By

    Table of Contents

    • RPG II
    • Determining Whether a User Is Already Signed On, Take Two
    • Parameterized RUNSQLSTM

    Content archive

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

    Recent Posts

    • With Power11, Power Systems “Go To Eleven”
    • With Subscription Price, IBM i P20 And P30 Tiers Get Bigger Bundles
    • Izzi Buys CNX, Eyes Valence Port To System Z
    • IBM i Shops “Attacking” Security Concerns, Study Shows
    • IBM i PTF Guide, Volume 27, Number 26
    • 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

    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