Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Question Unanswered: Cursor not working

    I have the following script. very simple just to see if this will work but unfortunately, I was not able to make it work. Care to tell me what is wrong?


    ==========================================
    DECLARE GLOBAL TEMPORARY TABLE T1_TBL
    ( ID BIGINT,
    NAME VARCHAR(200)
    )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS;

    INSERT INTO session.T1_TBL
    values(1,'grapes');
    INSERT INTO session.T1_TBL
    values(3,'apples');
    INSERT INTO session.T1_TBL
    values(3,'orange');


    DECLARE C1 CURSOR FOR
    SELECT name, id
    FROM session.T1_TBL
    FOR UPDATE OF name;
    OPEN C1;
    myLoop:
    LOOP
    fetch C1 INTO :name, :id;
    if :id = 3 then
    UPDATE session.T1_TBL
    SET name = :name concat 'Alive';
    end if;
    END LOOP myLoop;
    close C1;
    commit;
    ==========================================




    Running this script gave me the following error:

    ===============================

    myLoop: LOOP fetch C1 INTO :name, :id
    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 "myLoop" was found following
    "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>".
    SQLSTATE=42601

    SQL0104N An unexpected token "myLoop" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values> ".
    ===============================


    I just want to make it work and see how cursor works in reading the resultset per row.

    Please help. Many thanks.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think LOOP cannot be used in the dynamic SQL context; it can only be a part of a procedure.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    11
    Thank you for your reply!

    Ok I tried to put everything in a temp SP:

    ==============================================

    CREATE PROCEDURE session.T1_SPx(OUT counter INTEGER)
    LANGUAGE SQL
    BEGIN
    DECLARE C1 CURSOR FOR
    SELECT name, id
    FROM session.T1_TBL
    FOR UPDATE OF name;
    OPEN C1;
    myLoop:
    LOOP
    fetch C1 INTO :name, :id;
    if :id = 3 then
    UPDATE session.T1_TBL
    SET name = :name concat 'Alive';
    end if;
    END LOOP myLoop;
    close C1;
    commit;
    END
    ==============================================



    And it gives me an error


    ==============================================
    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 "L FOR
    UPDATE OF name". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=7. SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "L
    FOR UPDATE OF name". Expected tokens may include: "<psm_semicolon> ".
    ==============================================



    I just want to know how I can make this FOR LOOP statement work. Many thanks!!!

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Feb 2010
    Posts
    11
    Thank you for the prompt reply!

    The problem is similar to mine. I did this the moment I received your reply

    ================================
    CREATE PROCEDURE session.T1_SPx()
    LANGUAGE SQL
    BEGIN
    DECLARE sname VARCHAR(50);

    END@
    ================================


    But still getting an error

    ================================
    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 "RE sname
    VARCHAR(50)". Expected tokens may include: "<psm_semicolon>". LINE
    NUMBER=4. SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "RE sname VARCHAR(50)". Expected tokens may include: "<psm_semicolon> ".

    ================================


    Sorry but I am novice in using cursors in DB2. I have this script that should update a table and got a problem and my solution is I need to iterate the records in order for me to update the table correctly. Reason I want to use a cursor or control statements to achieve my goal.

    The logic that I would like to do in my script is similar to this one below

    ================================
    FOR v1 AS
    c1 CURSOR FOR
    SELECT name, id
    FROM session.T1_TBL
    DO
    SET sname = name concat 'Hi';
    UPDATE session.T1_TBL
    SET name = sname;
    END FOR;
    ================================

    And as you know it is not working. I really need help how this cursor works in DB2.


    Honestly, I really appreciate your help. And I hope sooner or later I can make this work with your help.


    [-]

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I did debug of your code
    on Command Editor of DB2 9.7 on Windows/XP.

    Code:
    ------------------------------ Commands Entered ------------------------------
    CREATE PROCEDURE session.T1_SPx() 
    LANGUAGE SQL 
    BEGIN 
    DECLARE GLOBAL TEMPORARY TABLE T1_TBL
    ( ID BIGINT,
    NAME VARCHAR(200)
    )
    WITH REPLACE
    ON COMMIT PRESERVE ROWS;
    
    INSERT INTO session.T1_TBL
    values(1,'grapes');
    INSERT INTO session.T1_TBL
    values(2,'apples');
    INSERT INTO session.T1_TBL
    values(3,'orange');
    
    BEGIN
    DECLARE v_name VARCHAR(200);
    DECLARE v_id   BIGINT;
    DECLARE C1 CURSOR FOR
    SELECT name, id
      FROM session.T1_TBL
    FOR UPDATE OF name;
    OPEN C1;
    myLoop:
    LOOP
       fetch C1 INTO v_name, v_id;
       if v_id = 3 then
          UPDATE session.T1_TBL
             SET name = v_name concat 'Alive'
           WHERE CURRENT OF c1;
          LEAVE myLoop;
       end if;
    END LOOP myLoop;
    close C1;
    commit;
    END;
    
    END@
    ------------------------------------------------------------------------------
    DB20000I  The SQL command completed successfully.
    
    ------------------------------ Commands Entered ------------------------------
    CALL session.T1_SPx()@
    ------------------------------------------------------------------------------
    
      Return Status = 0
    
    ------------------------------ Commands Entered ------------------------------
    SELECT * FROM session.T1_TBL@
    ------------------------------------------------------------------------------
    
    ID                   NAME                                                                                                                                                                                                    
    -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                       1 grapes                                                                                                                                                                                                  
                       2 apples                                                                                                                                                                                                  
                       3 orangeAlive                                                                                                                                                                                             
    
      3 record(s) selected.

  7. #7
    Join Date
    Feb 2010
    Posts
    11
    Hi Tonkuma!

    Thank you for your reply!

    I did try it and it gives me an error.


    =================================
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "COMMIT PRESERVE ROWS". Expected tokens may include: "<psm_semicolon> ".
    =================================

    I executed the script using the Query Command from the DB2 Control Center for Windows.

    Thank you very much for future help!

    [-]

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    See the link provided by Nick.

    See if this helps: bonehead question
    __________________
    ===
    Nick Ivanov
    Then read the description and the following example...
    CREATE PROCEDURE is a single [compound] statement, up the the final END, so the first statement delimiter should appear right after the END, otherwise the statement will appear as syntactically incorrect to the parser. Now, you also have to delimit the statements _inside_ the CREATE PROCEDURE statement. The way out is to redefine the statement delimiter and place the _new_ delimiter at the end of the CREATE statement, while leaving alone the "internal" delimiters.

    Thusly,
    Code:
    .......

  9. #9
    Join Date
    Feb 2010
    Posts
    11
    @tonkuma:
    Now, what I don't understand is that your script works fine when you try to execute it and not for me if all these things discussed here should solve my problem.

    Is this a version limitation too? I am currently using DB2 v9.1.
    I feel so hopeless.

    [-]

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that it would be not version limitation, but not sure.

    What error message(s) did you got by creating procedure on your Command Editor?

  11. #11
    Join Date
    Feb 2010
    Posts
    11
    =================================
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "COMMIT PRESERVE ROWS". Expected tokens may include: "<psm_semicolon> ".
    =================================


    And still couldn't figure out why the script is not working. If I can make this thing work, then I will be fine. I really need help on this.

    Many Thanks again for the prompt reply. Super Thanks!


    [-]

  12. #12
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If you are using the Command Editor to create the Stored Procedure, you need to change the statement delimiter character. The default is the semicolon which will not work for stored procedures. The entire CREATE PROCEDURE is just one statement to DB2. All of the internal sql statements are still part of the CREATE PROCEDURE statement, so the semicolon on those are need and thus a different character is need for the CREATE PROCEDURE statement. If you leave it at the default value, you get the error you are getting because DB2 thinks the entire CREATE PROCEDURE statement ends at the first semicolon.

    Andy

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If you are using the Command Editor, follow this steps.
    1) Tools ---> Tools Settings window ---> General tab ---> see "Use statement terminator character"
    2) Change "Use statement terminator character"(it must be semicolon) to "@".
    3) Close "Tools Settings" window.
    4) Execute my sample...
    CREATE PROCEDURE session.T1_SPx()
    LANGUAGE SQL
    BEGIN
    .....
    .....

    END;

    END@

  14. #14
    Join Date
    Feb 2010
    Posts
    11
    @ARWinner

    I changed the default delimiter value from semicolon( to this sign (@). I tried to re-execute tonkuma's revised script and I was surprised.

    It did work now! ^_^

    ============================================

    DB20000I The SQL command completed successfully.
    ============================================


    Thank you to all of you who helped me this quick!

    To n_i, tonkuma and ARWinner: Many and Super Thanks !!!


    [-]

  15. #15
    Join Date
    Feb 2010
    Posts
    11

    Update

    Hi Again,


    I have updated my script with the following. I was able to create the SP successfully but there was an error when trying to call it.

    ===============================================
    CREATE PROCEDURE session.T1_SPx()
    LANGUAGE SQL
    BEGIN
    declare v_parentid BIGINT;
    declare v_name VARCHAR(50);
    declare v_body CLOB;
    declare c1 cursor for
    select parentid, name, body
    from session.CELL2_TMP
    order by parentid
    for update of body;
    open c1;
    cell_loop:
    LOOP
    fetch c1 into v_parentid, v_name, v_body;
    UPDATE session.CELL2_TMP
    SET body = replace(body,'R("' concat v_name concat '")','')
    WHERE current of c1;
    END LOOP cell_loop;
    close c1;
    commit;
    END@
    ===============================================




    The error is

    ===============================================

    SQL0508N The cursor specified in the UPDATE or DELETE statement is not positioned on a row.

    ===============================================


    Any idea what could be the problem?

    Many thanks again for the help!


    [-]

Posting Permissions

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