Kbase 21032: How to Trap for SQL-89 FETCH and INSERT Errors.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
10/16/2008 |
|
Status: Unverified
GOAL:
How to check for failures of SQL-89 statements, FETCH and INSERT.
FACT(s) (Environment):
Progress 9.x
Progress 8.x
FIX:
Unlike 4GL, which offers the NO-ERROR option to suppress errors that occur as a result of executing the associated statement, SQL-89 provides no built-in mechanism for error suppression and handling when SQL-89 statements, such as FETCH and INSERT, fail.
The developer cannot check the ERROR-STATUS system handle for information on any errors that might have occurred during the execution of SQL statement(s) because the ERROR-STATUS system handle simply does not trap SQL-89 generated errors.
This sample code takes advantage of the ENDKEY condition, which the SQL-89 FETCH statement raises when it fails. Likewise, the SQL-89 INSERT statement raises the ERROR condition, which can be checked.
In the first sample, we make sure that the INSERT statement will fail by trying to insert non existing records, namely those that belong to the non-existent region "AFRICA".
In the second sample we force the FETCH statement to fail by trying to retrieve a non-existent record, namely the customer record that has a customer number of < -5.
/* ************************************************************/
/* How to trap the error generated by the INSERT statement */
/* ************************************************************/
DEFINE VARIABLE lInsertError AS LOGICAL NO-UNDO.
DO ON ERROR UNDO, LEAVE:
INSERT INTO Customer (Sales-Rep)
SELECT Sales-Rep FROM Salesrep WHERE Region = "AFRICA".
lInsertError = TRUE.
END.
MESSAGE lInsertError VIEW-AS ALERT-BOX.
/* ************************************************************/
/* How to trap the error generated by the FETCH statement */
/* ************************************************************/
DEFINE VARIABLE cName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iNumber AS INTEGER NO-UNDO.
DEFINE VARIABLE lFetchError AS LOGICAL NO-UNDO.
DECLARE cCursor CURSOR FOR SELECT Name, Cust-Num FROM Customer
WHERE Cust-Num < -5.
OPEN cCursor.
DO ON ENDKEY UNDO, LEAVE:
lFetchError = TRUE.
FETCH cCursor INTO cName, iNumber.
lFetchError = FALSE.
END.
CLOSE cCursor.
MESSAGE lFetchError VIEW-AS ALERT-BOX.