Using Variables in SQL
This chapter discusses how to:
Use variables in SQL.
Use dynamic SQL.
Use SQL error checking.
Use SQL and substitution variables.
Using Variables in SQL
SQL supports the use of variables. A SQL statement containing variables is considered static. When SQR runs this statement several times, it runs the same statement, even if the values of the variables change. Because SQL allows variables only in places where literals are allowed (such as in a WHERE clause or INSERT statement), the database can parse the statement before the values for the variables are given.
The ex16a.sqr sample program selects customers from a state that the user specifies:
Program ex16a.sqr begin-program do list_customers_for_state end-program begin-procedure list_customers_for_state input $state maxlen=2 type=char 'Enter state abbreviation' let $state = upper($state) begin-select name (,1) position (+1) from customers where state = $state end-select end-procedure ! list_customers_for_state
Note the use of the $state variable in the select paragraph. When you use a variable in a SQL statement in SQR for PeopleSoft, the SQL statement that is sent to the database contains that variable. SQR binds the variable before the SQL is run. In many cases, the database needs to parse the SQL statement only once. The only item that changes between runs of the select paragraph is the value of the variable. This is the most common example of varying a select paragraph.
In the sample program, the INPUT command prompts the user to enter the value of state. The MAXLEN and TYPE arguments verify the input, ensuring that the user enters a string of no more than two characters. If the entry is incorrect, INPUT reprompts.
The sample program converts the contents of the $state variable to uppercase, which enables the user to enter the state without worrying about the case. In the example, state is uppercase in the database. The sample program shows the LET command that is used with the SQR upper function.
You can let the SQL perform the conversion to uppercase by using where state = upper($state) if you are using an Oracle or Sybase database or by using where state = ucase($state) if you are using another database. However, SQR enables you to write database-independent code by moving the use of such SQL extensions to the SQR code.
When you run this program, you must specify one of the states that is included in the sample data for the program to return any records. At the prompt, enter IN, MI, NH, NJ, NM, NY, or OH. If you enter NY (the state where most of the customers in the sample data reside), SQR generates the following output:
Output for program ex16a.sqr John Conway Eliot Richards Harriet Bailey Kate's Out of Date Dress Shop Corks and Bottles, Inc.
Using Dynamic SQL
You may find it too restrictive that you can use variables only where literals are allowed. In the following example, the ordering of the records changes based on the user’s selection. The program runs the select statement twice. The first time, the first column is called name and the second column is called city, and the program sorts the records by name with a secondary sort by city. The second time, the first column is the city and the second is name, and the program sorts by city with a secondary sort by name. This is the first select paragraph:
select name, city from customers order by name, city
This is the second select paragraph:
select city, name from customers order by city, name
These statements are different. SQR constructs the statement each time before running it. This technique is called dynamic SQL, and it is illustrated in the following sample program. To take full advantage of the error-handling procedure, run it with the -CB command-line flag.
Program ex16b.sqr begin-program let $col1 = 'name' let $col2 = 'city' let #pos = 32 do list_customers_for_state position (+1) let $col1 = 'city' let $col2 = 'name' let #pos = 18 do list_customers_for_state end-program begin-procedure give_warning display 'Database error occurred' display $sql-error end-procedure ! give_warning begin-procedure list_customers_for_state let $my_order = $col1 || ',' || $col2 begin-select on-error=give_warning [$col1] &column1=char (,1) [$col2] &column2=char (,#pos) position (+1) from customers order by [$my_order] end-select end-procedure ! list_customers_for_state
When you use variables in an SQL statement in SQR to replace literals and more, you make them dynamic variables by enclosing them in square brackets. For example, when you use the [$my_order] dynamic variable in the ORDER BY clause of the select paragraph, SQR places the text from the $my_order variable in that statement. Each time the statement is run, if the text changes, a new statement is compiled and run.
Note. The z/OS operating system does not support square brackets for dynamic variables. Use slashes (/) instead.
Other dynamic variables are [$col1] and [$col2]. They substitute the names of the columns in the select paragraph. The &column1 and &column2 variables are column variables.
You can use dynamic variables to produce reports like this one. The data in the first half of the report is sorted differently from the data in the second half. Also note the give_warning error-handling procedure, discussed next.
The following is the output for Program ex16b.sqr:
John Conway New York Clair Butterfield Teaneck Corks and Bottles, Inc. New York Eliot Richards Queens Gregory Stonehaven Everretsville Harold Alexander Fink Davenport Harriet Bailey Mamaroneck Harry's Landmark Diner Miningville Isaiah J Schwartz and Company Zanesville Jerry's Junkyard Specialties Frogline Joe Smith and Company Big Falls Kate's Out of Date Dress Shop New York Quentin Fields Cleveland Sam Johnson Bell Harbor Bell Harbor Sam Johnson Big Falls Joe Smith and Company Cleveland Quentin Fields Davenport Harold Alexander Fink Everretsville Gregory Stonehaven Frogline Jerry's Junkyard Specialties Mamaroneck Harriet Bailey Miningville Harry's Landmark Diner New York John Conway New York Corks and Bottles, Inc. New York Kate's Out of Date Dress Shop Queens Eliot Richards Teaneck Clair Butterfield Zanesville Isaiah J Schwartz and Company
Using SQL Error Checking
SQR for PeopleSoft checks and reports database errors for SQL statements. When an SQR program is compiled, SQR checks the syntax of the SELECT, UPDATE, INSERT, and DELETE SQL statements in the program. Any SQL syntax error is detected and reported at compile time, before the report is run.
When you use dynamic SQL, SQR cannot check the syntax until runtime. In that case, the content of the dynamic variable is used to construct the SQL statement, which can allow syntax errors to occur in runtime. Errors could occur if the dynamic variables that are selected or used in a WHERE or ORDER BY clause are incorrect.
SQR traps any runtime error, reports the error, and ends the program. To change this default behavior, use the ON-ERROR option of the BEGIN-SELECT or BEGIN-SQL paragraphs.
begin-select on-error=give_warning [$col1] &column1=char (,1) [$col2] &column2=char (,#pos) position (+1) from customers order by [$my_order] end-select
In this sample program, if a database error occurs, SQR invokes a procedure called give_warning instead of reporting the problem and ending. Write this procedure like this:
begin-procedure give_warning display 'Database error occurred' display $sql-error end-procedure ! give_warning
This procedure displays the error message but does not stop running the program. Instead, the program continues at the statement immediately following the SQL or SELECT paragraph. Note the use of the $sql-error variable, which is a special SQR-reserved variable. It contains the error message text from the database and is automatically set by SQR after a database error occurs.
SQR has a number of reserved, or predefined, variables. For example, the $sqr-program variable has the name of the program that is running. The$username variable has the user name that was used to sign in to the database. The #page-count variable has the page number for the current page.
Using SQL and Substitution Variables
SQR uses the value of a substitution variable to complete the select paragraph at compile time. Because the select paragraph is complete at compile time, SQR can check its syntax before running the program. From this point on, the value of {my_order} cannot change and the SQL statement is considered static.
In the following program, the ASK command in the SETUP section prompts the user at compile time. The value that the user enters is placed in a special kind of variable called a substitution variable. This variable can be used to substitute any command, argument, or part of a SQL statement at compile time. This example is less common, but it demonstrates the difference between compile-time and runtime substitutions.
Program ex16c.sqr begin-setup ask my_order 'Enter the column name to sort by (name or city)' end-setup begin-program do list_customers_for_state end-program begin-procedure give_warning display 'Database error occurred' display $sql-error end-procedure ! give_warning begin-procedure list_customers_for_state begin-select on-error=give_warning name (,1) city (,32) position (+1) from customers order by {my_order} end-select end-procedure ! list_customers_for_state
In this case, the ASK command prompts the user for the value of the {my_order} substitution variable, which is used to sort the output. If the argument is passed on the command line, no prompt appears. When you run this program, enter name, city, or both (in either order and separated by a comma). The program produces a report that is sorted accordingly.
You can use the ASK command only in the SETUP section. SQR processes ASK commands at compile time before running the program. Therefore, all ASK commands are run before any INPUT command.
INPUT is more flexible than ASK. You can use INPUT inside loops. You can validate the length and type of data input and reprompt if it is not valid. The sample program at the beginning of this chapter contains an example of reprompting .
ASK can be more powerful. Substitution variables that are set in an ASK command enable you to modify commands that are normally quite restrictive. The following code shows this technique:
begin-setup ask hlines 'Number of lines for heading' end-setup begin-program print 'Hello, World!!' (1,1) end-program begin-heading {hlines} print 'Report Title' () center end-heading
In this example, the {hlines} substitution variable defines the number of lines that the heading will occupy. The BEGIN-HEADING command normally expects a literal and does not allow a runtime variable. When a substitution variable is used with this command, its value is modified at compile time.
See Compiling Programs and Using SQR Execute.