SELECT FOR UPDATE

Posted in 2008

Premnath Srinivasan — Tue, 06 May 2008 15:58:17 — source: IIUG Forums & Mailing Lists

Hi All, When I try to execute the following query in DBACCESS, I am getting the error "-255 : Not in transaction" SELECT * FROM tab2 FOR UPDATE But when I start a transaction and then execute the same query, every thing is working fine. Begin work; SELECT * FROM tab2 FOR UPDATE Please explain this behavior. Are the SELECT FOR UPDATE not supposed to work when we are not in a transaction ? Thanks in advance !!! Regards, SP

↪ replying to Premnath Srinivasan

Keith Simmons — Tue, 06 May 2008 16:52:01 — source: IIUG Forums & Mailing Lists

On 06/05/2008, Premnath Srinivasan <s.premnath@gmail.com> wrote: > Hi All, > > When I try to execute the following query in DBACCESS, I am getting the > error "-255 : Not in transaction" > SELECT * FROM tab2 FOR UPDATE> > But when I start a transaction and then execute the same query, every thing > is working fine. > > Begin work; > SELECT * FROM tab2 FOR UPDATE> > Please explain this behavior. Are the SELECT FOR UPDATE not supposed to > work when we are not in a transaction ? > > Thanks in advance !!! > > Regards, > SP > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Srinivasan Please provide IDS Version and O/S as aminimum to 'encourage' the best response!! However SELECT FOR UPDATE requires exclusive access to the selected records, to hold this exclusivity requires these records be locked and locking is only available withi a transaction. Dbaccess does not implicitly create transactions, ergo, SELECT FOR UPDATE in dbaccess must be bounded by START WORK and COMMIT. Keith

↪ replying to Premnath Srinivasan

R Fo — Tue, 06 May 2008 16:53:12 — source: IIUG Forums & Mailing Lists

Hi SP I guess you're new in IDS (informix) When you will select line (or lines) from a table to update , it should be in a transaction. You'll have possibility to start a transaction in your database if it has 'logging mode buffered, unbuffered or ansi'. Well: Run dbaccess at the first , execute the statement: begin work; After that, execute the select for update... At the end, if you have done some modifications in some lines, you have two possibilities: rollback work or commit work BR Roberto FERRONATO > To: ids@iiug.org > From: s.premnath@gmail.com > Subject: SELECT FOR UPDATE [11981] > Date: Tue, 6 May 2008 11:58:18 -0400 > > Hi All, > > When I try to execute the following query in DBACCESS, I am getting the > error "-255 : Not in transaction" > SELECT * FROM tab2 FOR UPDATE> > But when I start a transaction and then execute the same query, every thing > is working fine. > > Begin work; > SELECT * FROM tab2 FOR UPDATE> > Please explain this behavior. Are the SELECT FOR UPDATE not supposed to > work when we are not in a transaction ? > > Thanks in advance !!! > > Regards, > SP > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Explore the seven wonders of the world http://search.msn.com/results.aspx?q=7+wonders+world&mkt=en-US&form=QBRE

↪ replying to Keith Simmons

R Fo — Tue, 06 May 2008 16:54:50 — source: IIUG Forums & Mailing Lists

Sorry Keith Is not "start work" but "begin work" !! ehehhehe BR R Ferronato > To: ids@iiug.org > From: smiley73@googlemail.com > Subject: Re: SELECT FOR UPDATE [11982] > Date: Tue, 6 May 2008 12:52:02 -0400 > > On 06/05/2008, Premnath Srinivasan <s.premnath@gmail.com> wrote: > > Hi All, > > > > When I try to execute the following query in DBACCESS, I am getting the > > error "-255 : Not in transaction" > > SELECT * FROM tab2 FOR UPDATE> > > > But when I start a transaction and then execute the same query, every thing > > is working fine. > > > > Begin work; > > SELECT * FROM tab2 FOR UPDATE> > > > Please explain this behavior. Are the SELECT FOR UPDATE not supposed to > > work when we are not in a transaction ? > > > > Thanks in advance !!! > > > > Regards, > > SP > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > Srinivasan > > Please provide IDS Version and O/S as aminimum to 'encourage' the best > response!! > However SELECT FOR UPDATE requires exclusive access to the selected > records, to hold this exclusivity requires these records be locked and > locking is only available withi a transaction. Dbaccess does not > implicitly create transactions, ergo, SELECT FOR UPDATE in dbaccess > must be bounded by START WORK and COMMIT. > > Keith > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > _________________________________________________________________ Connect to the next generation of MSN Messenger http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai ltagline

↪ replying to R Fo

Keith Simmons — Tue, 06 May 2008 17:51:16 — source: IIUG Forums & Mailing Lists

On 06/05/2008, R Fo <roeferr@hotmail.com> wrote: > Sorry Keith > > Is not "start work" but "begin work" !! ehehhehe > > BR R Ferronato > > > To: ids@iiug.org > > From: smiley73@googlemail.com > > Subject: Re: SELECT FOR UPDATE [11982] > > Date: Tue, 6 May 2008 12:52:02 -0400 > > > > On 06/05/2008, Premnath Srinivasan <s.premnath@gmail.com> wrote: > > > Hi All, > > > > > > When I try to execute the following query in DBACCESS, I am getting the > > > error "-255 : Not in transaction" > > > SELECT * FROM tab2 FOR UPDATE> > > > > > But when I start a transaction and then execute the same query, every > thing > > > is working fine. > > > > > > Begin work; > > > SELECT * FROM tab2 FOR UPDATE> > > > > > Please explain this behavior. Are the SELECT FOR UPDATE not supposed to > > > work when we are not in a transaction ? > > > > > > Thanks in advance !!! > > > > > > Regards, > > > SP > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > Srinivasan > > > > Please provide IDS Version and O/S as aminimum to 'encourage' the best > > response!! > > However SELECT FOR UPDATE requires exclusive access to the selected > > records, to hold this exclusivity requires these records be locked and > > locking is only available withi a transaction. Dbaccess does not > > implicitly create transactions, ergo, SELECT FOR UPDATE in dbaccess > > must be bounded by START WORK and COMMIT. > > > > Keith > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > _________________________________________________________________ > Connect to the next generation of MSN Messenger > > http://imagine-msn.com/messenger/launch80/default.aspx?locale=en-us&source=wlmai ltagline > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Dohhh !! Long day, too much work !! Need sleep.

Related threads

Glossary

dbaccess