Checking for Errors after Inline SQL or Stored Procedures

It is important to check the results after executing inline SQL or a stored procedure. This is different from checking DataWindow errors which is done in DBError (see sample). A traditional approach to checking non-DataWindow database results might look something like:

SELECT Cust_Name
INTO :ls_Name
FROM CUST
WHERE Cust_Id = as_CustId;

CHOOSE CASE SQLCA.SQLCode
CASE 0
// all OK

CASE 100
// not found

CASE ELSE
// serious error
... standard message ...
END CHOOSE

There are a few disadvantages with having developers key in this type of error checking each time:

* it is cumbersome for developers to code an error message for serious errors each time
* it doesn't allow for more specific messages (e.g. database connection lost, etc.) for certain types of serious errors
* it doesn't allow for special handling of certain conditions in future (e.g. you might decide it's best to halt the application after certain types of errors in future).

A preferable approach is to write a general purpose error checking routine on the transaction object (i.e. on n_tr in PFC). Sample code for such a function is shown next (it can be overloaded to default parms):

Function Name: f_CheckDBError

Purpose: This function checks the DB Error code and returns the error
parameters on this transaction object.
If an unexpected error occurs, it displays an explanatory error message.
The first parameter (ai_HandleCode)indicates which errors are "expected".

Parameters:
in - ai_HandleCode (optional): code indicating how to handle non-zero SQL codes:
0 means ANY non-zero value is considered an error (default value when parm not specified)
1 means zero and "not found" are not considered errors
Note: other codes with special handling could be added
out - al_SQLCode (optional): SQL code coming back from DB.
out - al_SQLDBCode (optional): SQL DB code coming back from DB.
out - as_SQLText (optional): SQL text coming back from DB.

Returns: integer:
1: no error found
0: errors found but these fit into predefined "acceptable" category, according to ai_HandleCode value.

-1: errors found (i.e. an error not anticipated by the caller).

integer li_Return
string as_MsgParms[]
string ls_MessageID

// get the error codes from the specified transaction object.
al_SqlCode = This.SQLCode
al_SqldbCode = This.SQLDBCode
as_SqlText = This.SQLErrText

// Perform processing based on error code found
CHOOSE CASE al_SQLCode
CASE 0 no error
li_Return = 1

CASE 100 // not found condition
CHOOSE CASE ai_HandleCode
CASE 0
// condition was not expected
li_Return = -1
MessageBox("Error","Unexpected not found condition encountered")
CASE 1
// condition was expected ... just indicate it occurred to caller
li_Return = 0
END CHOOSE

// Other type of error (other than OK or not found), so check the
// DB-specific SQLDBCode
CASE ELSE
CHOOSE CASE al_SQLDBCode
CASE 3113, 3114 // Oracle database connection lost
// give a specific error message
li_Return = -1
MessageBox("Error","Database connection was lost")

CASE ... other special conditions (e.g. 20000-level errors in Oracle)

CASE ELSE
li_Return = -1
MessageBox("Error","Serious database error encountered with SQL Code = " + &
string(al_SqlCode) + " SQL DB Code = " string(al_SqldbCode) + &
" and SQL Text = " as_SqlText)
END CHOOSE
END CHOOSE

Return li_Return

Sample use of this function is provided below. First, it checks the results of a stored procedure:

p_GetCustDetails(al_CustId, as_CustName, as_CustGroup) // stored procedure call

// any non-zero code is a serious error
IF SQLCA.f_CheckDBError() = 1 THEN // call overloaded version with no parms
... all OK
ELSE
... serious error handling (message already given)
END IF

The next example checks the results of inline SQL and handles "not found":

SELECT Cust_Name
INTO :ls_Name
FROM CUST
WHERE Cust_Id = as_CustId;

CHOOSE CASE f_CheckDbError()
CASE 1
// all OK

CASE 0
// not found condition
ls_Name = "" // set default value

CASE ELSE
// serious error handling (message already provided)
li_Return = -1 // exit routine
END IF