Informix 11.70: Stored Procedure SQL exceptions
Bob Kruse found that in an Informix 11.70 stored procedure, an error in a WHILE loop over a cursor exits the loop despite an exception handler WITH RESUME. Alexandre Marini first suggested fixing the errors, then, after clarification, suggested a violations table and savepoints. No way to keep the loop running was found; unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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