August 24, 2005 Joel Cochran
Re: Dynamic SQL - Issue with Execute Immediate ok my dumb idea for the day.forget SQL on this one (unless someone else has a way). Create a DS for each different 'record type' in the file, read it in as a flat file and move the data into the appropriate DS for each 'record type' and parse it that way. Seems much simpler and I'm guessing that it would run pretty darn fast.
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.
Embedded SQL Example in AS400 using EXECUTE IMMEDIATE is given below- EVAL SQLSTMT1= 'SELECT COUNT(.) ARR(7) FROM ARR(1)'; C/EXEC SQL; C+ EXECUTE IMMEDIATE:SQLSTMT1; C/END-EXEC. Www.go4as400.com - A programming guide to learn AS400. Toggle navigation AS400 Tutorial AS400 chapters AS400 FAQ AS400 E-BOOKS AS400 COURSE. #ibmi #rpg #as400 SQLRPGLE Execute Immediate Fixed and Free format coding style Embedded SQL Statement IBM i Classes.
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:
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:
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:
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:
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:
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
As400 Sql Digits
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:
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 (?):
Now to issue this statement with our desired variable data, we introduce a new clause in the execute statement: using.
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:
A few notes here:
1. The order of the declare and prepare statements relative to each other do not really matter.
As400 Sql Update
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.
As400 Sql Date Function
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
As400 Sql Update Examples
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
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