Page 1 of 1
Concurrent write access to a record
Posted: Sat Jun 22, 2013 11:24 am
by alextouch
Hi
It's possible to make a record "untouchable" by others when one is modifying it?
In other words, if I select a row from a browse screen, while I make modifications in the edit screen, another people can access to the same record, make modifications and save it into database.
I need to prevent this to happen.
Thank you
Alex
Re: Concurrent write access to a record
Posted: Mon Jun 24, 2013 12:16 am
by admin
Alex,
As nuBuilder comes, its not, but 2 people can be editing the same record with only the fields they changed being updated.
The other way would to set a flag on "BEFORE OPEN" and having the Browse Screen filter out those being edited.
Steven
Re: Concurrent write access to a record
Posted: Tue Jul 02, 2013 10:47 pm
by alextouch
Steven
Thank you for your reply.
What I need to prevent is exactly that two people edit *the same field* at the same time.
Your suggestion for filtering in browse screen is good but I don't know how to do so; that is, what flag I have to use in BEFORE OPEN that tells me that the record is being edited?
Alex
Re: Concurrent write access to a record
Posted: Fri Jul 12, 2013 3:11 am
by massiws
Alex,
it's not a clean solution, but you can do something like this:
- on the table you want to prevent concurrent access add a field 'locked'; this will be the flag that you can use to filter out the locked records on Browse Screen:
Code: Select all
ALTER TABLE `your_table` ADD `locked` VARCHAR( 15 ) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL
- in Setup > Code Library add this two entry:
Code: Select all
/**
* Lock a record to prevent editing by other users.
*
* When a user open a record on Edit-Screen, the record is locked by storing
* the user ID in a field called 'locked': the locked record must be filter out in
* SQL Form statement.
*
* Example: to lock a record on invoice table insert this code in Form <Before Open> tab:
*
* lockRecord('invoice', 'invoice_id', '#recordID#', '#zzsys_user_id#');
*
* @param string $table Table name
* @param string $pk Primary key of the table
* @param string $recordID ID of the record to lock
* @param string $UserID ID of the user that lock the record
*
* @return void
*/
function lockRecord($table, $pk, $recordID, $userID) {
$sql = "UPDATE $table SET locked = '$userID' WHERE $pk = '$recordID' ";
nuRunQuery($sql);
}
Code: Select all
/**
* Unlock a previously locked record.
* The lock is released calling this function from a code in <After save> tab.
*
* Example: to unlock a record in invoide table:
*
* unlockRecord('invoice', 'invoice_id', '#recordID#');
*
* @param string $table Table name
* @param string $pk Primary key of the table
* @param string $recordID ID of the record to lock
*
* @return void
*/
function unlockRecord($table, $pk, $recordID) {
$sql = "UPDATE $table SET locked = '' WHERE $pk = '$recordID' ";
nuRunQuery($sql);
}
- Now, to filter out the locked records on Browse screen, add this to the SQL statement of your form:
- in Custom Code > Before Open tab insert this code to lock the record when a user select it:
Code: Select all
/* Lock the record */
$tbl = "you_table";
$pk = "your_table_primary_key";
lockRecord($tbl, $pk, "#recordID#", "#zzsys_user_id#");
- and in Custom Code > After Save tab insert this code to release the lock when a user save the record:
Code: Select all
/* Unlock the record */
if ("#recordID#" != "-1") {
$tbl = "you_table";
$pk = "you_table_primary_key";
unlockRecord($tbl, $pk, "#newID#");
}
As I said above, this is not a clean solution: the user
MUST exit Edit Screen with
Save (or
Save and Close) button. If the user leave the record clicking on the breadcrumbs the record isn't saved and remain still locked.
Maybe, adding some JavaScript code to control breadcrumbs exit, could work better.
Hope this helps,
Max.
Re: Concurrent write access to a record
Posted: Wed Jul 17, 2013 11:07 am
by alextouch
Max
Thank you for your reply.
I'll try your great solution!
Alex
Re: Concurrent write access to a record
Posted: Fri Aug 09, 2013 3:47 am
by admin
Alex,
Max is correct, it could be dangerous.
Steven