"Invalid cursor state" when running DELETE as last statement of stored procedure - 09-01-2005 , 03:18 PM
Using Pervasive 8.6:
I'm writing a stored procedure that is supposed to return a result set.
The proc creates new rows in an existing table, uses them in a join
with another table, and then deletes them. This is the error I get:
ODBC Error: SQLSTATE = S1000, Native error code = -5099
Invalid cursor state.
Is it not possible to run another statement after the SELECT that is
supposed to return the result set?
Here is a schematic version of the proc (full version uses other stored
procs for insert and delete):
CREATE PROCEDURE Search(:QueryID INTEGER) RETURNS (Url CHAR(128))
-- Add synonyms to the search query's keywords
INSERT INTO QueryKeywords (Keyword)
SELECT Value FROM Keywords WHERE...;
-- Run the search query, joining with another table
SELECT ImageKeywords.Url FROM QueryKeywords
INNER JOIN ImageKeywords
ON ImageKeywords.Keyword = QueryKeywords.Keyword
GROUP BY ImageKeywords.Url;
-- Get rid of the synonyms, causes error
-- When this line is removed proc succeeds
DELETE FROM QueryKeywords WHERE ...;
Thanks for any help.
Re: "Invalid cursor state" when running DELETE as last statement of stored procedure - 09-01-2005 , 05:07 PM
Hmm, actually, when I posted the above message I was still using an
actual DELETE statment. After that I updated the search proc to use a
"delete" stored proc instead of a raw statement(essentially the same
statement encapsulated in a proc) and it works. Yet another item on the
Pervasive bug list?
Re: "Invalid cursor state" when running DELETE as last statement of stored procedure - 09-08-2005 , 04:08 PM
I find bugs in Pervasive.SQL all the time and they are usually very good
about fixing them. I'd suggest that you report it, because if you don't,
the problem will never get fixed.
"Fardreamer" <doron.assayas (AT) gmail (DOT) com> wrote
Re: "Invalid cursor state" when running DELETE as last statement of stored procedure - 09-14-2005 , 09:51 AM
Quick update: using a secondary stored proc for cleanup in a similar
situation in another proc works well in the PCC but generates a
"specified record address is invalid(Btrieve Error 43)" when using the
I notified Pervasive of all the bugs I encountered like you recommended.