Hi All,

I am getting the following error:

The command did not return a dataset that is usable with a data reader

When I attempt to retrieve a resultset from the following Stored Procedure:

CREATE PROCEDURE "proc_GetDocumentsForProject" (IN "pProjectGUID" VARCHAR(50) COLLATE UNI)
BEGIN
EXECUTE IMMEDIATE 'SELECT * FROM IDDocumentStore WHERE ProjectGUID = ?' USING pProjectGUID;
END

Not sure if I have done this right or if I need to use a Cursor, any help would be appreciated.

The Query isn't complex and have tried with both a dbDataReader and the EDBDataReader, an explaination would be good, thanks for your help.


Cheers

Ivan

>Not sure if I have done this right or if I need to use a Cursor, any help would be appreciated.

You do need to use a CURSOR. EXECUTE IMMEDIATE runs a simple sql statement in a self contained fashion and there is nothing to pass back to your application.

CREATE PROCEDURE "proc_GetDocumentsForProject" (IN "pProjectGUID" VARCHAR(50) COLLATE UNI)
BEGIN
DECLARE ShowResults SENSITIVE CURSOR WITH RETURN FOR stmt;
PREPARE stmt FROM 'SELECT * FROM IDDocumentStore WHERE ProjectGUID = '+ QUOTEDSTR(pProjectGUID);
OPEN ShowResults;
END

You could use a parameter if you wanted, but in simple cases I prefer to use the supplied data directly. If you want an insensitive result alter SENSITIVE to INSENSITIVE. SENSITIVE should be faster, but you might want to prevent people from changing it.

Roy Lambert [Team Elevate]

Roy Lambert wrote:

Ivan

>Not sure if I have done this right or if I need to use a Cursor, any help would be appreciated.

You do need to use a CURSOR. EXECUTE IMMEDIATE runs a simple sql statement in a self contained fashion and there is nothing to pass back to your application.

CREATE PROCEDURE "proc_GetDocumentsForProject" (IN "pProjectGUID" VARCHAR(50) COLLATE UNI)
BEGIN
DECLARE ShowResults SENSITIVE CURSOR WITH RETURN FOR stmt;
PREPARE stmt FROM 'SELECT * FROM IDDocumentStore WHERE ProjectGUID = '+ QUOTEDSTR(pProjectGUID);
OPEN ShowResults;
END

You could use a parameter if you wanted, but in simple cases I prefer to use the supplied data directly. If you want an insensitive result alter SENSITIVE to INSENSITIVE. SENSITIVE should be faster, but you might want to prevent people from changing it.

Roy Lambert [Team Elevate]


Thankyou Roy very much, I am sorry to bother, just real new to this Product, slowly learning with every Post and the above worked well your help and this community is well worth the cost of the product, no doubt I will Post again. Cheers and thanks again.