Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Cursor

  1. #1
    Join Date
    Jun 2010
    Posts
    8

    Unanswered: Cursor

    I just start to use Db2 Cursor, I was trying to do a simple exemple,


    DECLARE C1 Cursor for
    select CHANGE_ID from CHANGES
    ORDER BY START_DATE;

    OPEN C1;

    FETCH C1 INTO :ch_id;

    But it give me this error, what's wrong??

    FETCH C1 INTO :ch_id
    SQL0104N An unexpected token "INTO" was found following "<identifier>".
    Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

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

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    Andy

  3. #3
    Join Date
    Jun 2010
    Posts
    8
    Db2 9
    Windows XP

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Syntactically it looks OK. Can you post the entire code? There maybe a problem elsewhere that is causing this.

    Andy

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Is this code in a stored procedure?

    Andy

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by Rafaelcl View Post
    I just start to use Db2 Cursor, I was trying to do a simple exemple,


    DECLARE C1 Cursor for
    select CHANGE_ID from CHANGES
    ORDER BY START_DATE;

    OPEN C1;

    FETCH C1 INTO :ch_id;

    But it give me this error, what's wrong??

    FETCH C1 INTO :ch_id
    SQL0104N An unexpected token "INTO" was found following "<identifier>".
    Expected tokens may include: "END-OF-STATEMENT". SQLSTATE=42601

    SQL0104N An unexpected token "INTO" was found following "<identifier>". Expected tokens may include: "END-OF-STATEMENT".
    Take a look on your code:
    Code:
    DECLARE C1 Cursor for
    select CHANGE_ID from CHANGES
    ORDER BY START_DATE;
    Where you use START_DATE on SELECT ?

    Also, if you are coding for Mainfraime you have to use:

    Code:
    EXEC SQL 
      DB2 statement
    END-EXEC
    Lenny
    Last edited by Lenny77; 06-15-10 at 16:02.

  7. #7
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Quote Originally Posted by ARWinner View Post
    Is this code in a stored procedure?

    Andy
    That's the real question here. If it is in external code (e.g. C/C++), things are fine and should work. Otherwise (in a stored procedure or on the DB2 console), you must not have a ':'. The colon is just an indicator in embedded SQL to tell the DB2 precompiler that a host variable follows.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  8. #8
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    Where you use START_DATE on SELECT ?
    don't need it. vsn7 or 8 and above on mainframe.
    Dick Brenholtz, Ami in Deutschland

  9. #9
    Join Date
    Jun 2010
    Posts
    8
    It's in a stored procedure. What should I change??

  10. #10
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Like this:

    Code:
    DECLARE CH_ID INT;
    
    DECLARE C1 Cursor for
    select CHANGE_ID from CHANGES
    ORDER BY START_DATE;
    
    OPEN C1;
    
    FETCH C1 INTO CH_ID;
    Andy

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Removed because of my misunderstandings.
    Last edited by tonkuma; 06-15-10 at 18:49. Reason: Removed because of my misunderstandings.

  12. #12
    Join Date
    Jun 2010
    Posts
    8
    I think it's some problem in my version. I try to execute the procedure I got on the IBM site , DB2 Universal Database, on the command editor. I create the staff table, and I haven't change anything on the code.

    But still gives me errors like that:
    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 "ecords
    INT DEFAULT 1". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=5. SQLSTATE=42601

    DECLARE c2 CURSOR WITH RETURN FOR SELECT name, job, CAST(salary AS INTEGER) FROM staff WHERE salary > medianSalary ORDER BY salary
    SQL0104N An unexpected token "RETURN" was found following "WITH". Expected
    tokens may include: "HOLD". SQLSTATE=42601


    Here is the code

    CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
    RESULT SETS 1
    LANGUAGE SQL
    BEGIN
    DECLARE v_numRecords INT DEFAULT 1;
    DECLARE v_counter INT DEFAULT 0;

    DECLARE c1 CURSOR FOR
    SELECT CAST(salary AS DOUBLE)
    FROM staff
    ORDER BY salary;
    DECLARE c2 CURSOR WITH RETURN FOR
    SELECT name, job, CAST(salary AS INTEGER)
    FROM staff
    WHERE salary > medianSalary
    ORDER BY salary;

    DECLARE EXIT HANDLER FOR NOT FOUND
    SET medianSalary = 6666;

    SET medianSalary = 0;
    SELECT COUNT(*) INTO v_numRecords
    FROM STAFF;
    OPEN c1;
    WHILE v_counter < (v_numRecords / 2 + 1)
    DO
    FETCH c1 INTO medianSalary;
    SET v_counter = v_counter + 1;
    END WHILE;
    CLOSE c1;
    OPEN c2;
    END e code:

  13. #13
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What command are you issuing to create the stored procedure?

    Andy

  14. #14
    Join Date
    Jun 2010
    Posts
    8
    I am trying to run that on the command Editor

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    The command editor has a default terminator of ";", but within a SP there are many semi-colons that do not signify the end of the SP. What you want is one SP source code with semi-colons after certain lines of code, and a different terminator at the very end (such as "@"). The when you create the SP you will need to specify a different terminator, for example:

    db2 -td@ -f input_file.sql > output_file.out
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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