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
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
