Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2012
    Posts
    2

    Question Unanswered: Rollback stored procedures

    Greetings.

    I have an stored procedure that call others stored procedures. If one of them fails, i need all the process to rollback. Every stored procedure, in case of fail returns an exception, which is caught by the main sp. At this point it works well.

    Then, when the exception is grabbed, i send a rollback statement, but all the changes made by the successful sp remains in the database.

    Is there a way to rollback all the process?

    Thanks in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by jjluna View Post
    Greetings.

    I have an stored procedure that call others stored procedures. If one of them fails, i need all the process to rollback. Every stored procedure, in case of fail returns an exception, which is caught by the main sp. At this point it works well.

    Then, when the exception is grabbed, i send a rollback statement, but all the changes made by the successful sp remains in the database.

    Is there a way to rollback all the process?

    Thanks in advance.
    Have you tried defining the procedures with COMMIT ON RETURN NO ..

    What platform and version are you talking about ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Apr 2012
    Posts
    2
    Hi

    I'm working with db2 for i5/OS.

    This is, basically, the structure of the main sp:

    Code:
    DECLARE EXIT HANDLER FOR SQLSTATE
    	BEGIN		
    		ROLLBACK; 
    	END;
    	
    	OPEN CURSOR0 ; 
    	SET ENDTABLE = 0 ; 
    	WHILE ENDTABLE = 0 DO 
    		FETCH CURSOR0 INTO ID_TMP , TIPO_TMP , VTA_CHECK_TMP ; 
    	END WHILE ; 
    	CLOSE CURSOR0 ; 
      
    	IF VTA_CHECK_TMP = 'S' THEN 
    		CALL CR400 . CRV2_GET_TIENDA ( TIENDA_TMP ) ; 
    		CALL CR400 . CRV2_CHK2_TRANSACCION ( TIENDA_TMP , NRO_CAJA , NRO_TRNS , COUNT_TRC , COUNT_TRD , COUNT_TRT , VALUE_EXCPT ) ; 		
    		IF ( COUNT_TRC = 0 AND COUNT_TRD = 0 AND COUNT_TRT = 0 ) THEN 			
    			CALL CR400 . CRV2_ADD_TRCMOV ( TIENDA_TMP , NRO_CAJA , NRO_TRNS ) ;	 			
    			CALL CR400 . CRV2_ADD_TRDMOV ( TIENDA_TMP , NRO_CAJA , NRO_TRNS ) ; 
    			CALL CR400 . CRV2_ADD_TRCTOV ( TIENDA_TMP , NRO_CAJA , NRO_TRNS ) ; 			
    			CALL CR400 . CRV2_ADD_CLIENTE_TRANSACCION ( TIENDA_TMP , NRO_CAJA , NRO_TRNS ) ; 	 
    			CALL CR400 . CRV2_ADD_MAQ_FISCAL_TRANSACCION ( TIENDA_TMP , NRO_CAJA , NRO_TRNS ) ;  
    			CALL CR400 . CRV2_ADD_MAECPC ( NRO_CAJA , NRO_TRNS ) ; 
    		END IF ; 
            END IF ;
    What I need is, if some of the inner sp fails, revert all the changes made before the exception.

    This is the code of one of the inner sp:

    Code:
    DECLARE EXIT HANDLER FOR SQLEXCEPTION, NOT FOUND
    	BEGIN
    		ROLLBACK ; 
    		SIGNAL SQLSTATE;
    	END;
    	INSERT INTO	.....
    It returns an exception which is successfully catched by the main sp. The problem is that the main sp does the rollback, but none of the changes made by the inner sp are rollbacked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •