Re: Dirty reads with OnLine

Extel Inc said: <excerpts> : } >> But as I just found out, if you do COMMITTED READ or above, you'll : } >> loop on a -244 SQLCODE if any update locks are around. Nice .. : } >>-- End of excerpt from Extel Inc. : } No I can't. And I know what DIRTY READ does. The whole point is I HAVE : } to use DIRTY READ with Informix as I can't guarantee someone else hasn;t : } locked something with a bad app. design for too long. This is in a : } shrinkwrapped product that reads gobs of other people's data. I can't : } complain to MIS.. And with Informix if I hit a -244 I'm dead meat. Just : } loops, no row level status and move on. And Jim Gordon said: : Well, unfortunately for you, Informix doesn't provide a way of resetting the : default isolation level via an external parameter that I'm aware of. : If I understand your problem correctly, this could really be described as a bug : within the shrink-wrapped package as it is supposed to set its own environment : for the job it's doing when it links to the database. I, at least, must not be understanding the problem correctly. Was the application purchased shrink-wrapped, and not under the previous writer's control, and the user forced to use COMMITTED READ and loop on -244 errors? Or is he being forced to use DIRTY READ, and read data that might not be committed? I have to agree with Jim, and say it's a problem with the package, if either of these is true. I can get what Extel Inc. is saying he/she wants (I think) by using two cursors and a combination of DIRTY and COMMITTED READ. I whipped up a little program to do this in about 3 minutes, not counting testing (although I didn't test very extensively). Okay, it's not the prettiest thing I ever wrote, but it's certainly very easy. I've attached it below. I won't even charge royalties ;-) When I lock the customers with customer_num < 110 (with an UPDATE statement), the output I get from this program is: sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 sqlcode = -246 110Roy Jaeger 111Frances Keyes 112Margaret Lawson etc. If I understand Extel Inc's complaint (a big IF there), this should cover the problem. Unfortunately, it will depend on how the shrink-wrapped application developer coded it. If they didn't code it like me, it won't behave like mine. June ---- June Tong Informix Software ---- ---- Senior Consultant (415) 926-6140 ---- ---- International Support junet@informix.com ---- ---- Location-du-jour: Menlo Park ---- database stores5 main define crec record like customer.* define cnum integer define st char(100) whenever error continue declare c1 cursor for select customer_num from customer let st = "select * from customer where customer_num = ?" prepare s1 from st declare c2 cursor for s1 set isolation to dirty read foreach c1 into cnum set isolation to committed read open c2 using cnum fetch c2 into crec.* if sqlca.sqlcode != 0 then display "sqlcode = ", sqlca.sqlcode else display crec.customer_num, crec.fname, crec.lname end if close c2 set isolation to dirty read end foreach end main