From torque-user-return-7416-apmail-db-torque-user-archive=db.apache.org@db.apache.org Mon Feb 12 13:49:20 2007 Return-Path: Delivered-To: apmail-db-torque-user-archive@www.apache.org Received: (qmail 64708 invoked from network); 12 Feb 2007 13:49:20 -0000 Received: from hermes.apache.org (HELO mail.apache.org) (140.211.11.2) by minotaur.apache.org with SMTP; 12 Feb 2007 13:49:20 -0000 Received: (qmail 4721 invoked by uid 500); 12 Feb 2007 13:49:27 -0000 Delivered-To: apmail-db-torque-user-archive@db.apache.org Received: (qmail 4425 invoked by uid 500); 12 Feb 2007 13:49:26 -0000 Mailing-List: contact torque-user-help@db.apache.org; run by ezmlm Precedence: bulk List-Unsubscribe: List-Help: List-Post: List-Id: "Apache Torque Users List" Reply-To: "Apache Torque Users List" Delivered-To: mailing list torque-user@db.apache.org Received: (qmail 4414 invoked by uid 99); 12 Feb 2007 13:49:26 -0000 Received: from herse.apache.org (HELO herse.apache.org) (140.211.11.133) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Feb 2007 05:49:26 -0800 X-ASF-Spam-Status: No, hits=0.3 required=10.0 tests=MAILTO_TO_SPAM_ADDR X-Spam-Check-By: apache.org Received-SPF: pass (herse.apache.org: local policy) Received: from [217.24.207.26] (HELO mail.seitenbau.net) (217.24.207.26) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 12 Feb 2007 05:49:16 -0800 Received: from [192.168.15.18] (helo=www.seitenbau.net) by router.seitenbau.net with esmtp (Exim 4.43) id 1HGbY6-0007Ki-Lf for torque-user@db.apache.org; Mon, 12 Feb 2007 14:48:54 +0100 In-Reply-To: Subject: AW: Best practice for entity update. To: "Apache Torque Users List" X-Mailer: Lotus Notes Release 7.0.1 January 17, 2006 Message-ID: From: Thomas Fischer Date: Mon, 12 Feb 2007 14:48:53 +0100 X-MIMETrack: Serialize by Router on www/seitenbau(Release 7.0.1|January 17, 2006) at 12.02.2007 02:48:53 PM MIME-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: quoted-printable X-Virus-Checked: Checked by ClamAV on apache.org We have implemented something similar. What you are asking for is calle= d "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 !=3D :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 du= al; 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 =3D 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_MODIFICATI= ON); } else { throw e; } } } public void setConcurrencyValue(long concurrencyValue) { // object mustnot be marked modified if concurrency_value is update= d after a save boolean modified =3D 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 us= ed 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 ar= e selected for blob or clob update. If you are not using oracle, you need= not care about this. Thomas "Thoralf Rickert" 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=FCngliche Nachricht----- > > Von: Christopher Thill [mailto:cthill69@yahoo.com] > > 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 =3D (IntakeTool) > > context.get(ConstSpr.INTAKE_STRING); > > Group group =3D > > intake.get(ConstSpr.INTAKE_SPRSA_CONTRACT,IntakeTool.DEFAULT_KEY); > > SprsaRsaContract sprsa =3D 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 =3D > > SpscmSalesCompanyPeer.buildCriteria(this); > > Criteria updateCriteria =3D > > 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: torque-user-unsubscribe@db.apache.org > > For additional commands, e-mail: torque-user-help@db.apache.org > > > > > > > ---------------------------------------------------------------------= > To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org > For additional commands, e-mail: torque-user-help@db.apache.org >= --------------------------------------------------------------------- To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org For additional commands, e-mail: torque-user-help@db.apache.org