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.

asked 22 May '14, 06:50

Arthoor's gravatar image

Arthoor
1.2k284662
accept rate: 0%

edited 23 May '14, 03:03

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

(22 May '14, 06:59) Volker Barth
Replies hidden
2

Try SET TEMPORARY OPTION ConsistentBehavior = 'On :)'

(22 May '14, 17:58) Breck Carter
1

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

SET TEMPORARY OPTION max_client_statements_cached = 0
(23 May '14, 07:49) Volker Barth

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

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

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

(23 May '14, 08:55) Volker Barth

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%20Rychliski's gravatar image

Mikel Rychliski
2.1k1641
accept rate: 30%

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

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%20Barth's gravatar image

Volker Barth
31.5k318461676
accept rate: 33%

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

permanent link

answered 23 May '14, 02:24

Arthoor's gravatar image

Arthoor
1.2k284662
accept rate: 0%

edited 23 May '14, 03:00

Hm, the obvious solution would be to make sure the desired table does exist:)

(23 May '14, 03:26) Volker Barth
Replies hidden

Is there another way to check if temporary table exists? :)

The background:
The original problem was declared here. Then I've made a workaround - I've moved temporary table creation from lower level procedure to a one step higher level procedure. But that lower level procedure sometimes can be called directly so it must be checked if temporary table exists like in these code blocks above. So after my modifications this "Invalid statement" problem arised. The exact real problem is someway different (I could not reproduce it yet) but with the same symptom - it gives "Invalid statement" error at some point after running for a long time when calling DELETE statement.

(23 May '14, 03:49) Arthoor

Is the problem "just two different error codes for the same symptom"? - I.e. if you handle both errors the same way (probably by creating the missing tempoary table), will the code work? Or do you notice a different behaviour due to the different error codes?

Is there another way to check if temporary table exists?

Well, you could as well try to SELECT (instead DELETE) from that table... - but AFAIK you have to try to access a temporary table to know of its existence, by design the system catalog won't give any hints...

(23 May '14, 04:57) Volker Barth

Is the problem "just two different error codes for the same symptom"?

No. The exact real problem in customer's production DB (which I still can't reproduce in my tests) is quite different. The DELETE statement fails with "Invalid statement" error when that temporary table actually does exist (or at least should exist). I hope that symptom in the event that I've written above is related with that real problem and somebody from SAP will give a hint how to workaround that and I'll not be forced to reproduce exactly the same problem. :)

you could as well try to SELECT (instead DELETE) from that table

The DELETE statement is needed in that case as I need to clean the table if it already exists. So additional SELECT statement before DELETE statement would be redundant.

(26 May '14, 02:24) Arthoor
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×81
×35
×18
×6

question asked: 22 May '14, 06:50

question was seen: 1,918 times

last updated: 26 May '14, 02:38