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))
AS
BEGIN
-- 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
WHERE ...
GROUP BY ImageKeywords.Url;

-- Get rid of the synonyms, causes error
-- When this line is removed proc succeeds
DELETE FROM QueryKeywords WHERE ...;
END;


Thanks for any help.

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?

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.

http://www.pervasive.com/support/submit_defect.asp

Regards,
- Greg

"Fardreamer" <doron.assayas (AT) gmail (DOT) com> wrote

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
..NET provider.

I notified Pervasive of all the bugs I encountered like you recommended.