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.