on 2014 Jul 02 4:58 PM
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;
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...
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
See section 9. TRY CATCH And Friends in Top 10 Cool New Features in SAP Sybase SQL Anywhere 16.
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 :).
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.
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.
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.
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?
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."
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...)
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
User | Count |
---|---|
75 | |
9 | |
9 | |
8 | |
8 | |
7 | |
7 | |
6 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.