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