SAP SQL Anywhere

Question

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.

Answer 1

1

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.

permanent link

answered 23 May '14, 06:13

Mikel Rychliski
2.1k●16●41
accept rate: 32%

edited 23 May '14, 07:21

1

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?

(23 May '14, 08:50) Breck Carter

@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"...)

(23 May '14, 09:01) Volker Barth

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.

(23 May '14, 09:06) Volker Barth

@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.

(23 May '14, 09:27) Mikel Rychliski

1

@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)

(23 May '14, 09:36) Mikel Rychliski

@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.

(26 May '14, 01:45) Arthoor

More comments hidden

showing 5 of 6 show all flat view

Answer 2

0	Might be a DBISQL issue - a test with dbisqlc on 12.0.1.4085 returns the expected "Table not found" error for all repetitions. Good ol' times... permanent link answered 22 May '14, 07:05 Volker Barth 39.5k●355●539●811 accept rate: 34% flat view

Answer 3

Well, the deeper I test the more strange results I get. Now I encapsulated these blocks (five this time) into an event:

IF EXISTS(select 1 from sys.sysevent where event_name = 'ev_test_exceptions') THEN
    DROP EVENT "ev_test_exceptions"
END IF;

CREATE EVENT "ev_test_exceptions"
SCHEDULE "every_10_seconds" START TIME '00:00' EVERY 10 SECONDS
HANDLER
BEGIN
    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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                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';
            WHEN err_invalidstatement THEN
                message 'Invalid statement error';
            WHEN OTHERS THEN
                message 'Another error';
                RESIGNAL;
    END;
END;

And these are the results in log file:

I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:06:50. Table not found error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:00. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:10. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
I. 05/23 09:07:20. Invalid statement error
...

I. e. when the event runs first time it gives correct results for all blocks, and when it runs every next time it gives unexpected results. After event recreation it runs correctly again first time.

If I move all the code from event to procedure and only call that procedure in the event then it seems to work correctly.

This time it is not related with dbisql. Not sure if this case should be asked as separate question since these cases must be somehow related (for now I've just edited question header and body to include event)...

SAP SQL Anywhere

Interactive SQL (dbisql) and event bug on exception handling

Follow this question

Related questions