Begin - End Transaction & SQL Server 
Author Message
 Begin - End Transaction & SQL Server

Hi,

   I have a bit of a problem here.  I've got a VFP form with a Grid and two
buttons - SAVE and EXIT.  Basically the user can add, delete or modify the
data in the grid and click on SAVE to have the work saved then EXIT to exit
the screen.  The data environment is set to OPTIMISTIC TABLE BUFFERING &
PRIVATE DATA SESSION and so is the table set to optimistic table buffering.
If the table is a VFP table or view, the code in the SAVE button will save
or rollback/revert the changes if the primary key is violated.  If however,
I make it a REMOTE VIEW to an SQL Server 7 table, the rollback does not work
as I hoped it would.  Here is the code from the SAVE button...

BEGIN TRANSACTION
SELECT tr_tests
x=TABLEUPDATE(2)
IF x=.f.
 ROLLBACK
 x=TABLEREVERT(.T.)
ELSE
 END TRANSACTION
ENDIF

I input the following DATA (each entry should be unique)...
ABC
123
XYZ
ABC
AAA
BBB

What happens is that the first three get sent to the server, the fourth
breaks the unique key so it causes an error at the TABLEUPDATE and no others
get through.  I need to ROLLBACK all records for that session.  Is there any
way of doing this with the Remote views in a data environment?  I know there
are ways to get around the breaking of the unique key, my problem isn't with
the design of the program, rather the behavior and outcome of the resulting
data on the server.  This is a far simpler version of what the real program
is all about.  Any help is much appreciated.

Thanks in advance!
Renato Da Conceicao
Programmer Analyst
Seabridge International Shipping Inc.



Sat, 13 Mar 2004 23:46:35 GMT  
 Begin - End Transaction & SQL Server
I didn't get any help but managed to solve this problem on my own so I
thought I might as well post an answer to my own question in case...

A-I'm totally wrong
  or
B-it may actually help someone else.

Here is the code I came up with in my test program:

* After the records are added in or chaged in the three remote views
* this code is executed to save the data.
* The three remote views all share an ODBC connection and have
* Optimisitc table buffering enabled.
* I use the connection handle to set the transactions to manual
* since usually they are set to automatic at remote view design time.
* In the split second that it takes to do the TABLEUPDATE, the
* program can either ROLLBACK or simply END TRANSACTION and then
* set the Transactions back to AUTO thereby freeing up any SQL server
* resources that are locked during this critical point.  As long as
* nothing crashes between the TABLEUPDATE and SQLROLLBACK/SQLSETPROP
* others on the network accessing the same tables should not notice anything
wrong.
SELECT tr_tests
hndl=CURSORGETPROP("ConnectHandle")
=SQLSETPROP(hndl,"Transactions", 2)
BEGIN TRANSACTION
SELECT tr_tests
update_1=TABLEUPDATE(2)
SELECT tr_t1
update_2=TABLEUPDATE(2)
SELECT tr_t2
update_3=TABLEUPDATE(2)
IF update_1=.f. OR update_2=.F. OR update_3=.F.
 =SQLROLLBACK(hndl) && Roll back the data on the server for all three tables
 ROLLBACK  && Rollback / End Transaction locally on the remote views
 SELECT tr_tests
 =TABLEREVERT(.T.)  && Revert changes to the first remote view
 SELECT tr_t1
 =TABLEREVERT(.T.)  && Revert changes to the second remote view
 SELECT tr_t2
 =TABLEREVERT(.T.)  && Revert changes to the third remote view
ELSE
 END TRANSACTION
ENDIF
=SQLSETPROP(hndl,"Transactions", 1)


Quote:
> Hi,

>    I have a bit of a problem here.  I've got a VFP form with a Grid and
two
> buttons - SAVE and EXIT.  Basically the user can add, delete or modify the
> data in the grid and click on SAVE to have the work saved then EXIT to
exit
> the screen.  The data environment is set to OPTIMISTIC TABLE BUFFERING &
> PRIVATE DATA SESSION and so is the table set to optimistic table
buffering.
> If the table is a VFP table or view, the code in the SAVE button will save
> or rollback/revert the changes if the primary key is violated.  If
however,
> I make it a REMOTE VIEW to an SQL Server 7 table, the rollback does not
work
> as I hoped it would.  Here is the code from the SAVE button...

> BEGIN TRANSACTION
> SELECT tr_tests
> x=TABLEUPDATE(2)
> IF x=.f.
>  ROLLBACK
>  x=TABLEREVERT(.T.)
> ELSE
>  END TRANSACTION
> ENDIF

> I input the following DATA (each entry should be unique)...
> ABC
> 123
> XYZ
> ABC
> AAA
> BBB

> What happens is that the first three get sent to the server, the fourth
> breaks the unique key so it causes an error at the TABLEUPDATE and no
others
> get through.  I need to ROLLBACK all records for that session.  Is there
any
> way of doing this with the Remote views in a data environment?  I know
there
> are ways to get around the breaking of the unique key, my problem isn't
with
> the design of the program, rather the behavior and outcome of the
resulting
> data on the server.  This is a far simpler version of what the real
program
> is all about.  Any help is much appreciated.

> Thanks in advance!
> Renato Da Conceicao
> Programmer Analyst
> Seabridge International Shipping Inc.



Tue, 16 Mar 2004 04:13:09 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. BEGIN / END Transaction

2. "Begin/End Transaction" Question

3. Begin/End Transaction - Sample Code

4. Insert record problems w/ Begin/End Transaction

5. How to use Begin..End Transaction against two databases

6. begin end transaction is Slow vfp7

7. Begin-end transaction limit

8. Buffering With Begin/End Transaction

9. REPOST - Begin/End Transaction

10. Begin/End transaction problem

11. Transactions with VFP & SQL SERVER

12. VFP 6.0 Index open problem with Begin Transaction

 

 
Powered by phpBB® Forum Software