Informix 11.70: Stored Procedure SQL exceptions

Greetings, I've written an extensive stored procedure that has some dynamic SQL. I have an exception handler defined with the "With Resume" clause to continue processing if an error occurs. The trouble is, when the code is in a FOR loop and incurs an error, it drops out of the loop. I tried defining the for loop as a cursor with hold but that did not help. Any suggestions? Is this managed better in 14.10? Thanks, Bob Kruse

Hello. Informix is very friendly. Wouldn¨t be easier if you try to correct these errors, instead of trying the database to ignore them? You did not mention which kind of errors are these, but it would be easier to correct them, than to avoid them, for sure. Even some small changes on your sql coding could solve them. If you could be some more specific, or could share some code, we could help further. HTH Best regards Alexandre Marini

Hi Alexandre, I did correct the errors. The point I am making is when you set up a loop construct in Stored Procedure Language, if there is any error it drops out of the loop. So if I am processing 1000 rows and there is a SQL error on the second row, the other 998 rows are not processed by the loop. A SQL error may occur to data related to a single row. The other 999 rows may process fine. I put an exception handler in to record the error and resume execution. But the code should not drop out of the loop until the exit conditions are satisfied. Thanks, Bob

Alexandre, I should also explain that I set up a WHILE loop to read from a select cursor. Thanks, Bob

Ok, Bob, I got your point. Maybe some violations table could be useful, instead? Then you might have another program/procedure to analyze and correct the missing rows.... I would also take a look at savepoints, maybe you can use them to a previous state and correct/proceed. Hope this helps Best regards Alexandre Marini