• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Embedding SQL in RPG IV–Episode II

    August 24, 2005 Joel Cochran

    In the first installment of this article, affectionately named “Episode I,” I presented the first half of an embedded-SQL primer. We discussed such heady topics as host variables and cursors, and it was a good start but far from complete. All of the examples in the first article were static examples, meaning that they had no true runtime dynamics. While the variable values could obviously change, the form and format of each SQL statement was constant. In Episode II, I am going to focus on one of the more powerful features of embedded SQL: dynamic SQL statements.

    Picking Up Where We Left Off

    To quote the old serial movies of the 1940s and 1950s: “When last we left our heroes . . .” we were working on looping through a Cursor, fetching records, and displaying their contents. Here is our last example:

    d myDS            ds
    d   id                          10a   varying
    d   name                        35a
    d   zip                          5s 0
    
    c/exec sql
    c+ declare mainCursor Cursor
    c+   for
    c+ select trim(char(id)), name, zip
    c+   from midserve/sqltest
    c/end-exec
    c/exec sql
    c+ open mainCursor
    c/end-exec
    c/exec sql
    c+ fetch next
    c+  from mainCursor
    c+  into :myDS
    c/end-exec
    
    c                   dow       SQLSTT = '00000'
    c     myDS          dsply
    c/exec sql
    c+ fetch next
    c+  from mainCursor
    c+  into :myDS
    c/end-exec
    c                   enddo
    
    c                   eval      *inlr = *on
    

    This is all well and good if we always want to loop through the entire file, because that is what this cursor will always do. We could make it a little more variable by adding a where clause based on a host variable:

    d searchZip       s                   like( zip )
    c/exec sql
    c+ declare mainCursor Cursor
    c+   for
    c+ select trim(char(id)), name, zip
    c+   from midserve/sqltest
    c+  where zip = :searchZip
    c/end-exec
    

    Now, this is dynamic in the sense that the value of searchZip may change, but the SQL statement itself never changes: it is completely invariable. So what if I wanted a similar search in the same program based on name, city, or state? I could declare three different cursors, but then I would need three sets of declares, opens, fetches, and closes, most likely all within some ugly branching logic. Fortunately, there is another way.

    Executing SQL from String Variables

    One thing that really makes SQL dynamic is its ability to execute an SQL statement stored in a string or character variable. From this point forward in the article we must distinguish from select statements and non-select statements, because the rules for each are slightly different. Let’s begin by discussing non-select statements.

    Imagine you want to update the file we’ve been using for our examples, but you only want to update fields that have changed. For this example, let’s assume the user may have changed the name, address, or city/state/zip. If you statically coded each possibility, your code may look something like this:

    c                   if        nameChg
    c/exec sql
    c+                  update midserve/sqltest
    c+                     set name = :name
    c+                     where id = :id
    c/end-exec
    c                   elseif    addrChg
    c/exec sql
    c+                  update midserve/sqltes
    c+                     set address = :address
    c+                     where id = :id
    c/end-exec
    c                   elseif    cityStZipChg
    c/exec sql
    c+                  update midserve/sqltest
    c+                     set city = :city,
    c+                         state = :state,
    c+                         zip = :zip
    c+                     where id = :id
    c/end-exec
    c                   endif
    

    And obviously, this doesn’t account for the fact that they may have changed all three categories. You could easily address that by making each elseif a stand-alone if block, but then you may update the same record three times. Hopefully no one reading this article would think that a wise idea. Being able to execute the update based on a string though gives us a lot more flexibility. Consider this as an alternative:

    d sqlString       s          32000a   varying inz
    
    c                   if        nameChg
    c                   eval      sqlString = 'name = ' + quote +
    c                             %trimr( name ) + quote
    c                   endif
    
    c                   if        addrChg
    c                   if        %len( sqlString ) > 0
    c                   eval      sqlString += ', '
    c                   endif
    c                   eval      sqlString += 'address = ' + quote +
    c                             %trimr( address ) + quote
    c                   endif
    
    c                   if        cityStZipChg
    c                   if        %len( sqlString ) > 0
    c                   eval      sqlString += ', '
    c                   endif
    c                   eval      sqlString += 'city = ' + quote +
    c                             %trimr( city ) + quote
    c                   eval      sqlString += ', state = ' + quote +
    c                             %trimr( state ) + quote
    c                   eval      sqlString += ', zip = ' + %char( zip )
    c                   endif
    
    c                   eval      sqlString =
    c                             'update midserve/sqltest set ' +
    c                             sqlString +
    c                             ' where id = ' + %char(id)
    c/exec sql
    c+                  execute immediate :sqlString
    c/end-exec
    

    Now I’ll be the first to admit that on the surface this may seem like more work, and up-front it is, but what it truly represents is runtime flexibility. This bit of code ensures that only the fields that have changed, and any combination of those fields, get updated. You can apply the same technique to inserts, deletes, create table/database/index/view, and the list goes on. In this manner, you can easily account for varying input sources and variable data types, a tool that is becoming increasingly valuable in this age of interconnectedness. We frequently have no choice or control over where data comes from. I spend a lot of time performing data conversions, and the variety of ways that users and other ISVs come up with to store name and address information alone is staggering. This kind of flexibility can be a real life saver.

    Moving on to using this technique with Select statements, one of my favorite uses of this technique is to control subfiles based on user selection. A full sample is available on my Web site under Code Samples: look at the SFL_TEMP.RPG source member. Here is the pertinent code:

         if change1 = true ;
           mainStatement = 'Select * from cam500ap where TPID >= ' +
                           %trim( %editc(CTL1ID:'Z') ) + ' order by TPID ' +
                           'for read only ' +
                           'optimize for ' + %char(sflSize) + ' rows' ;
         elseif change2 = true ;
           mainStatement = 'Select * from cam500ap where ' +
                           'SOCSEC1 = ' + %trim( %editc(CTL1ID:'Z') ) +
                           'or SOCSEC2 = ' + %trim( %editc(CTL1ID:'Z') ) +
                           ' order by TPID ' +
                           'for read only ' +
                           'optimize for ' + %char(sflSize) + ' rows' ;
         elseif CTL1NAME <> ' ' ;
           mainStatement = 'Select * from cam500ap where NAME1 >= ' +
                         singleQuote +
                           %trim( CTL1NAME ) +
                           singleQuote +
                           ' order by NAME1 ' +
                           'for read only ' +
                           'optimize for ' + %char(sflSize) + ' rows' ;
         else ;
           mainStatement = 'Select * from cam500ap order by NAME1 ' +
                           'for read only ' +
                           'optimize for ' + %char(sflSize) + ' rows' ;
         endif ;
    
    // And later...
     c/exec SQL
     c+ declare mainCursor Cursor
     c+   for mainSelect
     c/end-exec
     c/exec sql
     c+ prepare mainSelect
     c+    from :mainStatement
     c/end-exec
    

    Now the mainCursor is ready to be opened and processed. Working through this example, you’ll see that I’m altering both the where and the order by clauses based on the user’s input. This is a simple example, but effective. You could just as easily use a similar method to select different result columns, different joins, alternate libraries, etc. Being able to execute SQL from a string means that the options are virtually limitless. The last SQL statement issued in this example, the prepare statement, is the key to doing this for select statements, and opens other possibilities as well.

    Prepared Statements and Non-Selects

    The prepare statement makes your SQL reusable. In other words, if I have fifty places that can issue an update statement, I do not need fifty actual update statements. Instead, I can prepare the statement once, naming it whatever I like, and then issue updates by using another SQL statement, execute (earlier we used its brother execute immediate to issue a statement directly from a string). While this may seem like trivial replacement at first, it has its benefits, especially when combined with the second thing that prepare does for you. Prepare allows you to have parameter markers.

    I like to think of parameter markers as place holders for future data. Let’s continue with our update example. Say the format of the update never changed:

    c/exec sql
    c+                  update midserve/sqltest
    c+                     set name = :name
    c+                     where id = :id
    c/end-exec
    

    The only problem with this is that I always have to use the name and id host variables. But what if in one part of the program I wanted name and another part I wanted last_name? And what if there were a dozen other possibilities based on program flow? Sure, I could always update the name and id fields first, but what if I forget? What if I really needed that data somewhere else in the program and now I’ve overwritten it? This is a great situation for using prepare.

    First, create a string with the update SQL in it like before, but this time replace all the host variables with question marks (?):

    c                   eval      sqlString =
    c                             'update midserve/sqltest set ' +
    c                             'name = ? ' +
    c                             ' where id = ?'
    c/exec sql
    c+ prepare updateRecord
    c+    from :sqlString
    c/end-exec
    

    Now to issue this statement with our desired variable data, we introduce a new clause in the execute statement: using.

    c/exec sql
    c+ execute updateRecord
    c+    using :name_var,
    c+              :id_var
    c/end-exec
    

    Prepared Statements and Selects

    To round this discussion out, we return to select statements. We can use parameter markers like this in select statements by preparing the SQL we use in our declare statement. The only other change is that when we open the cursor, we have to include the appropriate using clauses like we just did above. Here is a complete example:

    d data            ds
    d name                          35a
    d address                       35a
    d city                          35a
    d state                          2a
    d zip             s              5s 0 inz( 24401 )
    
    d sqlString       s          32000a   varying
    d                                     inz( 'Select +
    d                                     id, name, address, city, state +
    d                                     from midserve/sqltest +
    d                                     where zip = ? ' )
    
    c/exec sql
    c+ declare mainCursor Cursor
    c+     for mainStatement
    c/end-exec
    c/exec sql
    c+ prepare mainStatement
    c+    from :sqlString
    c/end-exec
    c/exec sql
    c+ open mainCursor
    c+ using :zip
    c/end-exec
    c/exec sql
    c+                  fetch next
    c+                   from mainCursor
    c+                   into :data
    c/end-exec
    c                   dow       SQLSTT <> '02000'
    
      *   Do something here with the retrieved values
    
    c/exec sql
    c+                    fetch next
    c+                     from mainCursor
    c+                     into :data
    c/end-exec
    c                   enddo
    c/exec sql
    c+ close mainCursor
    c/end-exec
    c                   eval      *inlr = *on
    

    A few notes here:

    1. The order of the declare and prepare statements relative to each other do not really matter.

    2. Since this cursor has been declared, you can open and close it as often as you need to. And since it was created with prepare and parameter markers, each time you open it you can use different host variables and values.

    3. Don’t forget to close your cursor as soon as you are through with it. If you try to issue another open on a cursor that is still open from before, you’ll get a different SQLSTT and unpredictable results may occur.


    Final Thoughts

    As I mentioned in my last article, this is merely a primer, something to outline the basics. I hope I’ve covered enough bases in these articles to get you started down the road of embedding SQL in your RPG IV. And if you haven’t read it yet, the first article in this series is actually an excellent follow on to the one you just finished: Embedding SQL in /free.

    Speaking of /free, I do want to share one thought with all you readers out there: I wrote this article using fixed-format for all my examples. I thought that after three years of almost exclusive free-format it would be an interesting exercise. Let me just say that it was a lot more like exercise than I really expected, so I won’t be doing that again. If you haven’t tried free-format yet, give it a fair try, you won’t regret it.

    Until next time, Happy Coding!

    RELATED ARTICLES

    Embedding SQL in RPG IV–Episode I

    Embedding SQL in /free

    Joel Cochran is the director of research and development for a small software firm in Staunton, Virginia, and is the author and publisher of www.RPGNext.com and www.RPGBeans.com. You can reach Joel through our Contact page

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    DRV Technologies, Inc.

    Get More from Your IBM i

    Many users today struggle to get at the data they need on the IBM i. When users get reports, they look like they were formatted some time last century.

    Some organizations are still printing pre-printed forms and checks on impact printers.

    How often do operators log on to their system to look for messages they hope they don’t find?

    All of these scenarios can affect users’ perception of the IBM platform negatively, but there are simple solutions.

    DRV Technologies Inc. develops innovative solutions that help customers get more from their IBM i systems.

    Solutions include:

    • SpoolFlex spool conversion & distribution
    • FormFlex electronic forms
    • SecureChex MICR laser check printing
    • MessageFlex system monitoring

    FlexTools streamline resources, improve efficiency and enable pro-active system management.

    Better software, better service, DRV Tech.

    Learn how you can get more from your IBM i at www.drvtech.com

    Call 866 378-3366 for a Free Demonstration

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Questys Document Management System Gains iSeries File Support Trustgenix and IdentityForge Put iSeries, zSeries at Center of SSO

    Leave a Reply Cancel reply

Volume 5, Number 32 -- August 24, 2005
THIS ISSUE
SPONSORED BY:

WorksRight Software
COMMON
Bug Busters Software Engineering

Table of Contents

  • Embedding SQL in RPG IV–Episode II
  • A Speedier CHGPF
  • Admin Alert: Common Things to Do When Installing Third Party Products

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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