Concurrent write access to a record

[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

[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

[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

[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:

  Code: Select all

  ...
   WHERE locked = '' 

• 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.

[alextouch]

Max

Thank you for your reply.
I'll try your great solution!

Alex

[admin]

Alex,

Max is correct, it could be dangerous.

Steven