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.
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 18.104.22.1688 and would like to stay with this version for a while...
asked 07 May '15, 13:52
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.
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"
answered 07 May '15, 17:13