How to Form an Exception Handler

[#1]

Why is it that in this case:

begin
declare @zero integer;
set @zero = 0;
execute (YMD(@zero,1,1));
EXCEPTION when others then 
end

The code executes without error,

while in this case:

begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end

I get the error:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

Breck has this article on exceptions already posted on his blog, but as I was experimenting I ran into the above.

EDITS:
Added ";" to second block as it should have been.
Using Version 11.0.1.2472

[Breck_Carter]

Later edit: Read Ivan's answer if you want the real story. Read this answer if you want some mild amusement, but do NOT let it guide you on your journey! Also, for a followup question, see http://sqlanywhere-forum.sap.com/questions/1221/is-this-how-i-have-to-trap-exceptions-raised-by-the-...

---

Edit: Please note Volker's comment, where he describes the behavior in build 11.0.1.2427. My answer below uses an earlier build 11.0.1.2276 as the "base line" for comparisons.

Good catch!

This looks like a bug undocumented behavior change in Version 12, both the GA build 12.0.0.2483 and the first published EBF 12.0.0.2566:

An exception raised by a SELECT that returns a result set from a SQL block will bypass the subsequent EXCEPTION handler in that block.

Here's your code in V11 and V12: a simple BEGIN END block in dbisql with a exception raised in the SELECT that dbisql would otherwise display in the results tab. Version 11 behaves as expected (no result set, no message), but Version 12 throws that funky dbisql dialog box (it should not).

begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end

-- 11.0.1.2276: no result set in dbisql, no error message

-- 12.0.0.2483: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

-- 12.0.0.2566: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

If you wrap the failing SELECT inside a PROCEDURE and SELECT from that procedure, the behavior in V11 is slightly different but also expected (empty result set, but still no message). However, in V12 it is still wrong funky.

CREATE PROCEDURE p()
begin
declare @zero integer;
set @zero = 0;
select (YMD(@zero,1,1));
EXCEPTION when others then
end;
SELECT * FROM p();

-- 11.0.1.2276: empty result set, no error message

-- 12.0.0.2483: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

-- 12.0.0.2566: empty result, error message...
There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
Cannot convert 0 to a date
SQLCODE=-157, ODBC 3 State="07006"

I have a whole jackwagon full of code that depends on the V11 behavior, which means I've got a whole lot of work to do before moving it to 12... if I had any idea how to do it 🙂

---

jackwagon - Freight wagon or Chuck wagon (which held supplies) typically pulled by mules. Usually the slowest wagon in a wagon train. Worst job in a wagon train, being at the back, eating all the dust, dirt and smell from the front. Mules are identified as Jacks or Jenny depending on the sex of the mule. http://www.webanswers.com/answer/1331703/misc/what-is-a-jack-wagon-9a4409

---

It gets worse... I may not be able to move off build 11.0.1.2276 to a later EBF; see Volker's comment.

[VolkerBarth]

More interesting results: Testing with SA 11.0.1.2427, the first example does show an ISQL error dialog - both with DBISQL and DBISQLC.

[Breck_Carter]

Another good catch! ...and WORSE for me, because I was thinking about upgrading to a later EBF of V11. Sigh.

[VolkerBarth]

Sadly, the only documented change in general exception-handling between these versions seems to be the following one (taken from the 2427 readme). But it deals with T-SQL and as such, might not at all relate to this: http://search.sybase.com/kbx/changerequests?bug_id=623891.

[VolkerBarth]

To add: The statement block of interest is not T-SQL, as select sqldialect(...) will reveal. So I guess the link is not helpful (and the database options I use don't match the mentioned ones).

[VolkerBarth]

@Breck: Besides that, I really love your usage of the "catch" phrase: In well-known programming languages, that's the equivalence of SA's exception clause. As such, I feel you are thrown on your own exception-handling for your "Update MyApplication set version = V12;" statement. - But that's no fun, indeed:(