I run a Stored Procedure. After a while i cancel the execution and try to start it again. After that I always got a error message.
SQLCODE -180 Constant SQLE_CURSOR_NOT_OPEN SQLSTATE 24501 Sybase error code 559 ODBC 2 State 34000 ODBC 3 State 34000
I can't start the procedure anymore ????
asked 28 Jun '12, 02:33
SQLCODE -180 (SQLE_CURSOR_NOT_OPEN) means "You attempted to use a cursor that has not been opened.".
When you cancel the procedure's execution, it raises an error (SQLCODE -299, "Statement interrupted by user"). Without explicit error handling, I assume that the procedure just stops within its control flow -cf. the following quote from the SA 12 docs.
However, it should close its cursors on exiting, and should be able to be restarted afterwards (or from a parallel connection).
Here is a sample Watcom-SQL procedure for the SA 12 demo database that scrolls through a cursor and pauses 3 seconds after each row. When you cancel the execution, you can still restart it without problems:
CREATE OR REPLACE PROCEDURE STP_Test() begin DECLARE err_notfound EXCEPTION FOR SQLSTATE VALUE '02000'; DECLARE cur_employee CURSOR FOR SELECT Surname FROM Employees; DECLARE name CHAR(40); OPEN cur_employee; lp: LOOP FETCH NEXT cur_employee INTO name; MESSAGE 'Surname: ' || name TO CLIENT; WAITFOR DELAY '00:00:03'; IF SQLCODE <> 0 THEN LEAVE lp END IF; END LOOP; CLOSE cur_employee; MESSAGE 'STP_Test finished' TO CLIENT; end; CALL STP_Test(); -- cancel after some seconds from DBISQL CALL STP_Test(); -- run again, should work
Given this works as expected, I would conclude that there's a problem with the logic in the cursor loop itself inside your procedure (why would you cancel it anyway?).
Have you tried to use an UPDATE without cursor (as suggested in the other FAQ)?
FWIW, v12 has introduced the sa_list_cursors() system procedure. That might help to test whther the cursor remains declared and open after the procedure's execution:
EDIT: The real problem might be due to cursor behaviour on commit:
With the default setting of option close_on_endtrans (ON), the COMMIT in your procedure will close the cursor. That surely explains the error message - and should even appear for a "normal" (i.e. uncancelled) execution. You might use WITH HOLD in the cursor declaration or defer the COMMIT after the CLOSE statement...