Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2005
    Posts
    30

    Unanswered: Why a update with where clause, didn't work properly

    I tried to launch a proc like below by a C++ program.
    first i deactivate the index on field b , which is not primary key.
    a is primary key.

    But after this proc is finished without any error message.
    Supposed should have 1000 records updated, finally only 900 records updated!

    I check back with the same select, indeed 100 records stayed like original data, without update.

    Anybody can help me to check out the problem? the foreach doesn't scan the whole table thoroughly with the where condition?

    After this update, we enable the index on field b again.

    proc :

    drop procedure test();

    create procedure test()
    returning integer;

    {Variables pour la gestion des erreurs INFORMIX}
    define esql, eisam integer;
    define error_text char(255);

    define compteur integer; -- nombre de ligne traitees

    define key_name1 like table.a;
    define field like table.b;

    let compteur = 0;
    set debug file to 'test.out' ; trace on;

    begin
    on exception set esql, eisam, error_text
    raise exception esql, eisam, error_text;
    return;
    end exception

    foreach with hold
    select
    a,
    b
    into
    key_name1,
    field
    from table
    where (length(b)=11) and (b >= '96560000000') and (b <= '96569999999')

    let field = '965' || field[5,11];

    {update des lignes selectionnes}
    update table
    set
    b = field
    where a = key_name1;

    let compteur = compteur + 1;
    end foreach

    return compteur;
    end

    END PROCEDURE;

  2. #2
    Join Date
    Oct 2005
    Posts
    30
    i use informix V9.40.FC7 version.

  3. #3
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    get how many rows are updated with dbinfo('sqlca.sqlerrd2')
    and discover what key_name1 was not been updated...
    Last edited by ceinma; 09-28-07 at 10:48.
    ________________________________________
    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).
    ________________________________________

  4. #4
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    Hi, I don't know why your stored procedure doesn't update all the records. Maybe it has to do with the lock level (table locking vs. row locking) in conjunction with the isolation level. Check out under what conditions concerning these matters the SP is executed.
    But a simpler solution is to not use the FOREACH loop but make the changes in one query, like:
    Code:
    UPDATE table SET b = '965' || b[5,11]
    WHERE b MATCHES '9656???????';
    
    LET compteur = dbinfo('sqlca.sqlerrd2');
    BTW. In your SELECT query you're making use of numeric operators on a string type column. I wouldn't do that, for one thing because it costs extra time fot the implicit conversion the server has to make to interpret the clause. That's why I use the string operator MATCHES here.

    Regards
    Last edited by Tyveleyn; 09-30-07 at 08:34.

  5. #5
    Join Date
    Oct 2007
    Posts
    5

    Add begin work and comit work

    I would add a 'BEGIN WORK' statement at the beginning of your procedure and then add a 'COMIT WORK' statement at the end. This should force all updates to be commited.

Posting Permissions

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