Informix Guide to SQL: Tutorial

Informix Guide to SQL: Tutorial
Chapter 14: Creating and Using SPL Routines

Home Contents Index Master Index New Book

Exception Handling

You can use the ON EXCEPTION statement to trap any exception (or error) that the database server returns to your SPL routine, or any exception raised by the routine. The RAISE EXCEPTION statement lets you generate an exception within the SPL routine.

In an SPL routine, you cannot use exception handling to handle the following conditions:

Success (row returned)
Success (no rows returned)

Trapping an Error and Recovering

The ON EXCEPTION statement provides a mechanism to trap any error.

To trap an error, enclose a group of statements in a statement block marked with BEGIN and END and add an ON EXCEPTION IN statement at the beginning of the statement block. If an error occurs in the block that follows the ON EXCEPTION statement, you can take recovery action.

Figure 14-107 shows an ON EXCEPTION statement within a statement block.

Figure 14-107

BEGIN

DEFINE c INT;

ON EXCEPTION IN

	(

	-206, -- table does not exist

	-217  -- column does not exist

	) SET err_num

	

IF err_num = -206 THEN

		CREATE TABLE t (c INT);

		INSERT INTO t VALUES (10);

		-- continue after the insert statement

	ELSE

		ALTER TABLE t ADD(d INT);

		LET c = (SELECT d FROM t);

		-- continue after the select statement.

	END IF

END EXCEPTION WITH RESUME



INSERT INTO t VALUES (10);  -- will fail if t does not exist



LET c = (SELECT d FROM t);  -- will fail if d does not exist

END

When an error occurs, the SPL interpreter searches for the innermost ON EXCEPTION declaration that traps the error. The first action after trapping the error is to reset the error. When execution of the error action code is complete, and if the ON EXCEPTION declaration that was raised included the WITH RESUME keywords, execution resumes automatically with the statement following the statement that generated the error. If the ON EXCEPTION declaration did not include the WITH RESUME keywords, execution exits the current block entirely.

Scope of Control of an ON EXCEPTION Statement

An ON EXCEPTION statement is valid for the statement block that follows the ON EXCEPTION statement, all the statement blocks nested within the following statement block, and all the statement blocks that follow the ON EXCEPTION statement. It is not valid in the statement block that contains the ON EXCEPTION statement.

The pseudo code in Figure 14-108 shows where the exception is valid within the routine. That is, if error 201 occurs in any of the indicated blocks, the action labeled a201 occurs.

Figure 14-108

CREATE PROCEDURE scope()

	DEFINE i INT;

	.

	.

	.

	BEGIN		-- begin statement block A

	.

	.

	.

		ON EXCEPTION IN (201)

		-- do action a201

		END EXCEPTION

		BEGIN -- statement block aa

			-- do action, a201 valid here

		END

		BEGIN -- statement block bb

			-- do action, a201 valid here

		END	

		WHILE i < 10

			-- do something, a201 is valid here

		END WHILE



	END

	BEGIN		-- begin statement block B

		-- do something

		-- a201 is NOT valid here

	END 		

END PROCEDURE;

User-Generated Exceptions

You can generate your own error using the RAISE EXCEPTION statement, as the example in Figure 14-109 shows.

Figure 14-109

BEGIN

	ON EXCEPTION SET esql, eisam  -- trap all errors

		IF esql = -206 THEN       -- table not found

			-- recover somehow

		ELSE

			RAISE exception esql, eisam ; -- pass the error up

		END IF

	END EXCEPTION

		-- do something

END

In Figure 14-109, the ON EXCEPTION statement uses two variables, esql and eisam, to hold the error numbers that the database server returns. The IF clause executes if an error occurs and if the SQL error number is -206. If any other SQL error is caught, it is passed out of this BEGIN...END block to the last BEGIN...END block of the previous example.

Simulating SQL Errors

You can generate errors to simulate SQL errors, as the following example shows. In Figure 14-110, if the user is pault, then the SPL routine acts as if that user has no update privileges, even if the user really does have that privilege.

Figure 14-110

BEGIN

	IF user = 'pault' THEN

	 	RAISE EXCEPTION -273;  -- deny Paul update privilege

	END IF

END

Using RAISE EXCEPTION to Exit Nested Code

Figure 14-111 shows how you can use the RAISE EXCEPTION statement to break out of a deeply nested block.

Figure 14-111

BEGIN

	ON EXCEPTION IN (1)

	END EXCEPTION WITH RESUME -- do nothing significant (cont)



	BEGIN

		FOR i IN (1 TO 1000)

			FOREACH select ..INTO aa FROM t

				IF aa < 0 THEN

					RAISE EXCEPTION 1 ;    -- emergency exit

				END IF

			END FOREACH

		END FOR

		RETURN 1;

	END 

	

	--do something;         -- emergency exit to

	                                -- this statement.

	TRACE 'Negative value returned';

	RETURN -10;

END

If the innermost condition is true (if aa is negative), then the exception is raised, and execution jumps to the code following the END of the block. In this case, execution jumps to the TRACE statement.

Remember that a BEGIN...END block is a single statement. If an error occurs somewhere inside a block and the trap is outside the block, the rest of the block is skipped when execution resumes and execution begins at the next statement.

Unless you set a trap for this error somewhere in the block, the error condition is passed back to the block that contains the call and back to any blocks that contain the block. If no ON EXCEPTION statement exists that is set to handle the error, execution of the SPL routine stops, creating an error for the routine that is executing the SPL routine.