Controlling SQL Server transactions in VFP6 
Author Message
 Controlling SQL Server transactions in VFP6

I'm developing a VFP6 client-server app with SQL-Server backend data.
My problem concerns rolling
back a transacion after SQL-Server rejects an UPDATE/DELETE/APPEND
action.

I'm experimenting with a small sample program that uses updatable views
to make 2 changes
within a transaction:
   Update 1) Increment a value in 1 table, and then
   Update 2) append a record (with a duplicate key) to a second table.
The APPEND fails, as it should (Duplicate Primary Key), but the first
change doesn't rollback.

I've attempted framing the transaction in each of the following FoxPro
commands:
   1)  BEGIN TRANSACTION -
       END TRANSACTION or ROLLBACK
   2)  SQLEXEC(CHandle,"BEGIN TRANSACTION") -
       SQLEXEC(CHandle,"END TRANSACTION") or SQLEXEC(CHandle,"ROLLBACK
TRANSACTION")
   3)  SQLSETPROP(CHandle,'Transactions',2) -
       SQLRollBack(CHandle) OR SQLCommit(CHandle)

To test each update, I'm issuing Tableupdate() for each change.
(Without this, I don't
get error messages until I move the record pointer or close the view.)
According to
documentation I've read, Tableupdate() should work (and should be
un-doable) within a
transaction.

My complete FoxPro Test program appears below.  
What am I failing to do to assure that the first update will rollback if
the second update
fails?

*************************
** Begin Test program  **
*************************
CLOSE ALL
CLOSE DATABASES
OPEN DATA SQLDATA
****************************************
** Connect to the SQL-Server database **
****************************************
Local OK2Continue
OK2Continue=.T.
DataHandle=SQLCONNECT("CONNECTHB")
IF DataHandle<0
   =MESSAGEBOX("Connection Didn't Work",48,"Connection Failed")
   OK2Continue=.F.
ELSE
   ***********************
   ** Begin Transaction **
   ***********************
   SET MULTILOCKS ON
   IF SQLSETPROP(DataHandle,'Transactions',2)<0
      WAIT WINDOW "Error setting manual transactions"
   ENDIF
   *******************************
   ** change 1 value in TRX table
   *******************************
   Ok2Continue=AdjTRX()

   IF Ok2Continue
      Ok2Continue=AdjCust()   && This should fail-duplicate key
   ENDIF

   IF Ok2Continue
      if SQLCommit(DataHandle)<0
         lnError=AError(laError)
         =MESSAGEBOX("Error "+STR(laError(1),10)+ ;
                     CHR(13)+CHR(13)+ ;
                     laError(2),48,"Cust Update -- Error Condition")
         Tmp=SQLEXEC(DataHandle,"ROLLBACK TRANSACTION")
*!*              ROLLBACK   && ???
      endif
   ELSE

      *********************************
      ** PROBLEM HERE.. ROLLING BACK **
      *********************************
      WAIT WINDOW "Rolling Back"

      IF SQLRollBack(DataHandle)<0   && ???
         wait window "SQLROLLBACK() returned <0"
      ENDIF

   ENDIF

ENDIF   && lOK
close all
close data
RETURN OK2Continue

*************************
** increment TRX_TOTAL **
*************************
PROCEDURE AdjTrx
LOCAL RetThis
RetThis=.T.

SEEKTRX="0000000"
SEEKStation="000"

USE vTRXByID   && use the 1-record-only view
***************************************************
** SET OPTIMISTIC TABLE (5) OR ROW (2) BUFFERING **
** This won't work with Method A:                **
**       BEGIN TRANS - END TRANS/ROLLBACK        **
***************************************************
CURSORSETPROP('Buffering',5)

REPLACE TRX_TOTAL with TRX_TOTAL+1

IF !TableUpdate()
lnError=AError(laError)
=MESSAGEBOX("Error "+STR(laError(1),10)+ ;
            CHR(13)+CHR(13)+ ;
            laError(2),48,"Trx-Update Error Condition")
   RetThis=.F.
**    tablerevert()  && ??? needed ???
ENDIF
RETURN RetThis && FROM AdjTrx

*****************************************
**  ADD CUSTOMER RECORD (this should fail)
******************************************
PROCEDURE AdjCust
LOCAL RetThis
RetThis=.T.

SEEKID="000101"
SEEKSUFFIX="610"

USE vClByID   && use the 1-record-only view
***************************************************
** Set optimistic table (5) or row (2) buffering.**
** This won't work with Method A:                **
**       BEGIN TRANS - END TRANS/ROLLBACK        **
***************************************************
** CURSORSETPROP('Buffering',5)

BROW TITLE "Before Inserting Cust 000101::Suffix 610"

************************************
** make the primary key updatable **
************************************
lcOrigFieldList=CursorGetProp("UpdatableFieldList","vCLByID")
lcFieldList="CNO CSUFFX "+lcOrigFieldList
IF NOT CursorSetProp("UpdatableFieldList",lcFieldList,"vCLByID")
   ??CHR(7)
   =MESSAGEBOX("Can't update Primary Key Field")
   RETURN
ENDIF

INSERT INTO vClByID (CNO,CSUFFX,CFNAME, ;
                     CMNAME,CLNAME,CMR_MRS, ;) ;
             VALUES ("000101","610","CFNAME", ;
                     "CMNAME","CLNAME","CMR_MRS")

IF !TableUpdate()
   lnError=AError(laError)
   =MESSAGEBOX("Error "+STR(laError(1),10)+ ;
               CHR(13)+CHR(13)+ ;
               laError(2),48,"Cust Update -- Error Condition")
   RetThis=.F.
   tablerevert()  && ??? needed ???
ENDIF
RETURN RetThis && FROM AdjCust



Sat, 08 Mar 2003 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. Begin - End Transaction & SQL Server

2. Transactions with VFP & SQL SERVER

3. SQL Server Transactions

4. VFP w/ SQL Server 6.5 - transactions question

5. Looking for info about transfer of data form vfp6 to sql server

6. VFP6 SP3 & SQL Server 7

7. VFP6 -> SQL Server 7.0 updating

8. Help on Update Remote View from VFP6.0 to SQL Server

9. VFP6.0 versus SQL SERVER 7.0

10. VFP6 -> SQL Server 7.0 updating

11. Transactions (updating tables on 2 servers or more)

12. - How to implement efficient transaction logging in VFP6?

 

 
Powered by phpBB® Forum Software