We have implemented something similar. What you are asking for is called
"optimistic locking" and it is usually done using triggers. See e.g.
http://orafaq.com/papers/locking.pdf
Our implementation is on oracle 9i: It increases a check column on each
update by one. The following sql creates the triggers:
--------------------------------------------------------
-- INSERT TRIGGER
--------------------------------------------------------
CREATE OR REPLACE TRIGGER ANSCHREIBEN_INS_CONCURRENCY
BEFORE INSERT ON ANSCHREIBEN FOR EACH ROW
begin
SELECT 1 INTO :NEW.CONCURRENCY_VALUE FROM dual;
end;
.
run;
--------------------------------------------------------
-- UPDATE TRIGGER
--------------------------------------------------------
CREATE OR REPLACE TRIGGER ANSCHREIBEN_UPD_CONCURRENCY
BEFORE UPDATE ON ANSCHREIBEN FOR EACH ROW
declare
ERROR_MESSAGE_T VARCHAR(200);
begin
if( :NEW.CONCURRENCY_VALUE != :OLD.CONCURRENCY_VALUE) then
select 'Concurrency failure. Old : '
|| CAST(:OLD.CONCURRENCY_VALUE AS VARCHAR2(50))
|| ' new: '
|| CAST(:NEW.CONCURRENCY_VALUE AS VARCHAR2(50))
into ERROR_MESSAGE_T from dual;
raise_application_error( -20000, ERROR_MESSAGE_T);
end if;
SELECT :OLD.CONCURRENCY_VALUE + 1 INTO :NEW.CONCURRENCY_VALUE FROM dual;
end;
.
run;
The save() and setConcurrencyValue() methods in the object needs to be
altered as well:
public void save(Connection con) throws TorqueException
{
try
{
// first check whether object is modified
// then save,
// then increase concurrencyValue
boolean modified = isModified();
super.save(con);
if (modified)
{
setConcurrencyValue(getConcurrencyValue() + 1);
}
}
catch (TorqueException e)
{
logge.error(e);
if (DatabaseHelper.isConcurrencyFailure(e))
{
throw new Exception(DefaultMessages.ERROR_CONCURRENT_MODIFICATION);
}
else
{
throw e;
}
}
}
public void setConcurrencyValue(long concurrencyValue)
{
// object mustnot be marked modified if concurrency_value is updated
after a save
boolean modified = isModified();
super.setConcurrencyValue(concurrencyValue);
setModified(modified);
}
On other databases the sql script does not run, but maybe it gives you the
general idea.
However, on oracle, this is problematic if BLOB and CLOB columns are used
with Sarav's patched village jar for oracle, so this needs also to be
patched in order to not use the CONCURRENCY_VALUE columns if columns are
selected for blob or clob update. If you are not using oracle, you need not
care about this.
Thomas
"Thoralf Rickert" <[EMAIL PROTECTED]> schrieb am 09.02.2007
13:10:02:
> Hi!
>
> If it isn't possible to use a transaction connection you can use a
> timestamp column to verify if the row is changed in the database.
> Before saving the instance you can read the timestamp column from
> the DB and compare it with the timestamp that is set in your
> instance (this should be done in a "short transaction" during save()).
>
> bye
> Thoralf
>
> > -----Ursprüngliche Nachricht-----
> > Von: Christopher Thill [mailto:[EMAIL PROTECTED]
> > Gesendet: Freitag, 9. Februar 2007 12:46
> > An: Apache Torque Users List
> > Betreff: Best practice for entity update.
> >
> >
> > I wanted to ask a question about the best way to
> > prevent users overwriting each others changes when
> > updating the database.
> >
> > If you use the simplest way
> > IntakeTool intake = (IntakeTool)
> > context.get(ConstSpr.INTAKE_STRING);
> > Group group =
> > intake.get(ConstSpr.INTAKE_SPRSA_CONTRACT,IntakeTool.DEFAULT_KEY);
> > SprsaRsaContract sprsa = new SprsaRsaContract();
> >
> > group.setProperties( sprsa );
> > sprsa.save();
> >
> > You can over right another users changes to the
> > database.
> >
> > I have decided to stuff the torque object used to
> > build the form into the users temporary storage and
> > then when the user submits the form I retreive it and
> > do something like :
> >
> > Criteria whereCriteria =
> > SpscmSalesCompanyPeer.buildCriteria(this);
> > Criteria updateCriteria =
> > SpscmSalesCompanyPeer.buildCriteria(newValues);
> >
> > SpscmSalesCompanyPeer.doUpdate(whereCriteria,
> > updateCriteria);
> >
> > Then check to see how many rows were updated.
> >
> > Any suggestions for this would be appreciated.
> >
> > Thanks,
> > Chris
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: [EMAIL PROTECTED]
> > For additional commands, e-mail: [EMAIL PROTECTED]
> >
> >
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
