In this the SQL statement is hard coded in the program. If there is any error in host variable or in SQL statement selection fields or any other sql statement related error, then the compilation stops and SQL precompilation report is generated. We can check the converted source by seeing the spool file of the program. The converted source includes SQL communication area and many sql-call commands to open, process and close sql cursors.
|Genre:||Health and Food|
|Published (Last):||10 November 2013|
|PDF File Size:||10.19 Mb|
|ePub File Size:||12.86 Mb|
|Price:||Free* [*Free Regsitration Required]|
August 10, Joel Cochran. 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. SQL: Why and When. 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. Aggregate Data.
SQL lets you easily aggregate columns in like rows. For instance, if you wanted to find a list of all the different zip codes in a mailing address file and count how many addresses were in each zip code, SQL can easily accomplish this in a single statement. Combine this capability with 1 and 2 above and SQL absolutely shines.
The SQL engine will determine access paths at execution time. If possible, the access plan is then stored in the program object and reused on subsequent calls. As a general rule, you should not reference logical files on SQL statements.
Instead, reference the physical file name. If an appropriate access path does not exist, the engine must generate one on the fly, so building appropriate indexes can greatly benefit your SQL performance. There are many other capabilities of SQL; this list is hardly inclusive. I just wanted to lay out some basic ground rules. Pick the right tool for the right job. The database access method you choose is not an exclusive decision.
Getting Started. If you do not have the Development Kit installed, you cannot write programs that use SQL, so be sure you have this before you proceed. It is important to note that you do not need the development kit or any additional runtimes to execute programs written with embedded SQL. This can be very handy if you manage multiple machines but only have a single development environment.
While you can still use a tool like Operations Navigator to execute SQL on machines without Interactive SQL, neither one is a very good option for executing complex series of statements, especially if the function is a recurring job.
Personally, this was such as constant hassle for me that I changed my command default. Before too many of you more experienced SQL programmers out there send me a million e-mails, yes there are two other ways to handle the commitment control problem. This signifies that no commitment control should be used on this particular statement. The problem with this approach is the additional hassle of needing to remember it for every statement that updates the database.
It allows you to enforce certain program options in the code itself rather than on the compile command:. There are more options for set, and many people swear by it.
By changing the command default, I only have to remember it when I upgrade my OS level. Your original SQL statements will be commented and followed by the code inserted by the pre-compiler. Over the last several years the SQL pre-compiler team has done a lot of work to try to rectify this situation, and it is improving.
But there is some ugly. Should your compile fail at the pre-compiler level, the error listing you get back will be of very little assistance compared to the RPGIV error listings. The message are short and cryptic and offer nothing in the way of corrective suggestions.
This may take a little getting used to and can surely be frustrating when you are first starting out. Embedding Basics. Issuing an SQL statement within your source code has some rules as well. You can insert SQL like this anywhere in your code, including subroutines and subprocedures. Host Variables. Host variable is a term that you see frequently in embedded SQL discussions.
We can use these variables when we execute SQL as parameters or as return values. Host variables are always preceded in SQL by a semi-colon. The following example will add ten records to our table created above by incrementing a counter variable and using the counter for the ID field:. You can easily mix and match host variable and literals as well. It also demonstrates that, within the confines of the compiler directives, embedded SQL is basically free-format. These are merely stylistic demonstrations: You should experiment and find a style that is comfortable for you to code and easy for you to read.
I should also point out that the host variable names are the same as the field names in the file. This is simply a coincidence, or rather an intentional choice. Because of this difference, there is no conflict here in creating variables as the same name. There are some additional rules for host variable naming. I address them in more detail in the prior article, but to sum them up in one fell swoop: the names should be unique.
Personally, I make all my host variables global variables. I am also fond of using externally defined datastructures for my host variables to prevent the SQL Engine from performing unnecessary translations. To avoid this, I try to use host variables that are defined exactly the same as the database fields. These conventions have worked well for me and once instituted as a standard are easy to maintain.
Returning a Value into a Host Variable. In embedded SQL, you must specify the target s for the return value s to populate. As I implied before, you can also use a datastructure name as the return variable:. This is very handy, especially if you are retrieving large numbers of fields from a file. Handling Multiple Records.
Since SQL excels at processing groups of data, this is fairly typical. In order to process multiple rows, we need a facility for navigating the returned rows. The order of events for using a cursor is thus:. Declare Open Fetch Repeat as necessary Close. Declaring a cursor is fairly straightforward. Declaring a cursor for the select statement above would look like this:.
The differences are the addition of the declare and for clauses and the removal of the into clause. We still need an into clause, but it will be part of the fetch statement. Now that we have declared a cursor, we need to indicate to the program that we are ready to use it. This is important because cursors can be reusable more on this later.
To make the cursor ready for use, we must open it:. This will prepare the cursor and the data for retrieval. Now we must fetch the data into our host variable s. At this point we have some options. Below is a complete sample:. The limitations of this approach should be obvious.
It forces you to hard code a number of occurrences that is less than or equal to the number of rows returned. You can return fewer rows than the size of the MODS, but if you try to return more rows than the defined size of the MODS the compiler will throw an error. This hard coding may not be a problem depending on the needs of your program.
One good example would be a program that totals sales figures by month: since there can only be twelve calendar months, then fetching all the records into a MODS with 12 occurrences at one time makes perfect sense. Looping through a Cursor. For the majority of cases, however, you probably want to be able to loop through the cursor as if you were reading a file.
To do so, simply remove the for x rows clause. You can also make a cursor scrollable by adding the scroll clause to the declare statement:. If you do not include the scroll clause, the cursor can only be read forward, but a scrollable cursor has the additional capabilities of fetch first , fetch last , and fetch prior. There are additional fetch capabilities as well. Both of these fields return information to the program indicating the resulting state of the most recently executed SQL statement.
Final Thoughts. I would recommend some additional resources:. 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. You can reach Joel through our Contact page. Ashford's comprehensive library of Compliance formats is available to Barcode users. Sponsored By T.
January 31, at pm. Leave a Reply Cancel reply.
Subscribe to RSS
Embedding SQL in RPG IV–Episode I
Embedded SQL programming