SELECT ... FOR UPDATE

Hi All, I'd need some clarification on the SELECT .. FOR UPDATE statement: We're currently porting an application from Oracle to Informix and we're using these kind of statement a lot. At first, we expected the FOR UPDATE to behave the same as on Oracle. But It doesn't seem to work the same way. Here's the quick test I did: 1- Open a SQL Editor on database TESTDB 2- Execute: Begin work; Select * from tab1 for update; -- Here I expect to put locks on selected rows 3- Open another SQL Editor on database TESTDB 4- Use: Begin work; Update tab1 set col1=somevalue; -- Here I expect Informix to return me an error, but instead -- I can update the rows and commit without any problems! I've tried with different isolation levels but without success. Is there a way to use the SELECT FOR UPDATE as on Oracle? Thanks in advance, Steeve steeve_boulanger at baan dot com -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

Informix does not lock the records until you actually do a fetch. open cursor fetch cursor name into (variable) -- Tries to lock the record at this point. You should test the sqlca.sqlcode for an error if the record is locked. You can also restrict your updating to certain fields by using for update of field1, field2,.....,etc. steeve_boulanger@baan.com wrote: > Hi All, > > I'd need some clarification on the SELECT .. FOR UPDATE statement: > We're currently porting an application from Oracle to Informix and > we're using these kind of statement a lot. At first, we expected > the FOR UPDATE to behave the same as on Oracle. But It doesn't seem > to work the same way. Here's the quick test I did: > > 1- Open a SQL Editor on database TESTDB > 2- Execute: Begin work; > Select * from tab1 for update;> -- Here I expect to put locks on selected rows > > 3- Open another SQL Editor on database TESTDB > 4- Use: Begin work; > Update tab1 set col1=somevalue;> -- Here I expect Informix to return me an error, but instead > -- I can update the rows and commit without any problems! > > I've tried with different isolation levels but without success. > Is there a way to use the SELECT FOR UPDATE as on Oracle? > > Thanks in advance, > > Steeve > steeve_boulanger at baan dot com > > -----------== Posted via Deja News, The Discussion Network ==---------- > http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own