Community
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Subscribe

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.

Know the answer?

Help others by sharing your knowledge.

Answer

Need more details?

Request clarification before answering.

Show replies
Show replies
VolkerBarth
Contributor
‎2014 May 22 7:59 AM
0 Likes

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
Participant
‎2014 May 22 6:58 PM

Try SET TEMPORARY OPTION ConsistentBehavior = 'On :)'

VolkerBarth
Contributor
‎2014 May 23 8:49 AM

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

SET TEMPORARY OPTION max_client_statements_cached = 0
Breck_Carter
Participant
‎2014 May 23 9:36 AM
0 Likes

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

VolkerBarth
Contributor
‎2014 May 23 9:55 AM
0 Likes

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

View Entire Topic
former_SQLA_member1694875
Participant
‎2014 May 23 3:24 AM
0 Likes

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)...
Show replies
Show replies
VolkerBarth
Contributor
‎2014 May 23 4:26 AM
0 Likes

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

former_SQLA_member1694875
Participant
‎2014 May 23 4:49 AM
0 Likes

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.

VolkerBarth
Contributor
‎2014 May 23 5:57 AM
0 Likes

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

former_SQLA_member1694875
Participant
‎2014 May 26 3:24 AM
0 Likes

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.

Ask a Question