Equivalent of Firebird "when any" for SQL exception trapping in InterBase

Question

I need to extend an app written with Embarcadero Firemonkey for Windows to Android, so I need InterBase.

Besides there is no tutorial (none found so far at least) on how to use Firebird under Android, I have found a very important difference not only in the language itself, but in the way I can trap exceptions with Firebird and InterBase (i.e. into an insert stored procedure). Here is a sample Firebird code:

ALTER PROCEDURE AD_0_LIST_UPD_ORDER (
AD_0_NAME VARCHAR(20),
AD_0_ORDER INTEGER)
RETURNS (N_ERROR INTEGER)
AS
begin
   n_error=0;
   begin
      update ad_0_list
            set ad_0_order = :ad_0_order
          where (ad_0_name = :ad_0_name);
      when any do begin
        n_error=sqlcode;
        exit;
      end
   end
end^

What's beautiful in those few lines (when any ...) is that I have an exception that traps any SQL error, while in InterBase I have to (imagine) and write situations like -803, -625, theoretically from -1 to -999 for every procedure.

I can't fine a way to translate it. Is there a way to do this in InterBase?

Answer 1

InterBase PSQL language has had support for WHEN ANY ... for a long time. See https://docwiki.embarcadero.com/InterBase/2020/en/Handling_Errors and https://docwiki.embarcadero.com/InterBase/2020/en/Examples_of_Error_Behavior_and_Handling

Where it seems to be limited is that it does not recognize SQLCODE as a context variable that you can get a value from for assignment in your code within the WHEN ANY block. It would be a useful enhancement, I agree.

As a sample, the following code works in InterBase, albeit not knowing the exact SQLCODE generated. But, you can catch ANY exception in InterBase PSQL as well.

set echo on; 

/* run some bad statements; see exception code(s) */

/* Expected exception: SQLCODE -413 */
insert into employee (emp_no, first_name, last_name, dept_no, job_code, job_grade, job_country, salary) 
      values (2000, 'Two', 'Thousand', 2000, 'SALES', 2000, 'USA', 1); 
/* Expected exception: SQLCODE -297 */
insert into employee (emp_no, first_name, last_name, dept_no, job_code, job_grade, job_country, salary) 
      values (2000, 'Two', 'Thousand', 'ABC', 'SALES', 2000, 'USA', 1); 
      rollback;

/* Now, do the same with procedures. */
drop procedure TEST_PROC;
COMMIT;

set term ^;
CREATE PROCEDURE TEST_PROC (runcase INTEGER)
RETURNS (N_ERROR INTEGER)
AS
begin
   n_error=0;
   begin
    /* Give bad DEPT_NO value; integral instead of CHAR(3) */
    /* Expected exception: SQLCODE -413 */
    if (:runcase = 1) then
        insert into employee (emp_no, first_name, last_name, dept_no, job_code, job_grade, job_country, salary) 
            values (2000, 'Two', 'Thousand', 2000, 'SALES', 2000, 'USA', 1);

    /* Give bad SALARY value violating a value constraint */
    /* Expected exception: SQLCODE -297 */
    if (:runcase = 2) then
        insert into employee (emp_no, first_name, last_name, dept_no, job_code, job_grade, job_country, salary) 
            values (2000, 'Two', 'Thousand', 'ABC', 'SALES', 2000, 'USA', 1); 

    /* good SQL that will succeed; no exception */
    if (:runcase = 3) then
        INSERT INTO country (country, currency) VALUES ('India', 'Rupee');

    /* check for errors */
    when any do begin
        n_error = :runcase;
        exit;
    end 
   end 
end^

set term ;^
commit;

/* Now test above procedures */
/* see if any work getting done below, by setting count option on */
set count on;
execute procedure test_proc (1);
rollback;
execute procedure test_proc (2);
rollback;
execute procedure test_proc (3);
select * from country where country='India';
rollback;
execute procedure test_proc (99);
rollback;