RELEASE SAVEPOINT

Use the RELEASE SAVEPOINT statement to delete a savepoint.

Syntax

RELEASE SAVEPOINT savepoint-name 
savepoint-name ::= user-defined-name

Remarks

RELEASE, ROLLBACK, and SAVEPOINT and are supported at the session level (outside of stored procedures) only if AUTOCOMMIT is off. Otherwise, RELEASE, ROLLBACK, and SAVEPOINT must be used within a stored procedure.

Any committed statements within a stored procedure are controlled by the outermost transaction of the calling ODBC application. This means that, depending on the AUTOCOMMIT mode specified on SQLSetConnectOption, calling the stored procedure externally from an ODBC application performs one of two actions. It either commits automatically (AUTOCOMMIT on, the default) or waits for you to call SQLTransact with SQL_COMMIT or SQL_ROLLBACK (when AUTOCOMMIT is set to off).

Examples

The following example sets a SAVEPOINT then checks a condition to determine whether to ROLLBACK or to RELEASE the SAVEPOINT.

CREATE PROCEDURE Enroll_student( IN :student ubigint, IN 
:classnum INTEGER); 
BEGIN 
DECLARE  :CurrentEnrollment INTEGER; 
DECLARE  :MaxEnrollment INTEGER; 
SAVEPOINT SP1; 
INSERT INTO Enrolls VALUES (:student, :classnum, 
0.0); 
SELECT COUNT(*)  INTO  :CurrentEnrollment FROM 
Enrolls WHERE class_id = :classnum; 
SELECT  Max_size INTO  :MaxEnrollment FROM  Class 
WHERE  ID = :classnum; 
IF :CurrentEnrollment >= :MaxEnrollment 
THEN 
ROLLBACK TO SAVEPOINT SP1; 
ELSE 
RELEASE SAVEPOINT SP1; 
END IF; 

END;

Note that COUNT(expression) counts all non-NULL values for an expression across a predicate. COUNT(*) counts all values, including NULL values.