Catch an exception in event version 11

[former_member329524]

Hello, all

We have an old DB, which had a scheduled event added to it recently.

Even launches a stored procedure. Sometimes it terminates with error -6.

My question is: how can we catch the sql error text of this error and then allow the procedure to continue despite an error (it is, basically, a loop statement).

The client is using version 11, so there is no TRY CATCH.

Thank you

[former_member188493]

Before there was TRY CATCH, there was BEGIN ... EXCEPTION WHEN OTHERS THEN ... END which is (as far as I can tell) just as powerful. The trick is to capture the values in SQLCODE, SQLSTATE, and ERRORMSG() upon entry to the EXCEPTION WHEN OTHERS THEN section before any other code has a change to overwrite/change/reset those values.

For an introduction see SQL Anywhere: Tip: The All-Encompassing Try-Catch

For an extended example see SQL Anywhere: Dump and Continue

[JasonHinsperger]

You can use the "ON EXCEPTION RESUME" clause in the create procedure statement to continue after an error.  The special variables SQLCODE and SQLSTATE contain information about the execution status of the most recently executed statement.  The ERRORMSG built in function will give you the text of the most recent error message.

eg. You could do something like this:

create procedure x() on exception resume

begin

--do stuff

select a from foo;

if SQLCODE < 0 then

   --handle the error;

end if

--do more stuff

end

--Jason