DDL Procedure Example

DDL Procedure Example An Example of Creating a Procedure

If you have not read creating procedures you should do so now. At least read the first part of it.

We are going to assume that you are in DCL (command mode) and that you are in the Example Data Base. If you are actually going to do this exercise you should print out this help screen and have it along side of you while you are in DCL.

What you need to do to create a procedure is go to the Editor and key it in, then return to DCL and execute it to see how it runs.

From reading creating procedures you know that you can get to the Applica- tion Catalog, select the type of definition you wish to do (a procedure), click on NEW, assign your new procedure a name, and then click on EDIT to get to the blank canvas where you can key in the new procedure.

Lets name our new procedure MYPROC, and lets bypass all of the above by typing:
DEFINE P MYPROC

You get to the Editor in procedure definition mode and you are looking at a blank window whose title bar says MYPROC (PROCEDURE). If the window is not blank, that is, if you see some procedure code already there, then this is not a new procedure, someone else has already used the name MYPROC, and you should return to DCL and pick a different name to get started with. To return to DCL close this window by either clicking on the X in the upper right corner or typing Ctrl+W.

Enter the following commands as the first cut at our new procedure:

find a customers where name sv s
read a customers f_customers

This isolates all customers whose name begins with S and displays them one at a time using the default screen named F_CUSTOMERS (this screen already exists). Save this procedure definition and return to DCL in order to execute it.
To save: Click on theicon on the tool bar or type Ctrl+S.

To close: Click on thein the title bar of your definition window

or type Ctrl+W.

Now you are back in DCL. Execute the procedure by typing MYPROC (or whatever name you gave it). You see the first record. You can click on the VCR control

The VCR control:

First Last
PreviousNext

to see all of them. When you finish looking at them click on the X in the upper right corner of the READ screen to close the screen.

So a procedure is just a prestored sequence of commands, and once it exists you just use its name to invoke it. If we are satisfied that this is what we want, all that remains to integrate it into the application is to assign that procedure name to a Menu somewhere so that a user does not have to be in DCL to invoke it. But, we are not satisfied.

What is wrong with this procedure?

There are two things wrong with this procedure. First, it is not very useful, and second, it is not very friendly.

The useful part: It only finds S's. We can't write a different procedure for every letter or combination of letters that there is. We need to let the user tell us what the set selection criteria is, so that one procedure will work for any letter(s) that the user supplies.

The friendly part: What if there had been no names that started with S? Then the FIND would not have found anything, so the READ would have had nothing to read. The user would see the screen flash and then nothing else would happen. It would look like an error. So we need to check for nothing found and notify the user.

We need to go back to the definition and fix it. To do that you can pull down the WINDOWS Menu and click on the name MYPROC that you see there. Or you can type REDEF. Now make the procedure look like this:

prompt/u %xx Enter first few characters of Last Name
find a customers where name sv {%xx}
if %answer=0
message Nothing found for "{%xx}".
end
endif
read a customers f_customers

We use the PROMPT command to solicit the user to enter something. What- ever the user enters will go into the variable %xx. The notation {%xx} means "the value of %xx" so the value of that variable is the set selection criteria used for the FIND. The U option on the PROMPT command simply converts anything the user enters into upper case so that it will look good when substituted into the message.

The answer cell from FIND is checked to make sure something was found. If it is zero we display the message and quit the procedure instead of going to the READ. If the answer is non zero control goes directly to the command following the ENDIF, that is, to the READ.

Save this version of the procedure and try it out. This is a lot better, it works for whatever the user types in, and if it doesn't find anything you get the message. But still no cigar. It does not work well if the user sends in a blank or cancels the PROMPT. And this is one of the most important lessons you will learn: Whenever you put the ball into the users court you must code for the possibility that they either don't respond or that they respond incorrectly, that is, with something other than what you intend.

So lets go back for another pass at the procedure. This time make it look like:
LOOP:
prompt/u %xx Enter first few characters of Last Name
set/y %ans1=%answer
set/y %ans2=%answer2
if %ans2='CANCEL'
end
endif
if %ans1=0
goto loop
endif
find a customers where name sv "{%xx}"
if %answer=0
message Nothing found for "{%xx}". Try Again.
goto loop
endif
read a customers f_customers

This time we want to capture the two answers that come from PROMPT . The two SETs with the Y option do that. (You can read more about this in the topic "command answer cells ," especially the part about the need for the Y options.)

First we check to see if the user cancelled, and if so we exit the procedure. Then we check to see if anything at all was entered and if not we take the user back to the PROMPT. This was done by introducing a "label" into the procedure and using the GOTO command to send control back to that point in the procedure.

We put quotes around the {%xx} in the FIND command so that if a blank was entered the FIND command will interpret it correctly. Finally, we changed the flow when the message happens by telling the user to try again and sending them back to the PROMPT rather than just exiting. We thought that might be friendlier.

Now try it out. This version should be pretty bullet-proof. And the prize for reading this far is "trace." Just before you type in the procedure name in order to execute it, pull down the "Options" Menu and select TRACE. Now execute the procedure. You see that it displays all of the commands as it is executing them, and also you see all the variable substitutions that were made in the commands as they were executed. A handy checkout tool that can help you debug your procedures. Be sure and do not have TRACE on when you are running in production. To turn off the trace just select it again. It acts like a toggle.