Interactive SQL (dbisql) and event bug on exception handling

[VolkerBarth]

I have this code with exception handling (three identical BEGIN-END blocks to reproduce the bug):

BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;
BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;
BEGIN
    DECLARE err_tablenotfound EXCEPTION FOR SQLSTATE '42W33';
    DECLARE err_invalidstatement EXCEPTION FOR SQLSTATE '07W02';
    DELETE FROM #tmp_foo;
    EXCEPTION
        WHEN err_tablenotfound THEN
            message 'Table not found error' to client;
        WHEN err_invalidstatement THEN
            message 'Invalid statement error' to client;
        WHEN OTHERS THEN
            message 'Another error' to client;
            RESIGNAL;
END;


I run it in dbisql. My results:
Table not found error
Execution time: 0.008 seconds
Table not found error
Execution time: 0.002 seconds
Invalid statement error
Execution time: 0.001 seconds


I. e. the 3rd block gives me unexpected results.
dbisqlc works as expected.
Can anyone repeat this behavior?
Tried all these client and server versions of SA: 11.0.1.3113, 12.0.1.4086, 16.0.0.1824.
Edited: similar problem exists in events, see my own answer below.

[VolkerBarth]

I've tried with 12.0.1.4085, and the results are similar:

The first and second block return "Table not found error", all other repetitions return "Invalid statement error". I.e. if I repeat all 3 three blocks, they always return "Invalid statement error".

It's reproducable: If I drop the connection and re-connect then the next two block repetitions return "Table not found error", and all following ones return the other error.

Don't know what that means:(

[Breck_Carter]

Try SET TEMPORARY OPTION ConsistentBehavior = 'On :)'

[VolkerBarth]

So that option does exist but has a different name:)

SET TEMPORARY OPTION max_client_statements_cached = 0

[Breck_Carter]

That is TOO funny... I thought you were being sarcastic until I read Mikel's answer! ...well done.

[VolkerBarth]

Hm, I guess I have been a little bit sarcastic (or at least ironic) here - but the implications are too huge, as you have already pointed out:(

[Former Member]

Looks like this is a statement caching issue. I can reproduce it using JDBC, although it does require the garbage collector to run at the right time, and the server to decide to cache the statement.

If you need a workaround SET TEMPORARY OPTION max_client_statements_cached=0 should cause the (correct) "Table not found" error to occur every time.

[Breck_Carter]

If this is a server issue rather than a dbisql issue, the implications are huge: statement caching causes incorrect behavior, and should be turned off for all databases.

Your thoughts?

[VolkerBarth]

@Mikel: As Arthoor's tests have shown, the problem does appear for event code, too, so probably the same caching is done for events. Under what category of the following - quoted from here - do they fall?

The setting of this option applies to connections made using embedded SQL, ODBC, OLE DB, ADO.NET, and the SQL Anywhere JDBC driver. It does not apply to Sybase Open Client, jConnect, or HTTP connections.

(Possibly I'm just not used to thinking of events as "client code"...)

[VolkerBarth]

I'm even more puzzled that my tests with DBISQL and dbisqlc with the same user on the same (SA 12 test) database have revealed different results - I certainly have not changed that option in-between... - so if it's a server-side issue, why would the client matter, as long as the client API does make use of that caching? Here it would apply to both AFAIK (ESQL for dbisqlc, the SA JDBC driver for DBISQL)... Strange.

[Former Member]

@Breck It should be noted that the sever will keep track of situations where statement caching causes behaviour changes and react appropriately. For example, while repeatedly executing DELETE FROM #temp_foo will cache the statement, if you create a temp table with that name (CREATE TABLE #temp_foo (c1 int)) the server will drop it's cached statement and re-prepare the DELETE FROM #temp_foo on next execution.

The resulting SQL code does change as a result of the statement caching, but it could be argued that the 'Invalid statement' is more accurate (we aren't actually checking if the temp table is there). I'm not sure in this case if the invalid statement is acceptable.

[Former Member]

@Volker Probably has to do with the statements that dbisql executes in the background (for text auto-complete, etc.) changing the servers statement caching behaviour. The server will start or stop caching statements for a connection depending on the hit/miss rate. (Also FWIW, I'm able to get the 07W02 using ESQL)

[former_SQLA_member1694875]

@Mikel: max_client_statements_cached=0 does work in dbisql, but does NOT in events, i. e. I can't see any differences in my event tests.