Error handling calling procedures in select statement

[Former Member]

Hi,

We are trying to add error management in our database procedures using the exception clause. We found that some errors are not handled depending on how the procedure is called. It seems that when a procedure is called in a select statement, the calling procedure does not handle the errors from the called procedure.

In the following example, the procedure "proc_2" cause an error "Variable 'tst' not found". We expect procedure "proc_1" to be able to handle this error but it is not the case. The error is returned to the calling application as expected, but the exception handling in "proc_1" does not handle it. Again, this issue seems to happen only when the procedure is called in a select statement.

create or replace procedure dba."proc_1"()
result (res_ok char(2))
begin
  select res_ok from proc_2();

  exception
    when others then
      message 'Exception in proc_1' to console;
      resignal;
end;

create or replace procedure dba."proc_2"()
result (res_ok char(2))
begin
  set tst = '3';
  select 'OK';

  exception
    when others then
      message 'Exception in proc_2' to console;
      resignal;
end;

We have a lot of procedure calls in select statements and we want to be able to handle every database errors...

Is there a way to handle the error in the calling procedure in this scenario?

We use SQL Anywhere version 16.0.0.1948 and would like to stay with this version for a while...

Thanks

[MarkCulp · Accepted solution]

The problem that you are seeing is a misconception that many have stumbled on.

Your Proc_1 procedure is returning a result set back to the client so the client must open a client-side cursor to consume this result set. When the cursor is opened the Proc_1 procedure is executed up to the point that the statement for the first result set is reached at which point it prepares (but does not execute) the statement. The database engine then stops and returns control back to the client. The client then attempts to fetch the first row of the result set and control goes back to the database engine to get the first row. The engine then executes the statement that has been prepared (and this is done independent of the procedure execution). To get the first (and only row in this case) of the result set it then executes Proc_2 and hits the exception (which is caught by the exception clause in Proc_2 and resignaled) ... BUT since the execution of the procedure is effectively being done by the client the exception goes back to the client and does not get caught by the exception clause in Proc_1.

Note that this behaviour is different than other DBMSes because SQLA generates results sets "on demand" whereas other RDMSes execute procedures completely - to their end/return points - generating any and all result sets in their totality before returning control to the client.

HTH

[VolkerBarth · Accepted solution]

@Mark: Just as you point out that this is a common misconception (and works different for other DBMSes) - do the docs talk about these concepts? That would be helpful...

[Former Member · Accepted solution]

Thanks! That allow us to understand many things. I will share this information with my team.

[Breck_Carter]

In addition to Mark's explanation, here is how to deal with the problem: SELECT INTO a local variable (or table), then SELECT the return result set in proc_1.

create or replace procedure dba."proc_1"()
result (res_ok char(2))
begin
  declare @return_res_ok char(2);

  select res_ok into @return_res_ok from proc_2();

  select @return_res_ok;

  exception
    when others then
      message 'Exception in proc_1' to console;
      resignal;
end;

create or replace procedure dba."proc_2"()
result (res_ok char(2))
begin
  set tst = '3';
  select 'OK';

  exception
    when others then
      message 'Exception in proc_2' to console;
      resignal;
end;

SELECT * FROM proc_1();

Exception in proc_2
Exception in proc_1

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.

Variable 'tst' not found
SQLCODE=-260, ODBC 3 State="42000"

[Former Member]

Thanks for the tip! This will involve many changes in our system but I guess we don't have other choices to improve our error handling.