Backing out an update question

[MarkButler]

I have a remote view using MS SQL 2000 database and table. I use a framework and had a problem I was trying to solve regarding updates that did not back out correctly when an error was encountered. I wrote the following small program to test the transaction and subsequent backout when a failure occurs.

===========
CLOSE ALL
CLEAR ALL
CLEAR
SET MULTILOCKS ON
OPEN DATABASE v:\cms\data\cms_r.DBC
SELECT 0
USE cms_r!fieldops_m NODATA && remote SQL 2000 view
CURSORSETPROP('BUFFERING',5)
* set some parameters for the above view
pkvalue='%'
findfo_team='%'
findfo_name='DA%'
=REQUERY()
BEGIN TRANSACTION
REPLACE ALL fo_mi WITH '8' FOR fo_mi=' '
=TABLEUPDATE(.T.,.T.)
ROLLBACK
=REQUERY()
BROWSE
===========

My problem is that when I issue the rollback I would have expected the subsequent browse to show unchanged records. Instead the records are indeed changed and using SQL enterprise manager confirms this. What am I missing?

VFP 7 SP1

TIA
Mark

 

[badukist]

Hi
You must set manual transaction handling for remote view :

lnConHnd = CURSORGETPROP("ConnectHandle")
SQLSETPROP(lnConHnd ,"Transactions",2)

 

[Mike Lewis]

Mark,

The root of the problem is that you are doing the transaction processing locally in VFP, and SQL Server has no way of seeing that. By issuing BEGIN TRANSACTION, etc. in VFP, all that you are doing is applying transaction processing to the view itslef, not to the back end tables.

The ideal solution is to do the updating via a SQL Server stored procedure. However, that would be a big jump from using remote views, so another option would be to follow up Badukist's suggestion of enabling automatic transactions in the connection object.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[craigsboyd]

To follow up with some information from the help file that supports the posts made by both badukist and MikeLewis:


By default, Visual FoxPro automatically wraps every transactable command sent to the remote server in a transaction. This default automatic transaction handling is provided when the Transactions property is set to 1, or DB_TRANSAUTO.

To use automatic transaction mode

Use the DBSETPROP( ) function to set the Transactions property on the connection to 1 or DB_TRANSAUTO.

-or-

Use the SQLSETPROP( ) function to set the Transactions property on the active connection to 1 or DB_TRANSAUTO.
Transaction processing for the remote table is automatically handled.

Note The Visual FoxPro commands BEGIN TRANSACTION and END TRANSACTION create a transaction for the local Visual FoxPro cursor only. They don't extend the transaction to the remote server.

© 2001 Microsoft Corporation. All rights reserved.



Slighthaze = NULL
_{craig1442@mchsi.com}

"Whom computers would destroy, they must first drive mad." - Anon​

 

[MarkButler]

First, thank all of you as you all have contributed. I had been relying on a framework too long and forgot the fundumentals. For any others that may come across this I thought I would post the same code with the suggested modifications. Again thanx...

=====================
CLOSE ALL
CLEAR ALL
CLEAR
SET MULTILOCKS ON
OPEN DATABASE v:\cms\data\cms_r.DBC
SELECT 0
USE cms_r!fieldops_m NODATA && remote SQL 2000 view
CURSORSETPROP('BUFFERING',5)
lnHandle=CURSORGETPROP('ConnectHandle',ALIAS())
SQLSETPROP(lnHandle,'Transactions',2)
* set some parameters for the above view
pkvalue='%'
findfo_team='%'
findfo_name='DA%'
=REQUERY()
BEGIN TRANSACTION
REPLACE ALL fo_mi WITH '8' FOR fo_mi=' '
lLOK=TABLEUPDATE(.T.,.T.)
IF llOK
SQLCOMMIT(lnHandle)
END TRANSACTION
ELSE
SQLROLLBACK(lnHandle)
ROLLBACK
ENDIF
=REQUERY()
BROWSE
=====================