Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    5

    Unanswered: cursor update stored procedure creation error

    I have two table on a test environment (Informix I, I want to use the values from "table1" to update matched row on "table2" using Informix 'cursor' on a stored procedure. However, am still getting error creating the stored proc. Please find the code below :

    ====
    Create Procedure update1()

    Define p_Account char(10);
    Define p_Amount int;

    DECLARE uc CURSOR FOR
    SELECT account, amount FROM table1 FOR UPDATE
    BEGIN WORK
    OPEN uc
    FETCH uc INTO p_Account, p_Amount
    IF sqlca.sqlcode=0 THEN
    UPDATE table2 SET balance = balance + p_amount WHERE account = p_Account
    --WHERE CURRENT OF uc
    END IF
    CLOSE uc
    COMMIT WORK
    FREE uc

    End Procedure
    ====

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    in a stored procedure, you do not want to declare a cursor explicitly, nor use a cursor for update, since you do not control how the cursor progresses.

    you may want to use isolation levels such as CURSOR STABILITY or REPEATABLE READ in order to protect your rows instead.

    I would suggest something like this
    Code:
    set ISOLATION to CURSOR STABILITY;
    BEGIN WORK ;
    FOREACH SELECT account, amount INTO p_Account, p_Amount
    FROM table1
    
    
    IF sqlcode = 0 THEN
            UPDATE table2 SET balance = balance + p_amount WHERE account = p_Account ;
    END IF
    END FOREACH
    COMMIT WORK ;

Posting Permissions

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