Firebird: Cursor not returned from query

[Hardee Mahoney]

Hello,

I have a stored procedure that I want to return results to a TSQLDataSet.
When I execute the stored procedure in the TSQLDataSet I get the exception
"Cursor not returned from query".

Here is my stored procedure which I'm pretty sure is the correct syntax -
executes in isql:

CREATE PROCEDURE usp_get_id
RETURNS (Id SMALLINT)
AS
BEGIN
FOR SELECT id, name
FROM cancers
ORDER BY name
INTO :id, :name
DO
BEGIN
SUSPEND;
END
END

Here is my TSQLDataSet:

SDS.SQLConnection := DM.fbConnection;
SDS.CommandType := ctStoredProc;
SDS.CommandText := 'USP_GET_ID';
SDS.Open;

The exception is thrown at the open.

Any help is appreciated.

Thanks
Hardee Mahoney
Washington, DC

[Bill Todd [TeamB]]

You have created a selectable stored procedure. To call a selectable
stored procedure you treat it as though it were a table. For example:

SELECT * FROM usp_get_id

You need to set the command type to ctText and set CommandText to the
SELECT statement above.

[Hardee Mahoney]

Thank you very much.

Post by Bill Todd [TeamB]
You have created a selectable stored procedure. To call a selectable
SELECT * FROM usp_get_id
You need to set the command type to ctText and set CommandText to the
SELECT statement above.
--
Bill Todd (TeamB)

[BRebey]

Bill,

I have a similar problem with a BCB app. I can't get a Cursor back.

The trouble appears to be with my stored procedure doing 2 "select"s. In
the procedure, the first "select" is performed, it's results are manipulated
to create a dynamic query, then that query is run as the second "select".
The procedure works like this:

insert into #tempTable
select <whatever from wherever>
set @query = <stuff based on the resutl of the first query>
execute sp_executesql @query

In the above case, I get the "no cursor returned" error when I activate my
TSQLDataSet object.

If I remove the "insert into" line and just do the two queries, I **do not
get the error**, but instead get back the result set from the *FIRST* query,
rather than the LAST query.

I tried "fooling" the system by wrapping the first select with a secondary
stored procedure and calling that procedure instead of calling the select
directly, but that didn't fool anyone. I got identical results.

How can I get the results back from the SECOND (or Nth) query?

Post by Bill Todd [TeamB]
You have created a selectable stored procedure. To call a selectable
SELECT * FROM usp_get_id
You need to set the command type to ctText and set CommandText to the
SELECT statement above.
--
Bill Todd (TeamB)

[Bill Todd [TeamB]]

You have posted to a very old thread dealing with Firebird yet your
code suggests you are using SQL Server. Please a new thread with an
appropriate subject and identify the database you are using.