Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    5

    Question Unanswered: Foreach - Not In A Transaction

    Hello
    I'm trying to avoid an 'infinite loop' when I use "with hold" i'm updating 24000 records.

    I try to use this stored procedure, but don't work. The error message is
    255: Not in transaction.

    What's is the problem?

    CREATE PROCEDURE foreach_ex()
    define varExtremo_a char(40);
    define varTecnologia char(10);
    FOREACH cur1 FOR
    SELECT extremo_a INTO varExtremo_a
    FROM vunif_temp
    WHERE Tecnologia = 'WLL'
    IF varExtremo_a is null THEN
    UPDATE vunif_temp
    SET extremo_a = 'xx'
    WHERE CURRENT OF cur1;
    END IF
    END FOREACH

    END PROCEDURE;

    An example can be readed in
    http://www.dbcenter.cise.ufl.edu/tri...fm.html#125314

  2. #2
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    Please test this

    CREATE PROCEDURE foreach_ex()
    define varExtremo_a char(40);
    define varTecnologia char(10);

    on exception set ....
    ..........
    end exception

    FOREACH cur1 with hold FOR
    SELECT extremo_a INTO varExtremo_a
    FROM vunif_temp
    WHERE Tecnologia = 'WLL'
    IF varExtremo_a is null THEN
    begin work;
    UPDATE vunif_temp
    SET extremo_a = 'xx'
    WHERE CURRENT OF cur1;
    if sqca.sqlcode = 0 then
    commit work
    else
    rollback work
    end if;
    else
    continue foreach
    END IF
    END FOREACH

    Gustavo.

  3. #3
    Join Date
    Aug 2003
    Location
    Argentina
    Posts
    780
    Hi,

    Excuse me, i remember that you can't declare with hold whit SELECT FOR UPDATE and can't use WHERE CURRENT OF...
    I think that you must change sentence UPDATE add clausule WHERE with primary key.

    Gustavo.

  4. #4
    Join Date
    Nov 2004
    Posts
    26
    why not just add ;

    AND extremo_a = NULL

    to your cursor and use a unique (primary) key

    OR
    can you just use SQL


    UPDATE vunif_temp
    SET extremo_a = 'xx'
    WHERE Tecnologia = 'WLL'
    AND extremo_a is null
    Last edited by nog; 11-19-04 at 12:13.

  5. #5
    Join Date
    Jun 2004
    Location
    Madrid, Spain
    Posts
    47
    Hi,

    I can't check the documentation right now, but I'm pretty sure that you need to open a transaction for the named cursors inside the stored procedures to work properly. But I'd recomend not to begin/commit it in every loop: it will be the best way to kill your server performance. It will work better if you open the tx, do the job, and commit/rollback the transaction at the end (you can keep a variable with the results, for instance).

    Check it. I'm writing from what I remember, and it could be not too accurate.

    And my opinion is that the update statement that 'nog' has written will be the best and fastest way to do your job, if you can use it.

    Hope it helps.
    José Luis Matute.

    Regards from Spain.

Posting Permissions

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