CODEWITHSUNDEEP
sqldb2-400
Raestloz
Raestloz

Reputation: 203

SQL continue handler is not triggered

IBM i V7R1M0. I need to continue processing a statement whenever an error occurs and as far as I have read, such as from this:

https://dba.stackexchange.com/questions/88862/how-to-ignore-sql-errors-in-stored-procedure-not-handle

DECLARE CONTINUE HANDLER seems to be the answer, so

I have a very simple procedure that looks like this:

exec SQL create or replace procedure test_prod1   
         (in test2 decimal(1,0))                  
         language sql modifies sql data           
         begin                                    
         declare continue handler for sqlexception
            begin end;                            

         update DUPEPF set INT2 = test2;          
         end;

As far as I can tell, this means whenever an error occurs (such as unique key violation) the SQL statement will simply continue, yet this isn't the case. The statement stops whenever a key violation appears and the next rows are not processed. I am confused as to why this is the case

Upvotes: 0

Views: 558

Answers (1)

Charles
Charles

Reputation: 23823

Your continue handler is working...

Your procedure is ignoring the error thrown by the UPDATE statement and continuing. Except that there isn't anything else to do.

Just because your proc is ignoring the error, doesn't mean the DB can ignore the error inside of it's update statement processing.

EDIT
handlers change how your stored procedure or UDF handle errrors...think of them as a way to "catch" errors thrown by the DB. They don't prevent the DB from throwing those errors in the first place

Make sense?

In order to do what you're trying to do, you'd need to use your own cursor, something like so...

     create or replace procedure test_prod1   
     (in test2 decimal(1,0))                  
     language sql modifies sql data           
     begin           
     declare myInt integer;
     DECLARE DUPLICATE_KEY CONDITION FOR SQLSTATE '23505';
     DECLARE END_OF_TABLE CONDITION FOR SQLSTATE '02000';
     declare test_cursor cursor for
        select int2 from DUPEPP for update;
     declare exit handler for END_OF_TABLE
        close test_cursor;                         
     declare continue handler for DUPLICATE_KEY
        begin end;                            


     open test_cursor;
     fetch_loop:
     LOOP
       fetch next from test_cursor into myInt;
       update dupepf set int2 = test2
         where current of test_cursor;
     END LOOP fetch_loop;
     end;

Upvotes: 1

Related Questions