Re: 4GL - confused about update cursors, transactions, "with hold" and best way to carry out a specific task

In article <68f8qn$3sk$1@columbine.singnet.com.sg>, Kenneth Xu <kennethxu@hotmail.com> writes >Paul Roberts wrote in message <68enff$cuc@news.informix.com>... >>I'm a bit confused over the conditions about what can and cannot happen >>within a transaction, ought my cursor be "WITH HOLD", and so on. My DB is >>non-MODE ANSI but has transactions. My manual suggests that "each update... >>must take place within a transaction". I feel that I don't really want to >>use transactions here - and certainly I don't want to put the entire >>FOREACH within a transaction, as there will be many many updates and I'll >>get a long transaction error. > >1. If you can lock your table in share mode, do this: > lock table my_table in share mode > declare c1 cursor for > select data1, data2, data3,data4, data5 > from my_table > foreach c1 into p_data1, p_data2, p_data3, p_data4, p_data5 > [compute p_result based on these values and DB lookups] > update my_table > set result = p_result > where data1 = p_data1 > and ... > and data5 = p_data5 > [do exception check] > end foreach> This will encounter a long transaction. >2. otherwise: > > # you have declare it as "with hold", otherwise "commit work" > # or "rollback work" will close this cursor > declare c1 cursor with hold for > select data1, data2, data3,data4, data5 > from my_table > foreach c1 into p_data1, p_data2, p_data3, p_data4, p_data5 > begin work > beclare c2 cursor for > select data1, data2, data3, data4, data5 > where data1 = p_data1 > and ... > and data5 = p_data5 > for update of result > open c2 > fetch c2 > [do exception check, roll back if neccessary] > [compute p_result based on these values and DB lookups] > update my_table > set result = p_result > where current of c2 > [do exception check, roll back if neccessary] > commit work > end foreach This will not. You do not need to lock the table in shared mode since the commit will release all locks. Hence you will only hold locks on one row (or page) at a time. I would declare and prepare the cursor c2 outside the loop using "?" placeholders and also prepare the update outside the loop. > >3. In most case, it is good to have a serial field in a table. Assume the >serial field of your table is myid: > > declare c1 cursor with hold for > select myid from my_table > foreach c1 into p_myid > begin work > beclare c2 cursor for > select * where myid = p_myid > for update of result > open c2 fetch c2 into p_myrecord.* > [do exception check, roll back if neccessary] > [compute p_myrecord.result based on these values and DB lookups] > update my_table > set result = p_myrecord.result > where current of c2 > [do exception check, roll back if neccessary] > commit work > end foreach>> >>If there is a problem using an update cursor in this situation then I could >>always do without it by issuing my update statement along the lines of:- >> >> UPDATE my_table >> SET result = p_result >> WHERE data1 = p_data1 >> AND ... >> AND data5 = p_data5 >> >>Would that be much slower? I could always add a serial column to the table >>and use that as the basis for the update - or I could use rowid (but only >>if the table isn't fragmented, yes)? > > >Slower is still minor, serial column will definaly help. BUT, you will get >into problem if other process updated one of the key value after you fetch >the record and before you update it. That will be the major problem unless >you can ensure your program is the only process that will update this table. >We do not do that if there is an alternative way. > >Holp it helps. > >Happy New Year! >Ken > > > -- David Williams Maintainer of the Informix FAQ Primary site (Beta Version) http://www.smooth1.demon.co.uk Official site http://www.iiug.org/techinfo/faq/faq_top.html I see you standin', Standin' on your own, It's such a lonely place for you, For you to be If you need a shoulder, Or if you need a friend, I'll be here standing, Until the bitter end... So don't chastise me Or think I, I mean you harm... All I ever wanted Was for you To know that I care