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

asked 07 May '15, 13:52

louisjoelhc's gravatar image

louisjoelhc
126458
accept rate: 0%


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

permanent link

answered 07 May '15, 16:20

Mark%20Culp's gravatar image

Mark Culp
24.9k10141297
accept rate: 41%

edited 07 May '15, 16:24

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

(08 May '15, 09:15) Volker Barth

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

(08 May '15, 11:03) louisjoelhc

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"
permanent link

answered 07 May '15, 17:13

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

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.

(08 May '15, 11:07) louisjoelhc
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×95

question asked: 07 May '15, 13:52

question was seen: 2,005 times

last updated: 08 May '15, 11:07