MAKETRANSACTABLE() use

Each time a new version of VFP is released I look at all the new commands and functions to see how I might be able to incorporate them into my user applications and developer tools. Each time I have found some function or command that makes me scratch my head and wonder what the Fox Team had in mind. For VFP 9 this was MAKETRANSACTABLE().

At least this is how I felt about this command up to a month ago. I received a new mini-project from one of my clients who needed a basic import of some accounting data from an external program into their custom app. The external table was a free table created and/or updated by another program. The new records needed to be imported into another DBF contained in a database container. Once the records are imported I needed to stamp them as imported in the free table. Hmmm, sounds like something I would want to do inside a transaction, but I have this free table... ah, finally the perfect reason to use MAKETRANSACTABLE().

The funny thing about this is I have never tried the new command to see if it works {g}. It is rare for me to work with free tables in a production environment. Normally I work with SQL Server, or VFP contained tables.

So I tested it out and it does indeed work, and it works well. The odds of the import not working is very, very remote because I am only adding the new records to the database table (no updates of existing records), but I thought the extra security of doing it inside a transaction was worth the extra couple of commands to add to the code.

Here is some of the code so you can understand fundamentally what I did.

TRY
  USE (toParameter.cFeedFile) IN 0 EXCLUSIVE ALIAS (toParameter.cFeedFileAlias)
  SELECT (toParameter.cFeedFileAlias)
  INDEX ON TimPe TAG TimPe 
   
  * Allow the free table to work in a transaction

  MAKETRANSACTABLE(toParameter.cFeedFileAlias)

  * Set buffering so entire table gets updated or can be reverted
  CURSORSETPROP("Buffering", 5, toParameter.cFeedFileAlias)
   
  **** Open other tables...

CATCH TO loException
  toParameter.lOpenedAll = .F.
  toParameter.cTableUpdatedFailureMsg = "Failed opening tables - " + ;
                                    loException.Message 
ENDTRY

*** Handle processing of tables...

* Start a transaction so all changes can be backed out.

BEGIN TRANSACTION

toParameter.lDatabaseFileTableUpdatedResult = ;
TABLEUPDATE(.T., .F., toParameter.cDataBaseFileAlias)

IF toParameter.lDatabaseFileTableUpdatedResult
  * Continue on with the feed table update
  toParameter.lFeedFileTableUpdatedResult = ;
  TABLEUPDATE(.T., .F., toParameter.cFeedFileAlias)
   
  IF toParameter.lFeedFileTableUpdatedResult
    * All changes committed
    END TRANSACTION
  ELSE
    * Record why
    AERROR(laError)
    toParameter.cTableUpdatedFailureMsg = laError[2]
  ENDIF
ELSE
  * Record why
  AERROR(laError)
  toParameter.cTableUpdatedFailureMsg = laError[2]
ENDIF 

* Determine the way to finish up.
IF toParameter.lDatabaseFileTableUpdatedResult AND ;
  toParameter.lFeedFileTableUpdatedResult
  * All went well and is commited
ELSE 
  * Problems and rollback
  ROLLBACK 
  TABLEREVERT(.T., toParameter.cDataBaseFileAlias)
  TABLEREVERT(.T., toParameter.cFeedFileAlias)
  toParameter.nRecordsInserted = 0 
ENDIF

RETURN

I think it is cool when there are others that have a vision to incorporate things into Visual FoxPro that hit me years later how I can use them.