Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2007
    Posts
    2

    Unanswered: Calling Stored Proc that returns resultset

    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.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Aug 2007
    Posts
    2

    New to DB2

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •