Foxhound is the better* Database Monitor for SQL Anywhere. *better: More thorough, more relevant, more effective. ...more Alerts, more All Clears, more details, more control in your hands.

Local Session Locks

While it might be rare for two users to try to update the same data at the same time, it's not uncommon for one user to forget that some data is already being displayed and to open up another window to do the same thing.

Many applications prevent this by allowing only one copy of one window to be opened at a time. While that solution works, it also prevents the user from opening two different pieces of data in two instances of the same window. That's like a word processing program only letting you work on one document at a time.

A better solution is to keep track of which pieces of data are being worked on and to check before opening a new piece of data. This can be as simple as appending primary key values to a "lock string" as windows are being opened and removing them as windows are closed. If the primary key is already in the string, the new window can't be opened and the user is gently told to go back to the old one.

Because the lock string must be available throughout the application, it could be declared globally. A better solution is to encapsulate the lock string as a private instance variable within a global Custom Class User Object. This protects the lock string from accidental changes by forcing all updates to be performed by object level functions, also encapsulated within the user object.

Listing 1 shows how such a user object is declared and created, as well as how the user object functions of_get_lock and of_release_lock are called from the window open and close scripts. The window instance variable is_agreement_id holds the primary key to be locked, and the string literal "Agreement" is appended to differentiate this kind of key from others the application might use.

Listing 1: Grabbing and Releasing Locks

   // Global Variable Declaration.

   u_cc_app guo_app // Application "extension"

   // Application Open Script.

   guo_app = create u_cc_app

   // Window Instance Variable Declaration.

   private:
   boolean ib_legitimate = false

   // Window Open Script.

   li_rc = guo_app.of_get_lock &
      ( "Agreement " + this.is_agreement_id )
   if li_rc <> 1 then
      MessageBox ( "Error", &
         "This Agreement is already open." )
      close ( this )
      return
   end if
   this.ib_legitimate = true

   // Window Close Script.

   if this.ib_legitimate then
      guo_app.of_release_lock &
         ( "Agreement " + this.is_agreement_id )
   end if

The instance variable ib_legitimate indicates whether or not this instance of the window has successfully obtained a lock. A value of false prevents the second window close script from releasing the lock that belongs to the first window instance.

Listing 2 shows the script for of_get_lock inside the global user object u_cc_app. The string argument is first expanded with special characters so there will be no confusion if one value is identical to some substring of a different argument value. That's necessary because the Pos() function is used to check to see if the argument is already stored in the private instance variable zis_locks declared in u_cc_app.

Locking is accomplished by appending the argument to zis_locks so that a later call to Pos() with the same value would cause the function to return -1.

Listing 2: Function of_get_lock

   string ls_lock

   // Calculate special format lock substring.
   ls_lock = "\" &
      + trim ( upper ( as_lock ) ) + "\"

   // Check to see if lock already exists.
   if pos ( this.zis_locks, ls_lock ) > 0 then
      return -1
   end if

   // Add lock to the string of locks.
   this.zis_locks = this.zis_locks + ls_lock

   // Make sure the lock was added OK.
   if ( pos ( this.zis_locks, ls_lock ) ) &
   <> ( len ( this.zis_locks ) &
      - len ( ls_lock ) + 1 ) then
      MessageBox ( "Error", "get lock" )
      halt close
   end if

   return 1

Listing 3 shows how the of_get_lock function inside u_cc_app first looks for the argument value inside the lock string, and then removes it. This function isn't expecting any calls for locks the caller doesn't own, so any error is treated as fatal.

Listing 3: Function of_release_lock

   string ls_lock
   long   li_lock_pos

   // Calculate special format lock substring.
   ls_lock = "\" &
      + trim ( upper ( as_lock ) ) + "\"

   // Look for the lock.
   li_lock_pos = pos ( this.zis_locks, ls_lock )

   // Make sure lock already exists.
   if li_lock_pos = 0 then
      MessageBox ( "Error", "find lock" )
      halt close
   end if

   // Remove lock from lock string.
   this.zis_locks &
      = left ( this.zis_locks, &
               li_lock_pos - 1 ) &
      + mid ( this.zis_locks, &
              li_lock_pos + len ( ls_lock ) )

   // Make sure the lock was removed OK.
   if pos ( this.zis_locks, ls_lock ) <> 0 then
      MessageBox ( "Error", "release lock" )
      halt close
   end if

   return 1

Listing 4 shows a simple extension to the lock mechanism: a function that checks to make sure something isn't already locked. This is done by calling of_get_lock and of_release_lock. If the first call worked then there was no lock in the first place, and the second call releases the lock just obtained.

Listing 4: Function of_check_lock

   integer li_RC

   // First, prove the lock can be had.
   li_RC = this.of_get_lock ( as_lock )

   // Then release it.
   if li_RC = 1 then
      li_RC = this.of_release_lock ( as_lock )
   end if

   return li_RC

Storing these locks in a local string instead of a database has the advantage that all the locks are released if the application shuts down prematurely; e.g., the power fails or a GPF occurs. That's what we want: When the app is gone, so are its locks.

Performance is excellent because the lock string never grows very large. That's because the lock string length changes only in relation to the number of open sheets and that number stays within reason.

With PowerBuilder 5, however, the 59999-character limitation on strings has been removed so this technique is possible even for high volume applications; e.g., local row-level locks on multi-row displays.