Thursday, January 03, 2008

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.

Labels: ,

1 Comments:

At 1/04/2008 10:39:00 AM, Blogger Babs said...

Rick,

I too had a need for this recently (legacy app with free tables now in VFP9), and it worked great. Thanks for your comments, as they gave me confidence moving forward with this. As it turns out, we're working with another DotNet group on a different need now where they're going to be importing records into these VFP9 free tables and I'm in the process now of sending them the docs on the MAKETRANSACTABLE function.

VFP9 continues to rock! Too bad the DotNet folks can't use SCATTER/GATHER and STRTRAN, as there was a need for that too in this process they're doing! Drop me a line if you know the equivalent in DotNet (C#) 1.1. Thanks!

Michael Babcock
MB Software Solutions, LLC
http://mbsoftwaresolutions.com

 

Post a Comment

<< Home