How to Form an Exception Handler

[former_SQLA_member1694868]

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

[Former Member · Accepted solution]

There is a difference in how errors are processed depending on whether the error occurs as part of executing a stored-procedure statement or instead it occurs while returning rows from a result set of the procedure that has been opened without error. Errors that are detected while processing a result set are not handled by the EXCEPTION clause if it is present. Instead, the error is returned to the consumer. This explains the original question because in the second case, the error is detected when the row is fetched from the result set.

As noted by others, there is a difference in behavior with this statement between different versions of SA. The behavior change stems from change 560044. This change causes the error to get generated as rows are fetched from the statement instead of when it is opened. This timing change only affects errors generated with expressions over constants. If the statement had contained something like the following, then behavior is unchanged (and an error is reported to the client):

begin
    select (YMD( dummy_col,1,1)) from sys.dummy;
EXCEPTION when others then
end

With this example, the error can not be detected when the result set is opened, instead it occurs as the rows are fetched. As such, the error is not processed with the EXCEPTION clause and instead it is returned on the fetch.

The purpose of change 560044 was not to affect the way exceptions are processed; the change is intended to avoid generating spurious errors that occur while constant expressions are evaluated at open time. In some cases, these constant expressions generated errors but the proper result set could be returned without evaluating the expressions (for example, because the result set was empty, or because other conditions prevented the expression from being needed). The change defers the error until the expression value is definitely needed.

Breck, can you explain further (here or open a support case) how this change prevents you moving to a newer version. There may be some further refinements we can make to the change if we know the situations where you think the error should be generated at open time.

[former_SQLA_member1694868 · Accepted solution]

@Ivan: After your description I understand the differences between the situations.

My original goal was to handle ANY error with the exception handler, but it seems that some errors are unable to be handled and are passed on to the user. Are there any other ways to handle these errors?

[Breck_Carter · Accepted solution]

@Ivan: I am not woooorrrthy! Seriously, no support case, you have just explained why I sometimes see exceptions reach the browser window.

[Breck_Carter · Accepted solution]

@Ivan: Let's skip lunch today, let's just open another can of worms here at our desks: http://sqlanywhere-forum.sap.com/questions/1221/is-this-how-i-have-to-trap-exceptions-raised-by-the-...

[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:(

[VolkerBarth]

FWIW, John Smirnios has identified the change in behaviour as a result of the fix 560044, as stated in his comment on the "Bugfix documentation question".

As to the CR docs, the change happened in build 12.0.0.2330 and 11.0.1.2412.

Don't expect me to explain the behaviour - I'm just the messenger here:)

[johnsmirnios]

Due to that change, the evaluation of some constant expressions has changed from the time when cursors are opened to the time when values are fetched. I'm told that exception handlers only catch errors that occur during open, not fetch. Strictly speaking, then, the new behaviour is still "correct" but we also never like to change such fundamental behaviour in an EBF. From here, I'll let the developers who deal with those components figure out what, if anything, must be done. It's not my area of expertise.

[VolkerBarth]

For your 2nd example, I get a syntax error (SQLCODE -131) relating to the "set" in line 3 (tested with SA 11.0.1.2427).

That's reasonable as you are mixing Transact-SQL and Watcom-SQL dialects here, and that is not allowed in batches: The declare without a closing delimiter (no ';') is T-SQL, the set statement not.

It seems that a syncatically invalid batch won't be executed, and as such, neither the normal statements nor the exception handler are executed.

[Breck_Carter]

yes, but it gets interesting if you fix the syntax error... he probably broke the cardinal rule "Always copy and paste your code, never type it in from scratch."

[VolkerBarth]

Oh, "lack of coffee" must be the reason: I focussed on the missing ";" as the one difference, completely missing the execute/select change in line 4. And for sure: Your observation is more interesting:)

[former_SQLA_member1694868]

@Breck: Certainly did. Edited @Volker: I did not know that about mixing the dialects in batches. As I am picking up the language and taking some snippets from the book and copying some code from a blog I will be aware of that now, so please leave this answer even though I edited the question.

[VolkerBarth]

@Siger: No need to worry, we don't delete answers here... Glenn Paulley has written a great overview over the two dialects (and the resulting parsing problems) in his blog, cf. http://iablog.sybase.com/paulley/2010/03/mixing-sql-dialects.