Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    3

    Unanswered: Error related to Dynamic scrollbale cursor stored procedure in DB2

    Hi All,

    Version:DB2 v8.1.9.700 and FixPak 9

    I have created Dynamic scrollable cursor Stored procedure as below in Command Editor:

    CREATE PROCEDURE "QA"."ravisample"(IN P_FIRSTNAME VARCHAR(20))
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN

    DECLARE OutCrsr SENSITIVE DYNAMIC SCROLL CURSOR with return FOR
    SELECT LASTNAME, FIRSTNAME FROM CONTACTS2
    WHERE FIRSTNAME LIKE P_FIRSTNAME ;

    OPEN OutCrsr;

    END

    When executed above SP in Command editor following error message is displayed which is not helping to resolve issue:

    CREATE PROCEDURE "QA"."ravisample"(IN P_FIRSTNAME VARCHAR(20))
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN


    DECLARE OutCrsr SENSITIVE DYNAMIC SCROLL CURSOR with return FOR
    SELECT LASTNAME, FIRSTNAME FROM CONTACTS2
    WHERE FIRSTNAME LIKE P_FIRSTNAME
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "AME LIKE
    P_FIRSTNAME". Expected tokens may include: "ESCAPE <escape_char>". LINE
    NUMBER=11. SQLSTATE=42601

    OPEN OutCrsr
    DB21028E The cursor "OUTCRSR" has not been declared.

    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string
    for the SYSPROC.ADMIN_CMD procedure was detected at the specified
    token following the text "<text>". The "<text>" field indicates
    the 20 characters of the SQL statement or the input command
    string for the SYSPROC.ADMIN_CMD procedure that preceded the
    token that is not valid.

    As an aid, a partial list of valid tokens is provided in the
    SQLERRM field of the SQLCA as "<token-list>". This list assumes
    the statement is correct to that point.

    The statement cannot be processed.

    User Response:

    Examine and correct the statement in the area of the specified
    token.

    sqlcode : -104

    sqlstate : 42601

    Can any one have idea where it went wrong?

    Thanks in advance

    Ravindra

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You have to make sure that the entire CREATE PROCEDURE statement is processed by DB2 as a single statement, which is not happening in this case since there are default statement delimiters, semicolons, in the procedure text.

    Put a different delimiter (e.g. "@" is commonly used) at the end of the CREATE PROCEDURE statement and make sure you update the delimiter character in the command editor.

    Better yet, put the whole thing into a text file and execute it with the DB2 command processor:

    db2 -td@ -f myfile.sql.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Jun 2008
    Posts
    3
    Hi,

    Thanks for looking into the problem suggesting solution.

    I have added delimiter '@' at then end and on execution in Db2 Command Processor following error message is displayed:

    " DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0104N An unexpected token "<space>" was found following "OUTCRSR". Expected tokens may include: "SENSITIVE". LINE NUMBER=8. SQLSTATE=42601"

    Updated SP:

    CREATE PROCEDURE "QA"."ravisample1"(IN P_FIRSTNAME VARCHAR(20))
    DYNAMIC RESULT SETS 1
    READS SQL DATA
    NOT DETERMINISTIC
    LANGUAGE SQL
    BEGIN

    DECLARE OutCrsr SENSITIVE DYNAMIC SCROLL CURSOR FOR SELECT LASTNAME, FIRSTNAME FROM CONTACTS2 WHERE FIRSTNAME LIKE P_FIRSTNAME;
    OPEN OutCrsr;
    END@

    Can you please look into the SP and let me know if there is any wrong with SP.

    Thanks in Advance,
    Ravindra

  4. #4
    Join Date
    Jun 2008
    Posts
    3
    Any one have clue regarding above error message

Posting Permissions

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