A far better method, instead of locking a record, I've found, is change
logging. All the apps we use here now have them, and the users love it.

I use one log table per table, with matching primary keys, so you can filter
easily from any screen you're display, as you already have all the keys to
query the log table:

For example:
The patient notes table:
CREATE TABLE tbl_patnotes (
int_noteid INTEGER NOT NULL,
int_patientid INTEGER NOT NULL, /* The Patient's id */
dtm_stamp TIMESTAMP DEFAULT 'now' NOT NULL,
int_userid INTEGER NOT NULL, /* The logged in user's id(who's
making this note */
str_note VARCHAR(10000), /* The note itself */
PRIMARY KEY(int_noteid),
FOREIGN KEY(int_userid) REFERENCES tbl_user(int_userid),
FOREIGN KEY(int_patientid) REFERENCES tbl_patient(int_patientid)
);
CREATE GENERATOR GEN_TBL_PATNOTES;


Then, you'd have a logging table to record changes

CREATE TABLE logtbl_patnotes (
int_logid NUMERIC(18,0) NOT NULL,
int_noteid INTEGER NOT NULL,
int_patientid INTEGER NOT NULL,
dtm_stamp TIMESTAMP DEFAULT 'now' NOT NULL,
str_field VARCHAR(32), /* The name of the field */
str_oldval VARCHAR(10000), /* The old value */
str_newval VARCHAR(10000), /* The New(current) value */
PRIMARY KEY(int_logid)
);
CREATE GENERATOR GEN_LOGTBL_PATNOTES;


Basically a trigger runs on every insert and update which compares old_* and
new_* values. Changes are written to a log table, that is also displayed on
your normal record editing screen.
That way, your users can see each version of the record, the differences
between them, and when the change was made, and who by...
In a hospital environment, you'd win awards for the auditability of the
system...

N.
-----Original Message-----
From: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com]On Behalf Of sasidhardoc
Sent: Thursday, 2 November 2006 12:06 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: SELECT FOR UPDATE WITH LOCK and
Connections

> "mucking about" but genuinely have a statement like this in a
> production application, CHANGE IT FAST!!

I am trying to have an application ported from an MS SQL backend to
FB. A table, (progress notes for patients) has multiple records that
are patient progress notes. Only one person should be able edit a
progress note at a time - so we open it using a "SELECT (WITH LOCK)"
in MS SQL. If it succeeds, then a second transaction writes the user
name to an AUDIT table and commits.
Subsequent attempts to EDIT this progress note while the first
transaction is still active will result in an error that is trapped.
For subsequent transactions, the note is opened "read only" and also
retrieves information from AUDIT to show who is currently editing the
note etc.

> Please read *carefully* the implementation and usage notes about FOR
> UPDATE WITH LOCK in the Firebird 1.5.3 release notes. It was *not*
> designed to be a workaround for developers who can't be bothered
> handling lock conflicts. It's purpose is to apply strict >

serialization on a record-by-record basis. If you ever have this >
(rare) requirement, the scope of the query specification should be >
limited to a very few records (ideally, one record) by a very tight >
WHERE clause.
The SP retrieves ONE record - the NOTE that we are trying to edit -
using "WHERE ID(PK) = x"
In any case, it looks like SELECT FOR UPDATE with LOCK will not be an
adequate replacement for MS SQL "SELECT (WITH LOCK)" since dropped
connections (such as flaky laptop wireless connections) will wreak
havoc leaving behind locked records.
How would I address my need to allow only one user to edit the record
at a time?






[Non-text portions of this message have been removed]