And while my hopes of someday rescuing the Princess and destroying the Death Star erode a bit more each day, I can take a cue from a real-life Star Wars icon, George Lucas: This is my prequel. Working with Groups of Data. SQL is terrific at selecting and manipulating groups of data. Massaging Columns. SQL has a slew of columnar functions designed to tally, total, summarize, manipulate, and calculate columns of data. Many program features such as substringing, averaging, and math functions can be performed during the record selection phase.
|Published (Last):||17 September 2016|
|PDF File Size:||17.17 Mb|
|ePub File Size:||12.10 Mb|
|Price:||Free* [*Free Regsitration Required]|
In total fairness, I will also acknowledge that working with free-format certainly has some undesirable aspects. As demonstrated above, this can be quite messy. In fact, this is downright ugly. And it can easily get worse! Multiple cursors, additional select statements, updates, deletes, and inserts can all be interspersed with sections of free-format code.
Eventually you have as much time invested in compiler directives as you do in actual code. This approach can be difficult to read, which means it is difficult to manage. So how do we conquer the ugliness? And we already know that we cannot use embedded SQL statements inside free-format. Our only recourse then is to separate the embedded SQL from the free-format code.
Naturally, for such an operation, we are going to turn to our trusty tool: the subprocedure. Before we begin breaking our program up into smaller chunks, there are some pesky rules to go over.
Second, the use of local variables. Since we are moving these statements to subprocedures, we can take advantage of local variables, but with restrictions. Namely, if the variable name is also not a global variable name, an SQL statement should be able to use the local variable as a host variable. To complicate matters, this is not always true. The local name can be the same as a global name, provided that both variables are defined the same. Otherwise, SQL will get confused and not recognize the value of either variable.
This is only a problem if the local and global variables are defined differently. The solution is to ensure that your host variable names unique no matter what. For update and insert statements, I like to use an externally defined DS based on the same file. This way the variable definitions are guaranteed to be correct. As a result, no data conversion is ever required so I get a little performance boost.
You can populate the variables before calling the procedure or use local variables to populate the global host variables if you like. While it is a little extra work, being cognizant of this issue beforehand can save you time and aggravation.
Modularizing the Program Now that we know what the rules are, we can transfer the SQL statements out of our mainline and into subprocedures. The real gem here is in the main line. We certainly have a lot less jumping in and out of free-format, even in the subprocedures, since they are primarily SQL.
Obviously this is a very simple example, but let me point out a couple of things. At first glance, it appears that the declare and open procedures could be combined. While in this case that may be true, what if the cursor had a variable where clause? A Few More Tidbits For scrollable cursors, is to have a single fetch procedure instead of our unidirectional fetchNext that accepts a parameter for the fetch method and some branching logic.
Below is a simple example, you could easily add the rest of the scroll types i. At least we are limiting it to this subprocedure and getting it out of the mainline. And of course, you could have separate subprocedures for each of these instead of one subprocedure. Another thing you could do is to make your fetch routine s return a boolean value representing whether or not a record was found. Final Thoughts I do, as always, have a few items on my wish list.
The first is to find a solution to the problems with the local variables discussed above. Looping through a cursor is OK, but sometimes I want to know how many records the cursor has, or maybe dump the contents of a cursor into an array or even an outfile.
I can imagine a lot of similar tasks and uses. This is especially true when you have a lot of conditional updating, multiple cursors, and the like. 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.
Click here to contact Joel by e-mail. Share this:.
Embedding SQL in /free
Embedding SQL statements in ILE RPG applications that use SQL
Embedding SQL in RPG IV–Episode I