I just faced a strange behaviour of ASE (version 15.7 ESD 15.2) using a for update cursor.
Finally i found the root cause, but i thougth about posting it here because it can be usefull, and maybe someone faced it already.
I wrote this simple "for update" cursor :
DECLARE c_contactrule
CURSOR FOR
SELECT a.id
FROM FTContactRule a, Client b, ClientContact c
WHERE /* join conditions */
AND a.client_id IN ( bla bla bla )
AND a.message_subtype_id ( bla bla bla )
AND /* and so on */
for update of a.preferred_medium_id
go
(I summarized a bit the query for this post, to be more readable )
Hereafter, of course, i read the cursor value until the end.
open c_contactrule
fetch c_contactrule into @rule_to_update
while @@sqlstatus = 0
begin
/* do something */
end
close c_contactrule
deallocate c_contactrule
go
I think you guessed that there are somewhere an update preferred_medium_id where current of
Currently, this query crashes in a very specific way : the message thrown is "ASE termintaed this process", and indeed, he killed my session!
Investigating, i found that the bad line of code was the first fetch into
statement.
I finally found what cause this error message, but i still think it is a bug since the crash is a reaction too hard from the server. There should be a more user-friendly message or a parse-error, but not a silent kill.
Anyway, my tests demonstrates that the problem is due to the fact that i update a field which is not in the select statement of the cursor :
select a.id
....
for update of a.preferred_medium_id
I just added the field in the select list, and of course i added a variable in the fetch into and it worked fine.
select a.id, a.preferred_medium_id
....
for update of a.preferred_medium_id
However, it is somewhat strange to have to hold a value in a variable when you don't need to do anything with it.
Also, killing the session is not a good response from the server. If there is a syntax rule for this, then we should have a parse error saying "You cannot update a field wich is not in you select list for un update cursor"