can't trap exception in SPL
I've just started working with SPL and I haven't been able to get
exception handling to work. Given this pared-down SQL:
------------------------------------------------------------------
-- tested with IDS 11.70.FC8GE, isql 7.51.FC1XD, sqlcmd 88.00 on RHEL 5.11
drop procedure if exists update_test;
create procedure update_test();
define s char(255);
begin work;
foreach update_c for
select desc into s
from oglo_sku
where desc = 'QUUX'
on exception
let s = 'oops';
end exception;
update oglo_sku
set desc = 'FOO'
where current of update_c;
end foreach;
commit work;
end procedure;
execute procedure update_test();
drop procedure if exists update_test;
------------------------------------------------------------------
1. If I try to run it with isql, it won't even parse:
dibbler% sudo-dba isql pactrak t.sql
Routine dropped.
201: A syntax error has occurred.
Error in line 6
Near character position 29
zsh: exit 255 sudo-dba isql pactrak t.sql
dibbler% _
2. With sqlcmd it runs but the exception isn't caught. The following is
with one of the rows locked in a different window:
dibbler% sudo-dba sqlcmd -d pactrak -f t.sql
SQL -244: Could not do a physical-order read to fetch next row.
ISAM -107: ISAM error: record is locked.
SQLSTATE: IX000 at t.sql:28
zsh: exit 1 sudo-dba sqlcmd -d pactrak -f t.sql
dibbler% _
Thanks for any help.
Roderick
Original post:
<stuff removed>
1. If I try to run it with isql, it won't even parse:
dibbler% sudo-dba isql pactrak t.sql
Routine dropped.
201: A syntax error has occurred.
Error in line 6
Near character position 29
zsh: exit 255 sudo-dba isql pactrak t.sql
dibbler% _
2. With sqlcmd it runs but the exception isn't caught. The following is
with one of the rows locked in a different window:
dibbler% sudo-dba sqlcmd -d pactrak -f t.sql
SQL -244: Could not do a physical-order read to fetch next row.
ISAM -107: ISAM error: record is locked.
SQLSTATE: IX000 at t.sql:28
zsh: exit 1 sudo-dba sqlcmd -d pactrak -f t.sql
dibbler% _
Thanks for any help.
Roderick
Response:
1) Yes the SQL parser of ISQL was not updated with the enhanced SQL syntax
required for SPL. So you have to create SPL using dbaccess (comes with the
server), or some other front end tool that just sends the create procedure
statement over to the server as a statement to prepare (on the engine) and
then execute the prepared statement.
2) I think you just have the on exception block too late in your SPL. The
error that is being generated is by the select of the foreach, but your on
exception is inside the foreach. So I think it's too late. For the select
portion of your foreach to have the exception caught, the on exception block
needs to be outside/prior to your foreach statement. The way you currently
have it written, the update where current of (and I guess your commit) is the
only statement that would be caught by the exception.
When I changed your code to this:
drop procedure if exists update_test;
create procedure update_test()define s char(255);
on exception
let s = 'opps';
end exception;
begin work;
foreach update_c for
select desc into s
from oglo_sku
where desc = 'QUUX'
update oglo_sku
set desc = 'FOO'
where current of update_c;
end foreach;
commit work;
end procedure;
execute procedure update_test();
I'm able to execute the procedure and get no errors, even though I don't even
have the table oglo_sku. If I remove the on exception code, the spl errors out
with a 206 error.
Jacques Renaut
Informix Support
HCL Americas
Thank you so much! This works perfectly, and more importantly makes sense to me. Roderick