Tip: The All-Encompassing Try-Catch

One of the least-used SQL statements in all of SQL Anywhere is the "try-catch" EXCEPTION handler. If you look it up in the Help, you'll see a discussion of how to use it with DECLARE ... EXCEPTION FOR SQLSTATE declarations, but the open-ended all-encompassing form is easier to code and often more useful.

Let's say you want to execute some statement, and you don't care if it works or not, you just want your code to robustly carry on with the next statement: for example, you want to DROP a table that may or may not exist. If the table doesn't exist, you don't care, and you certainly don't want to get an error message because the DROP failed.

One solution is to wrap the DROP statement in a BEGIN - END block, and include an EXCEPTION handler that does nothing. Here's an example where a local temporary table is being dropped; because there is no information about local temporary tables in the system catalog there's no easy way to check if the table exists, so the exception handler is a good alternative:

BEGIN
DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

CREATE LOCAL TEMPORARY TABLE t (
   x INTEGER NOT NULL PRIMARY KEY );

The clause "EXCEPTION WHEN OTHERS THEN" means "trap any exception that occurs in the BEGIN block", and the fact that no code follows the THEN means "ignore the exception".

Now let's say you have some code that might cause problems, and you want to capture diagnostic information if an exception occurs and then carry on. You can use the same "trap any exception..." syntax, but this time you put some diagnostic code after the THEN. Here's a template for a stored procedure:

CREATE PROCEDURE ...
BEGIN

   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );

   -- code outside the scope of the exception handler

   BEGIN

      -- code which will have exceptions handled

   EXCEPTION WHEN OTHERS THEN

      SELECT SQLCODE, SQLSTATE, ERRORMSG()
         INTO @sqlcode, @sqlstate, @errormsg;

      -- optional code: further processing, etc.

   END;

   -- code that can check if an exception occurred

END;

Here's a simple demonstration:

BEGIN
   DECLARE @sqlcode   INTEGER;
   DECLARE @sqlstate  VARCHAR ( 5 );
   DECLARE @errormsg  VARCHAR ( 32767 );
   DECLARE @date      DATE;
   BEGIN
      SET @date = 'xxx'; -- raise an exception
   EXCEPTION WHEN OTHERS THEN
      SELECT SQLCODE, SQLSTATE, ERRORMSG()
         INTO @sqlcode, @sqlstate, @errormsg;
   END;
   MESSAGE STRING ( 'SQLCODE = ', @sqlcode ) TO CLIENT;
   MESSAGE STRING ( 'SQLSTATE = ', @sqlstate ) TO CLIENT;
   MESSAGE STRING ( 'ERRORMSG() = ', @errormsg ) TO CLIENT;
END;

SQLCODE = -157
SQLSTATE = 53018
ERRORMSG() = Cannot convert 'xxx' to a timestamp