Search code examples
sap-ase

Sybase ASE deallocate cursor if it exists

Say I have

declare c cursor for select * from table

If I ran the above twice, it would fail with

There is already another cursor with the name 'c' at the nesting level '<pointer>'

I can prevent this with

declare c cursor for select * from table
...
deallocate c

However, if the cursor takes long to execute and I interrupt it mid-work, the cursor will remain and will fail again on re-run.

What I'm after is to do something like

if cursor_exists(c) deallocate c
declare c cursor for select * from table
...

Is that possible with Sybase ASE?

Solution

  • If running ASE 16.0 SP02 PL05 (or later) you have access to the cursorinfo() function. [NOTE: While SAP, for some unknown reason, removed this function from later reference manuals the function does in fact exist in newer versions of ASE.]

    Of particular interest is the first bullet under the Usage section:

    enter image description here

    Testing the 'status' option:

    -- cursor 'c' does not exist at this point

select cursorinfo('c','status')
go

 ---------------------------------- 
 NULL 

declare c cursor for select * from sysobjects
go

select cursorinfo('c','status')
go

 ---------------------------------- 
 COMPILE_NEEDED, DECLARED, LANGUAGE

    One idea for incorporating cursorinfo() into OP's requirement for conditionally running the deallocate [cursor] command:

    if cursorinfo('c','status') is not NULL deallocate c

    NOTE: above code was verified against an ASE 16.0 SP04 PL04 instance

    If OP is running a pre-16.0 SP02 PL05 version of ASE I'm not aware (off the top of my head) of a 'clean' way of performing the same operation without unwanted output, ie:

    • sp_cursorinfo (a wrapper for dbcc cursorinfo()) does not have a means of suppressing dbcc output messages nor does the stored proc provide any sort of usable return code
    • I'm not aware of any other functions, system store procs, global variables nor system tables that could be used to ascertain the existence of a cursor
    • you could just ignore (??) the error message generated when running deallocate [cursor] c (when said cursor doesn't exist); the command line isql tool will print the message and continue running whatever else may be in the batch, but I don't know how some GUIs would react to the error message