in the last article in this series i will look at the problem of detecting and resolving what visual foxpro thinks are update conflicts. the idea behind this is that we want to ensure that when vfp reports an update conflict there really is one. the reason that there is any doubt is that vfp will report an update conflict if any change has been made to disk copy of the record that is being saved – whether or not the changes made by the current user actually conflict with saved data or not. as an example consider the following scenario:

·         user #1 opens a  customer record to edit their phone number

·         user #2 opens the same customer record to add the zip + 4 value

·         user #1 saves their change to the phone number

·         user #2 tries to save their change to the zip code, but gets an “update conflict” error even though the value that this user is changing was not affected by the changes that were made and saved by user 1.

with a little effort we can detect and resolve such errors and only reject a user change when it truly conflicts with existing data. to do that we will make use of the ability of visual foxpro to return the curval() and oldval() values for any column (for an explanation of the role of these functions see my december 2005 article on “handling buffered data in visual foxpro”).

the process consists of several steps and is shown here (for clarity) as a simple program with some associated functions though, of course, it could easily be built into a class and instantiated as an object when needed. the program shown here only returns true when conflicts have been resolved. a return value of false indicates that either an error has occurred, or that conflicts remain. in the latter case a cursor contains the details of all unresolved conflicts.

[1]  first we need to check that we have a table name and that it is actually buffered. since, as we will see, the resolution process relies on using tableupdate(), we can only handle conflicts for buffered tables anyway. the program creates a cursor which will be used to record details of any conflicts that cannot be resolved programmatically so that upon completion, they can be presented to the user for review and decision.

lparameters tutable

local llretval, lnbuffmode, lctable, lnoldarea, lnnextrec, lnrows

 

*** check parameters

if empty(tutable)

  *** nothing passed, use current table

  lctable = alltrim( alias() )

  if empty( lctable )

    *** no table, so we can’t track any update conflicts – return

    return llretval

  endif

else

  do case

    case type( "tutable" ) = "c"

      *** assume character string is the required alias

      lctable = alltrim( tutable)

    case type( "tutable" ) = "n"

      *** get the alias for the specified work area

      lctable = alias( tutable )

    otherwise

      *** invalid parameter

      return llretval

  endcase

endif   

 

*** create a local cursor for storing conflicts

create cursor curcflix ( ;

    cfxrecnum  c (  8), ; && conflict number

    cfxfldnam  c (200), ; && field name

    cfxoldval  c (200), ; && original value

    cfxcurval  c (200), ; && current value on disk

    cfxusrval  c (200), ; && change in the buffer

    cfxforcit  n (  1) ) && user defined action

 

*** check buffermode

llretval = .t.

lnbuffmode = cursorgetprop( 'buffering', lctable )

if lnbuffmode < 2

  *** if table is not buffered just return false – we can’t use tableupdate() anyway

  return .f.

else

  *** save current work area and select required table

  lnoldarea =  select()

  select (lctable)

endif

[2]  the next part of the process depends upon the type of buffering that is being used. if the table is row buffered then we only need to check the current row. when table buffering is in effect we need to process all records with pending changes. that means wrapping the row-level check inside code that uses getnextmodified() to find all records with pending changes.

*** if row-buffered, just process this row

if lnbuffmode < 4

  *** row buffering

  llretval = checkrow( recno(), lctable )

else

  *** table buffering - need to find all modified records

  *** which means calling getnextmodified() until it returns 0

  *** indicating that there are no more records with changes

  lnnextrec = 0

  do while .t.

    lnnextrec = getnextmodified( lnnextrec )

    if lnnextrec = 0

      exit

    endif

    *** try and update the record

    llretval = checkrow( lnnextrec, lctable )

    if ! llretval

      *** if failed, exit

      exit

    endif

  enddo

endif

[3] the checkrow() function (or method if you are creating an object from this) is where the real work is done. for each field in the record, this method reads the current user's buffered value, the oldval() and curval() values and passes them through a logic check as follows:

if the user has not changed this field, and the old and current values are identical,

   ignore this field – it will not cause a conflict anyway

else

  if the user has not changed the field, but the old and current values are different,

     update the buffer directly with the current value

  else

    if the user has changed the field, but the value in the buffer is already identical

       to the current value, ignore this change

    else

       this really is a conflict, so record it

but this logic on knowing whether the current user has actually change any given field. to get a list of the fields that vfp regards as having been changed we use a function named getuserchanges() that returns a comma separated list of columns with pending changes:

function getuserchanges

local lcretval, lctable, lcfldstate, lncnt, lcstatus

 

*** initialize the return value

lcretval = ''

 

*** and the current alias - which was handled in the calling code above

lctable = alias()

 

*** first check for fields that vfp sees as having have changed values

lcfldstate = nvl( getfldstate( -1, lctable ), "")

if empty( chrtran( lcfldstate, '1', ''))

  *** nothing but '1', therefore nothing has changed

  return lcretval

endif

 

*** so, we have got at least one changed field! but we need to handle the

*** deleted flag indicator first. we can use "deleted()" as the field name here!

if ! inlist( left( lcfldstate, 1), "1", "3" )

  lcretval = "deleted()"

endif

 

*** now get rid of the deleted flag indicator

lcfldstate = substr( lcfldstate, 2 )

*** get the field names for changed fields

for lncnt = 1 to fcount()

  *** loop through the fields

  lcstatus = substr( lcfldstate, lncnt, 1 )

  if inlist( lcstatus, "2", "4" )

    lcretval = lcretval + iif( ! empty( lcretval ), ",", "") + field( lncnt )

  endif

next

*** return the list of changed fields

return lcretval

notice that we use the native deleted() function as a field name in this function. both curval() and oldval() will accept this as a valid "field name" (returning a logical value indicating whether the field was deleted in the underlying table) so we can actually check for deletions as well as changes to values!

[4] as noted above, the checkrow function gets called once for each row that needs to be validated and is where the decision is made as to whether user intervention is required or not. for rows where a conflict cannot be resolved programmatically we write out the details to the cursor we created earlier. here is the function:

function checkrow( tnrecnum, tctable )

local lncnt, lucurval, luoldval, lnrows, llretval, lcfldlist, lcfldname, luusrval

 

*** force the correct record to be current

select (tctable)

if recno() # tnrecnum

    goto tnrecnum

endif

 

*** get the list of fields changed by the current user

lcfldlist = ""

lcfldlist = thisform.getuserchanges( tctable )

 

*** scan through the fields

for lncnt = 1 to fcount()

  lcfldname = field( lncnt )

  lucurval = curval( field( lncnt ))

  luoldval = oldval( field( lncnt ))

  luusrval = eval( field( lncnt ))

 

  *** will this field cause a conflict?

  if lucurval == luoldval

    *** no changes have been made to the field

    *** so no problem will arise

  else

    *** changes have been made to the field

    if ! field( lncnt ) $ lcfldlist

      *** but the curent user has not modified the field

      *** so we can just update it from curval()

      replace (field(lncnt)) with lucurval

    else

      *** something has changed!  the question is what?

      if eval( field(lncnt) ) == lucurval

        *** user has not actually changed anything

        loop

      else

        *** this is a conflict that we cannot resolve programmatically

        *** so write out the information as character string data to the cursor

       insert into curcflix ( cfxrecnum, cfxfldnam, cfxoldval, cfxcurval, cfxusrval, cfxforcit);

             values ( transform(recno()), lcfldname, transform(luoldval), transform(lucurval), ;

                      transform(luusrval), 2 )

      endif           

    endif

  endif

next

[5] the final section of the main process code merely checks the record count of the conflict cursor to see if anything needs to be done by the user. if there are no records in this cursor, all conflicts have been resolved by forcing the user’s buffer to match the underlying data where there were discrepancies. so we now use a tableupdate() and include the force parameter to update the data and clear the buffers:

*** check the conflict cursor

if reccount( "curcflix") = 0

  *** there are no unresolvable conflicts so just force the update

  llretval = tableupdate( .t., .t., lctable )

else

  go top in curcflix

  llretval = .f.

endif

*** return final status

return llretval

obviously if there are any records in the conflict cursor you would need to allow the user to decide what to do and then either force the update or revert it. but that is an exercise for you, the reader.

3 Responses to Detecting and resolving update conflicts in VFP

  • David Pye says:

    If the field that is being compared has null values in it (or blank?) then the line

    “IF luCurVal == luOldVal”

    is not true if the old value and current value are both null, as you cannot equate nulls.  To get round this problem I use the following line.

    IF luCurVal == luOldVal or (isnull(luCurVal) and isnull(luOldVal)) or (isblank(luCurVal) and isblank(luOldVal))

    Similarly, if the fields that are being compared are memo’s, then an error occurs when trying to save the values into the ‘conflict’ cursor.  I simply have another ‘if’ statement for this type of field and simply store the word ‘changed’ into the cursor for the offending records.

    Hope these comments are useful.
    David Pye

    Hi David, thank you for your input. Of course it is useful – any improvements are always welcome and you certainly have taken care of issues that I neglected when I developed the class. Thanks for sharing

  • Elijah Kihuga says:

    A most informative article. THanks Andy for help over this not-so-easy topic.

  • Jill Lindgren says:

    Thank you so much Andy.  You just solved a problem I have been working on for DAYS.  Being new to working with SQL I have been a little more than frustrated.  When I saw your name next to a response about Update Conflicts I knew I’d be pointed in the right direction.  This code saved me time and saved my sanity (and my husband says our marriage).  Thank you so much for being so giving with your code.  I often feel like I am on an island doing what I do..it’s so good to know there is someone out there like you to count on for guidance once in a while.

    You’re very welcome. Glad I could help a bit. — Andy

Leave a Reply

Your email address will not be published. Required fields are marked *