Hey All:

I am trying to call a stored proc that returns a result set but am getting an error. See below. Thanks in advance for any help.

Rance

-------------------------------
My Stored Proc code...
-------------------------------

CREATE PROCEDURE xxx.GETMyRecords ( )
DYNAMIC RESULT SETS 1
LANGUAGE SQL

BEGIN
DECLARE CLIENTCUR CURSOR WITH RETURN TO CALLER
FOR SELECT * FROM xxx.Mytable ;
OPEN CLIENTCUR ;
END

--------------------------------------------------------------
What I am Calling from SQL Script in iSeries navigator...
--------------------------------------------------------------

DECLARE result1 RESULT_SET_LOCATOR VARYING;

CALL xxx.GETMyRecords ();

ASSOCIATE RESULT SET LOCATORS(result1)
WITH PROCEDURE xxx.GETMyRecords ;

ALLOCATE rsCur CURSOR FOR RESULT SET result1;

OPEN rsCur;

-------------------------------
The error I am getting is...
-------------------------------

Processing ended because the highlighted statement did not complete successfully

> DECLARE result1 RESULT_SET_LOCATOR VARYING

SQL State: 42601
Vendor Code: -104
Message: [SQL0104] Token RESULT_SET_LOCATOR was not valid. Valid tokens: DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. Cause . . . . . : A syntax error was detected at token RESULT_SET_LOCATOR. Token RESULT_SET_LOCATOR is not a valid token. A partial list of valid tokens is DYNAMIC SENSITIVE ASENSITIVE INSENSITIVE. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token RESULT_SET_LOCATOR. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.

Do you call this procedure on an iSeries system? Have you verified that DB2 for iSeries supports variables of type RESULT_SET_LOCATOR?

DB2 for LUW does, but that's a different product.

Sorry I should have mentioned I'm new to DB2. I come from a SQL Server background. How would I go about checking if iSeries supports it? And if not, how can I go about returning a resultset from a DB2 stored procedure on an iSeries. Thx