- A failure message to a CLIv2-based external stored procedure.
- An exception to a JDBC-based external stored procedure.
The system returns the message to the external procedure to provide it with an opportunity to post the error and then to either cleanly close or disconnect any external files or connections it had established. The only remaining course of action is for the procedure to return something to the caller.
The following outcomes occur depending on what the external stored procedure returns.
| IF the external procedure … | THEN … |
|---|---|
| returns with its own error | it can set the SQLSTATE to its own exceptions code and the original fail condition will not be known to the caller of the procedure. |
| returns no error (the SQLSTATE value is ‘00000’) | the system returns the original failure to the caller. |
| attempts to submit another request | the system returns an error because the procedure submitted a request after receiving a trigger fail message. |
The system terminates the triggering request in all of these cases.
The following table documents examples of three possible outcomes for the following scenario.
Suppose a trigger is defined as follows.
CREATE TRIGGER trig2 AFTER INSERT ON t2 FOR EACH ROW (CALL sqlxspex(2););
You then submit the following two requests from the external stored procedure using either CLIv2 or JDBC function calls, where the table named ThisIsNotATable does not exist:
INSERT INTO t2 VALUES (1,1); DELETE FROM ThisIsNotATable ALL;
Because there is no table named ThisIsNotATable in the current database, the system returns an error to the procedure.
| IF the procedure … | THEN the outcome is as follows … |
|---|---|
| returns its own error | the error message reports whatever SQLSTATE code and text message is coded in the procedure, for example:*** Failure 7504 in UDF/XSP/UDM rgs.sqlxspex: SQLSTATE U0001:
Statement# 1, Info =0
*** Total elapsed time was 1 second.
Note that the outcome of this example is not determined by the system, but is entirely dependent on how the procedure is coded. The example is designed to show how an external procedure handles the error the system returns to the trigger. In this case, the procedure was coded to set the SQLSTATE to ‘U0001’ for a 7504 error, but any valid SQLSTATE code and user-written text message could have been used. |
| returns with a SQLSTATE of ‘00000’ | the error message reports the original error that was reported to the external stored procedure:*** Failure 3807 SQLXSPEX:Object 'ThisIsNotATable' does not exist.
Statement# 1, Info =0
*** Total elapsed time was 1 second.
In this case, the original error is the failure of the DELETE request that the procedure attempted to execute against a table that does not exist. |
| ignores the error and attempts to submit another SQL request | the procedure is terminated with the following error message:*** Failure 7836 The XSP mydbase.sqlxspex submitted a request subsequent to receiving a trigger fail message.
Statement# 1, Info =0
*** Total elapsed time was 1 second.
|