Rollback Issue on Remote View (SQL Backend)

[cmoreagain]

Does the ROLLBACK command work on remote views? My backend database is SQL I’m using remote views to access and update it. I’ve set buffering mode of the remote view to 5 so I can do a tableupdate and tablerevert. In my saving module I start with begin transaction and test if the tableupdate is successful or else I’ll rollback. The view rolls back but the backend database still gets updated.

Begin Transaction

Select RemoteView1
Replace test With 'x'
If !(Tableupdate(.T.))
Rollback
Sele RemoteView1
=Tablerevert(.T.)
Else
End Transaction
Endif

Requery('RemoteView1')

 

[JackTheC]

BEGIN TRANSACTION and ROLLBACK or END TRANSACTION only work on VFP's DBF-tables (or local cursors/views)

If you want to use transaction on SQL you should ALSO use
so called manual transactions on SQL and
sqlcommit() or sqlrollback

p.e.:

mc=cursorgetprop('connecthandle') && handle of the view
sqlsetprop(mc,'transactions',2) && manual transaction

begin transaction && VFP transactie
delete
nc0=tableupdate(.f.,.t.)

* subtables via SPT
nc=sqlstringconnect(MZConnString) && handle of SQL PassTrough (SPT)
sqlsetprop(nc,'transactions',2) && man.transaction

nc1=sqlexec( nc,"delete from subtable1 where sofi=?zsofi and volgnr=?gekvolgnr&quot
nc2=sqlexec( nc,"delete from subtable2 where sofi=?zsofi and volgnr=?gekvolgnr&quot

if nc0 and nc1+nc2=2 && all went well
sqlcommit(nc) && SPT
sqlcommit(mc) && View
end transaction && VFP
else
recall
sqlrollback(nc) && SPT
sqlrollback(mc) && View
rollback && VFP
=messagebox('error message')
endif
sqlcancel(nc)
sqldisconnect(nc)

 

[Mike Lewis]

CMoreAgain,

I agree with JackTheC ..... or, you can go one step further. Write the update as a stored procedure on the server. Put your BEGIN TRANSACTION, ROLLBACK, etc. in the stored procedure. That is somewhat more efficient than using VFP's SQLROLLBACK() etc.

Mike


Mike Lewis
Edinburgh, Scotland

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

 

[cmoreagain]

JackTheC MikeLewis
Thanx for stearing me guys to the right direction. I'll try the manual transaction setting for the connection handle i used for my views first, I'm not yet familiar on how to implement stored procedures but i'll later explore it though.