Search code examples
crashcursorsybasesap-ase

Sybase ASE for update cursor that crashes


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.


Solution

  • 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"