Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2008
    Posts
    7

    Unanswered: Foreach Cursor Error

    Hello All,

    This problem has been discussed here and I do not know why I am still facing problems on that.
    The problem should update a table with about 3,000,000 rows using a controlled flow which needs to commit the data every 10,000 rows, may be more but, not enough to run on long transactions or lock table overflow.

    Errors like:
    exception : looking for handler
    SQL error = -255 ISAM error = 0 error string = = ""
    exception : no appropriate handler
    or
    SQL Error =-535 have been occurring on the following procedure.

    CREATE PROCEDURE update_t1();
    DEFINE count INT;
    DEFINE my_rowid INT;
    LET count = 0;
    LET my_rowid = 0;
    FOREACH cur_su WITH HOLD FOR
    SELECT rowid INTO my_rowid FROM t1 WHERE 1=1
    IF count = 10000 THEN
    COMMIT WORK;
    BEGIN WORK;
    LET count = 1;
    ELSE
    UPDATE t1
    SET col1=10.00, col2=9.34
    WHERE rowid = my_rowid;
    LET count = count + 1;
    END IF
    END FOREACH
    END PROCEDURE;

    Any help will be very appreciate.Thanks

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    I see only one mistake...
    where is the last commit?
    If you have 10.001 records, the foreach will finish on this record and any commit will be executed...
    You need to create a transaction control, like this:

    Code:
    CREATE PROCEDURE update_t1();
    DEFINE count INT;
    DEFINE my_rowid INT;
    DEFINE inTrans INT;
    LET count = 0;
    LET my_rowid = 0;
    LET inTrans = 0;
    FOREACH cur_su WITH HOLD FOR
    SELECT rowid INTO my_rowid FROM t1 WHERE 1=1
    IF count = 10000 THEN
    COMMIT WORK;
    BEGIN WORK;
    LET inTrans = 1;
    LET count = 1;
    ELSE
    UPDATE t1
    SET col1=10.00, col2=9.34
    WHERE rowid = my_rowid;
    LET count = count + 1;
    END IF
    END FOREACH ;
    if inTrans = 1 then 
      commit work ;
    end if
    END PROCEDURE;
    If your error not have any with this situation... my suggestion is use a range of key field to execute this update. I believed will be faster too.
    To know how much records are for each key , you can use the update statistics histogram (dbschema -hd option). But, keep your statistics updated for this!
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Aug 2008
    Posts
    7

    Foreach Cursor error

    Thanks for you prompt reply Ceinma,
    Although, I have implemented your recommendatios, I am still facing problems. Can you take a look into the log file?

    update ed_ticket set
    (col1) = (10.00),
    (col2) = (9.34)
    where (= rowid, my_rowid);
    expression+ count, 1)
    evaluates to 2000
    let count = 2000
    select cursor iteration.
    select cursor returns 73222
    expression= count, 2000)
    evaluates to t

    commit work;
    exception : looking for handler
    SQL error = -255 ISAM error = 0 error string = = ""
    exception : no appropriate handler

    So, 2,000 rows have been updated but not the rest of the table.

    Thank you,
    Ed

  4. #4
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    Hum... i think you will need the IBM Support...
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  5. #5
    Join Date
    Nov 2010
    Posts
    1

    Foreach Cursor error

    You shall set ''Begin work" statement inside foreach cursor in order to set beginning of your job, so you can set one check as following:
    IF count = 0 THEN
    BEGIN WORK;
    END IF;

Posting Permissions

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