ÈçºÎͨ¹ý TableUpdate º¯ÊýÓë CursorAdapter ¶ÔÏó¿ÉÊÓ»¯ FoxPro ÖиüÐÂÊý¾Ý

CRLF+[EXECSCRIPT("IF _tally=0" + CHR(10) +
		  "ERROR('Update conflict')" + CHR(10) + "ENDIF")]

#DEFINE CRLF CHR(13)+CHR(10)
Local loCursor,ovfp
CLEAR
 
ON ERROR
 
Set Exclusive Off
Close Databases All
Set Multilocks On
loCursor = Createobject('CA')
* Load loCursor with the data specified in SelectCmd and display error message if error occurs.
loCursor.CursorFill()
GO top
* Display the value of the company name before update.
? "Before:",companyname
?
ovfp=Createobject("visualfoxpro.application.8")
ovfp.DoCmd("set exclusive off")
ovfp.DoCmd("update (_samples+'\northwind\customers') set companyname='Alfreds Futterkisted' where customerid='ALFKI'")
GO top
* Update the data in the cursor.
replace companyname WITH 'Alfreds Futterkiste'
* Update the back end.
retval=TABLEUPDATE(0,.F.,locursor.alias)
Messagebox("Tableupdate="+Transform(retval))
 
* If update conflict occurs, display the error.
if(retval=.F.)
 LOCAL ARRAY errors(1)
 AERROR(errors)
* Displays the errors.

 IF "Update conflict"$errors[2]
  MESSAGEBOX("Update Conflict-reverting changes")
  =TABLEREVERT(.T.,locursor.alias)
 ENDIF
endif
 * Refresh the Cursor to get the updated data.
loCursor.CursorRefresh()  && Get the data again to be sure
GO top
* Display the value of the company name after update.
?
? "After:",companyname
 
Define Class CA As CursorAdapter
 Alias = 'test1'
 DataSourceType = 'NATIVE'
 SelectCmd = 'select * from (_samples+"\northwind\customers")'
 Tables = 'Customers'
 KeyFieldList = "customerid"
 UpdatableFieldList = "companyname"
 UpdateNameList = "customerid customers.customerid,companyname customers.companyname"
 WhereType= 3
	* This is a custom property, that is added to handle update conflicts. It does not do
 * anything by itself. It is added below to the automatically-generated UpdateInsertCmd to
 * test whether anything was actually updated.
 ConflictCheckCmd =CRLF+[EXECSCRIPT("IF _tally=0" + CHR(10) + "ERROR('Update conflict')" + CHR(10) + "ENDIF")]
 
 Procedure AfterUpdate
  Lparameters cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult
  * To see why it will fail on the back end, look at the UpdateInsertCmd that is used
  ? "Update Command sent="+UpdateInsertCmd
	* Swap the actual values in the command to see what occurred.
  UpdateInsertCmd=Strtran(UpdateInsertCmd,[OLDVAL('customerid','test1')],Oldval('customerid','test1'))
  UpdateInsertCmd=Strtran(UpdateInsertCmd,[OLDVAL('companyname','test1')],Oldval('companyname','test1'))
  UpdateInsertCmd=Strtran(UpdateInsertCmd,[test1.companyname],test1.companyname)
  ? "With the OLDVAL() and test1.companyname evaluated the update statement is :"+UpdateInsertCmd
  * Check tally.
  ? "Tally="+Transform(_Tally)
 
 Procedure BeforeUpdate
  Lparameters cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd
  cUpdateInsertCmd=cUpdateInsertCmd+this.ConflictCheckCmd
 
ENDDEFINE

IF @@ROWCOUNT=0 RAISERROR (' Update
		  conflict.', 16, 1)

LOCAL loCursor,ovfp,nhnd,lsuccess

CLEAR
SET EXCLUSIVE OFF
CLOSE DATABASES ALL
SET MULTILOCKS ON

loCursor = CREATEOBJECT('CA')

* Load loCursor with the data specified in SelectCmd and display error message if error occurs.
IF !loCursor.CursorFill()
	=AERROR(lar)
	MESSAGEBOX(lar[2]) 
ENDIF 


* Display the value of the company name before update.
? "Company Name Before Update:",companyname
?
* Create a connection handle for SQL Server so you can set up the update conflict.
nhnd=SQLSTRINGCONNECT([Driver=SQL Server; SERVER=<SQL SERVER NAME>; DATABASE=NORTHWIND])
=SQLEXEC(nhnd,[update customers set companyname='Alfreds Futterkiste' where customerid='ALFKI'])
=SQLDISCONNECT(nhnd)

* Now make a change to the local data, and then try to update it.
GO TOP
REPLACE companyname WITH 'Alfreds Futterkisted'

lsuccess=TABLEUPDATE(0,.F.,locursor.alias)
Messagebox("Tableupdate="+Transform(lsuccess))
* Error handling function. Displaying the error message if update conflict occurs.
IF !lsuccess
	=AERROR(lar)
	IF "Update conflict"$lar[2]
		MESSAGEBOX("Update conflict!-Reverting changes")
		=TABLEREVERT(.f.,locursor.alias)
	ENDIF
ENDIF  

* Get the current data from the CursorAdapter.
loCursor.CursorRefresh()  
GO TOP

* Displaying the value of the company name after update.
?
?"Company Name After Update:", companyname

DEFINE CLASS CA AS CursorAdapter
	Alias = 'test1'
	SelectCmd = 'select * from customers'
	Tables = 'Customers'
	KeyFieldList = "Customerid"
	UpdatableFieldList = "companyname"
	UpdateNameList = "customerid customers.customerid,companyname customers.companyname"
	WhereType= 3 && Key and modified 
	
	* This is a custom property that is added to handle update conflicts. It does not do
   	* anything by itself. It is added below to the automatically-generated UpdateInsertCmd to
   	* test whether anything was actually updated.
	ConflictCheckCmd =";IF @@ROWCOUNT=0 RAISERROR (' Update conflict.', 16, 1)"
	
	
	* Initializing the connectivity to Data source (SQL Server) by using ODBC driver.
	PROCEDURE init 
		WITH THIS
			.DataSourceType = 'ODBC'
			.DataSource=SQLSTRINGCONNECT([Driver=SQL Server; SERVER=<SQL SERVER NAME>; DATABASE=NORTHWIND])
		ENDWITH
	ENDPROC
	
	PROCEDURE BeforeUpdate
		LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd
		
		? "Entering BeforeUpdate()"
		
		cUpdateInsertCmd=cUpdateInsertCmd + THIS.ConflictCheckCmd
	ENDPROC
	
	
	PROCEDURE AfterUpdate

		LPARAMETERS cFldState, lForce, nUpdateType, UpdateInsertCmd, DeleteCmd, lResult

		* To see why it will fail on the back-end, look at the UpdateInsertCmd that is used.
		? "Update Command sent="+UpdateInsertCmd

		* Swap the actual values in the command to see what occurred.
		lcActualCmd =Strtran(UpdateInsertCmd,[OLDVAL('customerid','test1')],Oldval('customerid','test1'))
		lcActualCmd =Strtran(UpdateInsertCmd,[OLDVAL('companyname','test1')],Oldval('companyname','test1'))
		lcActualCmd =Strtran(UpdateInsertCmd,[test1.companyname],test1.companyname)

		? "With the OLDVAL() and test1.companyname evaluated the update statement is :"+UpdateInsertCmd
		?
		? "Leaving AfterUpdate()"
	ENDPROC
	
	* Destroying the connection.
	PROCEDURE destroy 
		=SQLDISCONNECT(THIS.DataSource)
	ENDDEFINE