SAP SQL Anywhere

Question

SQLA 16.0.0.1915 I have a select statement in a Stored Proc that truly encounters an error: the SELECT INTO returned two rows. My error code (IF sqlcode < 0) executes, but it reports sqlcode=0 and sqlstate=00000. I used the Sybase Central debugger to stop after the SET ls_debug_info... to make sure it wasn't affected by a following line of code.

I hope it is a simple mistake, but I am baffled.

SELECT crcntc_id INTO lbi_crcntc_id
    FROM    tifis_crcntc_contact
    WHERE   phone_crregs_registry_id = crregs_registry_id AND
            old_phonenumber = crcntc_phone_number;
IF sqlcode < 0   THEN
    SET ls_debug_info = 'sqlcode=' || STRING(sqlcode) || ', sqlstate=' || sqlstate;
    CALL log_message(as_app_name, sqlcode, 'aisinterface_update_tifis', 'Error selecting tifis_crcntc_contact.');
    RETURN -18620;
END IF;

Accepted Answer

EDIT: ~~The SET statement within the IF block will reset SQLCODE to 0~~ The IF statement itself will already reset SQLCODE to 0, as it is successfully executed. So within the CALL statement, SQLCODE will already be 0 again. You will need to store the SQLCODE temporarily even before you check its value if you want to access it afterwards.

From the docs (somewhat hidden in the topic "Default handling of warnings"):

Successful execution of any SQL statement resets SQLSTATE to 00000 and SQLCODE to 0. If a procedure needs to save the error status, it must do an assignment of the value immediately after execution of the statement which caused the error or warning.

Note: An initial "SET mySQLCODE = SQLCODE;" statemement before the IF block will store that value but will reset SQLSTATE to 00000, too, so you may need a SELECT INTO statement to temporarily store both SQLCODE and SQLSTATE...

Answer 2

1

FWIW, in case your code must not be compatible with v12 or below, you might use the new v16 functions like ERROR_SQLCODE() and ERROR_SQLSTATE() together with TRY-CATCH blocks. AFAIK, these functions make the "triggering" error available even after further SQL statements have been called.

EDIT: The v16 docs are not really clear here, but ERROR_SQLCODE() and the like seem to work with "classic" Watcom-SQL exception handlers, too, cf. this small sample:

begin
   declare nTest int = 0;
   set nTest = 1 / 0;
exception when others then
   if SQLCODE < 0 then
      message 'Direct access:   SQLCODE = ' || SQLCODE || ', SQLSTATE = ' || SQLSTATE || '.' to client;
      message 'Function access; SQLCODE = ' || ERROR_SQLCODE() || ', SQLSTATE = ' || ERROR_SQLSTATE() || '.' to client;
   end if;
end;

This does return:

Direct access: SQLCODE = 0, SQLSTATE = 00000.
Function access; SQLCODE = -628, SQLSTATE = 22012.

Apparently, the IF statement will reset SQLCODE and SQLSTATE here but the according functions still give access to the triggering error.

permanent link

answered 03 Jul '14, 03:42

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 03 Jul '14, 04:02

Is it just me who would like a blog article by the SQL Anywhere team on such important improvements?

Even for an "Invisible database" enhancements like those should be made visible:)

(03 Jul '14, 05:35) Volker Barth

Replies hidden

1

See section 9. TRY CATCH And Friends in Top 10 Cool New Features in SAP Sybase SQL Anywhere 16.

(03 Jul '14, 10:31) Breck Carter

Well I am glad it is a simplistic and obvious problem. I am not so glad to realize I have a LOT of code that still looks like that! I started using the TRY-CATCH some time ago after Breck pointed out the advantages and gave a sample in reply to another problem I had. But I now see I have a lot of old code that isn't actually going to work if there is ever an exception. Live and learn. The advantage of having used a keypunch and punched tape are that I have had time to "learn" a lot :).

(03 Jul '14, 10:45) Bill Aumen

1

Sure enough I had looked into that valuable document before I posted my suggestion here - nevertheless even that really profound section does not explicitly tell that ERROR_SQLCODE() will conserve the SQLCODE over statement-boundaries...

But I don't want to sound picky, I'm happy that you keep blogging... If the SQL Anywhere team would do so at least now and then (besides Jason's appreciated "From Glenn's Archives"), it would be nice.

(03 Jul '14, 12:00) Volker Barth

I am MUCH happier now since I have discovered my most frequent code actually does work: IF sqlcode <> 0 THEN RAISERROR 18420 '18420 trigger prreg_pay_register_bu, sqlcode = ' || STRING(sqlcode); END IF;

This is the code I originally developed and tested, so I should have stuck with it and not tried to get fancier :).

So... more searching for the code that doesn't work. And more use of TRY-CATCH.

(03 Jul '14, 17:01) Bill Aumen

Replies hidden

Hm, are you sure the IF statement does not reset the SQLCODE to 0 here? In my tests (see above) the IF statement had had that undesired effect...

Re-tested with 12.0.1.3324:

begin
   declare nTest int = 0;
   set nTest = 1 / 0;
exception when others then
   if SQLCODE < 0 then
      message 'SQLCODE = ' || SQLCODE || ', SQLSTATE = ' || SQLSTATE || '.' to client;
   end if;
end;

returns "SQLCODE = 0, SQLSTATE = 00000."

whereas

begin
   declare nTest int = 0;
   set nTest = 1 / 0;
exception when others then
   message 'SQLCODE = ' || SQLCODE || ', SQLSTATE = ' || SQLSTATE || '.' to client;
end;

returns "SQLCODE = -628, SQLSTATE = 22012".

The displayed SQLCODE differs in a similar way when using RAISERROR instead of MESSAGE.

(03 Jul '14, 17:42) Volker Barth

FWIW the example in Bill's original question doesn't use EXCEPTION, it just tests SQLCODE after a SELECT.

(04 Jul '14, 07:24) Breck Carter

Yes, I'm aware of that. However, even that does behave in the way described in my own tests:

-- Forcing a -186 error ("Subquery cannot return more than one row")
select * from systable where table_id = (select table_id from systable);
if SQLCODE <> 0 then
   message 'Error: ' || SQLCODE to client;
end if;

returns "Error: 0" as well.

Or is there any particular T-SQL feature at work in Bill's procedure?

(04 Jul '14, 07:58) Volker Barth

Dunno about T-SQL, but this nugget throws cold water on the whole discussion: "SQLCODE was deprecated in the ANSI SQL/1992 standard, and was eliminated entirely from SQL/1999. SQLCODE values continue to be maintained in SQL Anywhere for backward compatibility for applications. SQLSTATE is the preferred status indicator."

(04 Jul '14, 09:18) Breck Carter

Well, I still prefer SQLCODE over those SQLSTATE strings (where one would have to test "IF SQLSTATE NOT LIKE '00___'" instead of "IF SQLCODE <> 0") - and how would one distinguish between errors and warnings?

But besides that: The discussion itself does both apply to SQLCODE and SQLSTATE - both are reset by the next statement, so that does not really cool down the current issue. (Not cooling down itself is fitting, we're just awaiting the WIFA World Cup quarter-final between France and Germany...)

(04 Jul '14, 09:46) Volker Barth

Hmmm. The one difference I see between my code that did work and your example is that I used RAISERROR instead of MESSAGE???

In my app, I check the results of the update, and this is how it looked back in my PowerBuilder app. That's SQLdbCode and SQLErrtext:

Database error code: -18415 SQLSTATE = S1000 [Sybase][ODBC Driver][SQL Anywhere]RAISERROR executed: 18415 trigger prreg_pay_register_bu, sqlcode = 100

(04 Jul '14, 11:12) Bill Aumen

Replies hidden

FWIW, as stated, I have tested with RAISERROR as well...

But nevermind, if it does work in your case, well then it's obviously alright with you:)

(05 Jul '14, 04:41) Volker Barth

Comment Text Removed

Here is what I think might be the difference. Foxhound reminds me of a non-default option setting I use: SET OPTION PUBLIC.continue_after_raiserror = 'OFF'; -- default 'On'

(07 Jul '14, 11:40) Bill Aumen

showing 4 of 13 show all flat view

SAP SQL Anywhere

error within Stored Proc, but sqlcode = 0

Follow this question

Related questions