******************************  
  SET MULTILOCKS ON  
  SET DELETED ON  
  CLOSE DATABASES ALL   
  CLEAR  
    
  oCA=CREATEOBJECT("CACategoriesODBC")  
 * Use ODBC connection to the Northwind database on the local SQL Server as a data source  
  oCA.DataSource=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=(local);Trusted_Connection=Yes; DataBase=Northwind")  
    
    
  IF !oCA.AutoOpen()  
  	AERROR(errs)  
  	DISPLAY MEMORY like errs  
  	return  
  ENDIF  
    
  SELECT (oCA.Alias)  
  LIST  
    
  ? "Add new category"  
  INSERT INTO (oCA.Alias) (Name) VALUES ("New category")  
  ?[TABLEUPDATE(.F.)=],TABLEUPDATE(.F.)  
  LIST  
    
 * delete the new record from the table  
  GO bottom  
  DELETE  
  TABLEUPDATE(.F.)  
    
    
  SQLDISCONNECT(oCA.DataSource)  
  RETURN   
    
    
  DEFINE CLASS CACategoriesODBC AS CursorAdapter  
  	Alias="CACategories"  
    
  	DataSourceType="ODBC"  
  	SelectCmd="select * from categories"  
  	CursorSchema="Id I, Name C(15), Descr M, Pict M"  
    
 	* set update properties  
  	Tables="Categories"  
  	KeyFieldList="Id"  
  	UpdatableFieldList="Name, Descr, Pict"  
  	UpdateNameList="Id Categories.CategoryId, Name Categories.CategoryName,"+;  
  		"Descr Categories.Description, Pict Categories.Picture"  
  	CompareMemo=.F.  
    
 	* Use ConversionFunc property to remove leading and trailing spaces from the Name field before sending   
 	* its value to the server   
  	ConversionFunc="Name AllTrim"  
    
 	* set conflict check type  
  	ConflictCheckType=3  
    
    
  	FUNCTION AutoOpen()  
  		NODEFAULT   
  		RETURN this.CursorFill(.T.) && use schema   
  	ENDFUNC  
    
  	PROCEDURE AfterInsert  
  		LPARAMETERS cFldState, lForce, cInsertCmd, lResult  
  		IF lResult=.T. && get new Id  
  			LOCAL currentArea  
  			currentArea=SELECT()  
    
 			* if insert is successful, update ID field with   
 			* real value using @@IDENTITY variable from the server  
 			* (CategoryId is IDENTITY field)  
  			TRY  
  				IF 1=SQLEXEC(this.DataSource,"SELECT @@IDENTITY as CategoryId","IDRes")  
  					replace id with IDRes.CategoryId IN (This.Alias)  
  					USE IN IDRes  
  				ELSE  
  					AERROR(errs)  
  					DISPLAY MEMORY like errs  
  				ENDIF  
  			FINALLY  
  				SELECT (currentArea)  
  			ENDTRY	  
  		ENDIF  
  	ENDPROC   
  ENDDEFINE  
 ******************************  

  
 ********************************  
 *Here is an example for ADO data source type, the same approach should work for ODBC.  
    
  SET EXCLUSIVE OFF  
  SET MULTILOCKS ON  
  SET DELETED ON  
  CLOSE DATABASES ALL   
    
  CLEAR   
    
  oCA=CREATEOBJECT("CACategoriesAdo2")  
  ?oCA.AutoOpen()  
  WAIT WINDOW "Insert/Update/Delete records in the BROWSE"  
  BROWSE NORMAL  
    
    
  DEFINE CLASS CACategoriesAdo2 AS CursorAdapter  
  	Alias="CACategories"  
    
 	* set data source type, SELECT command and cursor schema  
  	DataSourceType="ADO"  
  	SelectCmd="select CategoryId, CategoryName, Description from categories"  
  	CursorSchema="Id I, Name C(15), Descr M"  
    
    
 	* These properties must be set in order to send updates using ADODB.Command object  
  	InsertCmdDataSourceType="ADO"  
  	UpdateCmdDataSourceType="ADO"  
  	DeleteCmdDataSourceType="ADO"  
    
 	* We will use custom InsertCmd, UpdateCmd and DeleteCmd  
    
 	* Pass ID field as an output parameter to the #CategoryIns procedure,  
 	* It will be automatically updated with new IDENTITY value  
  	InsertCmd="EXEC #CategoryIns ?@CACategories.Id OUTPUT, ?CACategories.Name, ?CACategories.Descr"  
    
  	UpdateCmd="EXEC #CategoryUpd ?CACategories.Id, ?OLDVAL('Name','CACategories'), ?CACategories.Name, ?CACategories.Descr"  
    
  	DeleteCmd="EXEC #CategoryDel ?CACategories.Id, ?OLDVAL('Name','CACategories')"  
    
  	PROCEDURE Init  
  		LOCAL oCon as ADODB.Connection, oRS as ADODB.Recordset, oCom as ADODB.Command  
    
 		* When object is created, create ADO connection object and connect  
 		* to the Northwind database on the local SQL Server  
  		oCon=CREATEOBJECT("ADODB.Connection")  
  		oCon.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=(local)")  
    
 		* Create stored procedure for Insert, return new value for CategoryId field  
 		* using output parameter @ID	  
  		TEXT TO cSQL NOSHOW  
  			CREATE procedure #CategoryIns @id int OUTPUT, @Name varchar(15),   
  											@Descr text  
  			AS  
  			INSERT into Categories (CategoryName,Description)   
  			VALUES (@Name, @Descr)  
    
  			SELECT @id=SCOPE_IDENTITY( )  
  		ENDTEXT  
    
  		oCon.Execute(cSQL)  
    
 		* Create stored procedure for Update, it'll check for update conflicts  
  		TEXT TO cSQL NOSHOW  
  			CREATE procedure #CategoryUpd @id int, @OldName varchar(15),   
  							@Name varchar(15), @Descr text  
  			AS  
  			SET ANSI_NULLS OFF  
    
  			UPDATE Categories SET CategoryName=@Name,Description=@Descr  
  			WHERE CategoryId=@Id AND CategoryName=@OldName  
    
  			IF @@ROWCOUNT=0 AND @@ERROR=0  
  				RAISERROR (' Update conflict.', 16, 1)  
  		ENDTEXT  
    
  		oCon.Execute(cSQL)  
    
 		* Create stored procedure for Delete, it'll check for update conflicts  
  		TEXT TO cSQL NOSHOW  
  			CREATE procedure #CategoryDel @id int, @Name varchar(15)   
  			AS  
  			SET ANSI_NULLS OFF  
    
  			DELETE Categories  
  			WHERE CategoryId=@Id AND CategoryName=@Name  
    
  			IF @@ROWCOUNT=0 AND @@ERROR=0  
  				RAISERROR (' Update conflict.', 16, 1)  
  		ENDTEXT  
    
  		oCon.Execute(cSQL)  
    
 		* Create Recordset object and configure it for better fetch performance,  
 		* we will not use it for updates  
  		oRs=CREATEOBJECT("ADODB.Recordset")  
  		oRS.ActiveConnection=oCon  
  		oRS.CursorLocation= 2  && adUseServer   
  		oRS.CursorType= 0  && adOpenForwardOnly   
  		oRS.LockType= 1  && adLockReadOnly  
    
  		this.DataSource=oRS			  
    
 		* Create the ADODB.Command object to send updates through  
  		oCom=CREATEOBJECT("ADODB.Command")  
  		oCom.ActiveConnection=oCon  
    
  		this.InsertCmdDataSource=oCom  
  		this.UpdateCmdDataSource=oCom  
  		this.DeleteCmdDataSource=oCom  
  	ENDPROC  
    
  	FUNCTION AutoOpen()  
  		NODEFAULT   
  		RETURN this.CursorFill(.T.,.F.,-1) && use schema   
  	ENDFUNC  
    
    
 	* The following events are used only to print  
 	* commands that are being executed by the object  
  	PROCEDURE BeforeInsert   
  		LPARAMETERS cFldState, lForce, cInsertCmd  
  		?  
  		? PROGRAM()  
  		? "cInsertCmd=",cInsertCmd  
  	ENDPROC   
    
  	PROCEDURE BeforeUpdate   
  		LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd  
  		?  
  		? PROGRAM()  
  		? "cUpdateInsertCmd=",cUpdateInsertCmd  
  		? "cDeleteCmd=",cDeleteCmd  
  	ENDPROC   
    
  	PROCEDURE BeforeDelete   
  		LPARAMETERS cFldState, lForce, cDeleteCmd	  
  		?  
  		? PROGRAM()  
  		? "cDeleteCmd=",cDeleteCmd  
  	ENDPROC   
    
  ENDDEFINE  
 ********************************  

  
 **************************************    
  CLOSE DATABASES all    
  SQLDISCONNECT(0)    
  CLEAR    
  SET MULTILOCKS ON    
    
  LOCAL nAutoRefreshConn as Integer    
    
 * Change connection string to connect to any available SQL Server    
  nAutoRefreshConn=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=(local);Trusted_Connection=Yes;")    
  IF (nAutoRefreshConn <1)    
   	? "Failed to connect!!!"    
     AERROR(aerrs)    
     DISPLAY MEMORY LIKE aerrs    
     return    
  ENDIF    
    
  TEXT TO cSQL NOSHOW    
     CREATE TABLE #CAAutoRefreshDemo    
     (    
      f_IDENTITY int NOT NULL IDENTITY PRIMARY KEY,    
      f1 int NOT NULL UNIQUE,    
      f_TIMESTAMP timestamp    
     )    
  ENDTEXT    
    
  IF SQLEXEC(nAutoRefreshConn ,cSQL)!=1    
     ? "Failed to create demo table!!!"    
     AERROR(aerrs)    
     DISPLAY MEMORY LIKE aerrs    
  ENDIF    
    
  LOCAL oCA as CursorAdapter  
    
  oCA=CREATEOBJECT("CursorAdapter")  
    
  oCA.Alias = "CATest"    
  oCA.BufferModeOverride= 5   
  oCA.DataSource= nAutoRefreshConn    
  oCA.DataSourceType="ODBC"    
  oCA.SelectCmd="select * from #CAAutoRefreshDemo"    
  oCA.CursorSchema="f_IDENTITY I, f1 I, f_TIMESTAMP Q(8)"      
  oCA.UseCursorSchema= .T.  
  oCA.Tables="#CAAutoRefreshDemo"    
  oCA.TimestampFieldList="f_TIMESTAMP"  
  oCA.UpdatableFieldList="f1"    
  oCA.UpdateNameList="f1 #CAAutoRefreshDemo.f1, f_TIMESTAMP #CAAutoRefreshDemo.f_TIMESTAMP, f_IDENTITY #CAAutoRefreshDemo.f_IDENTITY"    
    
  oCA.CursorFill()  
    
  ?"1) Refresh IDENTITY field using [SELECT @@IDENTITY] command"  
  oCA.InsertCmdRefreshFieldList="f_IDENTITY"  
  oCA.InsertCmdRefreshCmd="SELECT @@IDENTITY"  
    
  INSERT INTO CATest (f1) VALUES (1)  
  INSERT INTO CATest (f1) VALUES (2)  
  INSERT INTO CATest (f1) VALUES (3)  
    
  IF !TABLEUPDATE(.T.)  
   	? "TABLEUPDATE is failed!!!"    
     AERROR(aerrs)    
     DISPLAY MEMORY LIKE aerrs    
  ELSE  
  	LIST   
  ENDIF  
  ?  
  ?"2) Refresh IDENTITY and TIMESTAMP fields using "  
  ?"   [SELECT f_IDENTITY, f_TIMESTAMP FROM #CAAutoRefreshDemo WHERE f_IDENTITY=@@IDENTITY] command"  
  oCA.InsertCmdRefreshFieldList="f_IDENTITY"  
  oCA.RefreshTimestamp= .T.    
  oCA.InsertCmdRefreshCmd="SELECT f_IDENTITY, f_TIMESTAMP FROM #CAAutoRefreshDemo WHERE f_IDENTITY=@@IDENTITY"  
    
  INSERT INTO CATest (f1) VALUES (40)  
  INSERT INTO CATest (f1) VALUES (50)  
  INSERT INTO CATest (f1) VALUES (60)  
    
  IF !TABLEUPDATE(.T.)  
   	? "TABLEUPDATE is failed!!!"    
     AERROR(aerrs)    
     DISPLAY MEMORY LIKE aerrs    
  ELSE  
  	LIST   
  ENDIF  
    
    
  ?"3) Refresh IDENTITY and TIMESTAMP fields using alternative unique key"  
  oCA.RefreshTimestamp= .F. && just for demo purposes will include f_TIMESTAMP into InsertCmdRefreshFieldList  
  oCA.InsertCmdRefreshFieldList="f_IDENTITY, f_TIMESTAMP "  
  oCA.InsertCmdRefreshKeyFieldList="f1"  
  oCA.InsertCmdRefreshCmd="" && CursorAdapter will generate command automatically   
    
  INSERT INTO CATest (f1) VALUES (700)  
  INSERT INTO CATest (f1) VALUES (800)  
  INSERT INTO CATest (f1) VALUES (900)  
    
  IF !TABLEUPDATE(.T.)  
   	? "TABLEUPDATE is failed!!!"    
     AERROR(aerrs)    
     DISPLAY MEMORY LIKE aerrs    
  ELSE  
  	LIST   
  ENDIF  
    
    
  TABLEREVERT(.T.)  
  SQLDISCONNECT(0)  
  RETURN   
 ************************************************   

  
 ********************************  
  CLEAR  
    
  LOCAL oConn as ADODB.Connection, oRS as ADODB.Recordset, oCom as ADODB.Command  
    
  oConn=CREATEOBJECT("ADODB.Connection")  
  oConn.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=(local)")  
    
  oConn.Execute("create table #test (f1 int IDENTITY)")  
  oConn.Execute("create procedure #test_ins_1 @f1 int OUT as "+;  
  			  " insert into #test DEFAULT VALUES "+;  
  			  " select @f1=SCOPE_IDENTITY()")  
  oConn.Execute("create procedure #test_ins_2 as "+;  
  			  " insert into #test DEFAULT VALUES "+;  
  			  " return SCOPE_IDENTITY()")  
    
  oCom = CREATEOBJECT("Adodb.Command")  
  oCom.ActiveConnection = oConn  
    
  oRS = CREATEOBJECT("Adodb.Recordset")  
  oRS.ActiveConnection = oConn  
    
  LOCAL oCA as CursorAdapter  
    
  oCA=CREATEOBJECT("CursorAdapter")  
  oCA.Alias="test"  
  oCA.DataSourceType="ADO"  
  oCA.DataSource=oRS  
  oCA.SelectCmd="select * from #test"  
  oCA.InsertCmdDataSourceType="ADO"  
  oCA.InsertCmdDataSource=oCom  
  oCA.InsertCmd="EXEC #test_ins_1 ?@test.f1 OUT"  
    
  ?oCA.CursorFill()  
    
  APPEND BLANK  
  APPEND BLANK  
  APPEND BLANK  
    
  oCA.InsertCmd="EXEC ?@test.f1 = #test_ins_2"  
  APPEND BLANK  
  APPEND BLANK  
  APPEND BLANK  
    
  LIST  
    
  ?oCA.CursorRefresh()  
  LIST  
  USE  
 ********************************  

  
  ...Cmd="EXEC ..., ?IIF(GETFLDSTATE('Descr','CACategories')%2=0,CACategories.Descr,NULL), ?GETFLDSTATE(-1,'CACategories')"

  
  ********************************    
  *Here is an example for ADO data source type, when Recordset is used to update data source.    
        
    SET EXCLUSIVE OFF    
    SET MULTILOCKS ON    
    SET DELETED ON    
    CLOSE DATABASES ALL     
        
    CLEAR     
        
    oCA=CREATEOBJECT("CACategoriesAdo2")    
    IF !oCA.AutoOpen()    
    	AERROR(aerrs)  
    	DISPLAY MEMORY LIKE aerrs  
    	RETURN   
    ENDIF  
    WAIT WINDOW "Insert/Update/Delete records in the BROWSE"    
    BROWSE NORMAL    
        
        
    DEFINE CLASS CACategoriesAdo2 AS CursorAdapter    
    	Alias="CACategories"    
        
  	* set data source type, SELECT command and cursor schema    
    	DataSourceType="ADO"    
    	SelectCmd="select CategoryId, CategoryName, Description from categories"    
    	CursorSchema="Id I, Name C(15), Descr M"    
    	Tables ="categories"  
    	UpdatableFieldList = "Name, Descr"  
    	KeyFieldList = "Id"  
    	UpdateNameList = "Id categories.CategoryId, Name categories.CategoryName, Descr categories.Description"  
    
    	InsertCmdRefreshFieldList = "Id"  
        
    	PROCEDURE Init    
    		LOCAL oCon as ADODB.Connection, oRS as ADODB.Recordset    
        
  		* When object is created, create ADO connection object and connect    
  		* to the Northwind database on the local SQL Server    
    		oCon=CREATEOBJECT("ADODB.Connection")    
    		oCon.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=Northwind;Data Source=(local)")    
        
  		* Create Recordset object and configure it for data source update,    
    		oRs=CREATEOBJECT("ADODB.Recordset")    
    		oRS.ActiveConnection=oCon    
    		oRS.CursorLocation = 3  && adUseClient   
    		oRS.CursorType= 1  && adOpenKeyset  
    		oRS.LockType= 3  && adLockOptimistic  
        
    		this.DataSource=oRS			    
    	ENDPROC    
        
    	FUNCTION AutoOpen()    
    		NODEFAULT     
    		RETURN this.CursorFill(.T.,.F.,-1) && use schema     
    	ENDFUNC    
        
        
  	* The following events are used only to print    
  	* commands that are being executed by the object    
    	PROCEDURE BeforeInsert     
    		LPARAMETERS cFldState, lForce, cInsertCmd    
    		?    
    		? PROGRAM()    
    		? "cInsertCmd=",cInsertCmd    
    	ENDPROC     
        
    	PROCEDURE BeforeUpdate     
    		LPARAMETERS cFldState, lForce, nUpdateType, cUpdateInsertCmd, cDeleteCmd    
    		?    
    		? PROGRAM()    
    		? "cUpdateInsertCmd=",cUpdateInsertCmd    
    		? "cDeleteCmd=",cDeleteCmd    
    	ENDPROC     
        
    	PROCEDURE BeforeDelete     
    		LPARAMETERS cFldState, lForce, cDeleteCmd	    
    		?    
    		? PROGRAM()    
    		? "cDeleteCmd=",cDeleteCmd    
    	ENDPROC     
        
    ENDDEFINE    
  ********************************   

CREATE procedure #CategoryUpd @id int, @OldName varchar(15),     
    							@Name varchar(15), @Descr text    
    			AS    
    			SET ANSI_NULLS OFF    
        
    			UPDATE Categories SET CategoryName=@Name,Description=@Descr    
    			WHERE CategoryId=@Id AND CategoryName=@OldName    
        
    			IF @@ROWCOUNT=0 AND @@ERROR=0    
    				RAISERROR (' Update conflict.', 16, 1)

  
 **************************************      
    CLOSE DATABASES all      
    SQLDISCONNECT(0)      
    CLEAR      
    SET MULTILOCKS ON      
        
    LOCAL nAutoRefreshConn as Integer      
        
  * Change connection string to connect to any available SQL Server      
    nAutoRefreshConn=SQLSTRINGCONNECT("DRIVER=SQL Server;SERVER=(local);Trusted_Connection=Yes;")      
    IF (nAutoRefreshConn <1)      
     	? "Failed to connect!"      
       AERROR(aerrs)      
       DISPLAY MEMORY LIKE aerrs      
       return      
    ENDIF      
        
    TEXT TO cSQL NOSHOW      
       CREATE TABLE #CAAutoRefreshDemo      
       (      
        f_IDENTITY int NOT NULL IDENTITY PRIMARY KEY,      
        f1 int NOT NULL UNIQUE,      
        f_TIMESTAMP timestamp      
       )      
    ENDTEXT      
        
    IF SQLEXEC(nAutoRefreshConn ,cSQL)!=1      
       ? "Failed to create demo table!"      
       AERROR(aerrs)      
       DISPLAY MEMORY LIKE aerrs      
    ENDIF      
        
    LOCAL oCA as CursorAdapter    
        
    oCA=CREATEOBJECT("CA_SCOPE_IDENTITY")    
        
    oCA.Alias = "CATest"      
    oCA.BufferModeOverride= 5     
    oCA.DataSource= nAutoRefreshConn      
    oCA.DataSourceType="ODBC"      
    oCA.SelectCmd="select * from #CAAutoRefreshDemo"      
    oCA.CursorSchema="f_IDENTITY I, f1 I, f_TIMESTAMP Q(8)"        
    oCA.UseCursorSchema= .T.    
    oCA.Tables="#CAAutoRefreshDemo"      
    oCA.UpdatableFieldList="f1"      
    oCA.UpdateNameList="f1 #CAAutoRefreshDemo.f1, f_TIMESTAMP #CAAutoRefreshDemo.f_TIMESTAMP, f_IDENTITY #CAAutoRefreshDemo.f_IDENTITY"      
    oCA.KeyFieldList="f_IDENTITY"  
        
    oCA.CursorFill()    
        
    oCA.IDENTITY_Field="f_IDENTITY"    
    oCA.RefreshTimestamp= .F. && just for demo purposes will include f_TIMESTAMP into InsertCmdRefreshFieldList    
    oCA.InsertCmdRefreshFieldList="f_TIMESTAMP "    
        
    INSERT INTO CATest (f1) VALUES (100)    
    INSERT INTO CATest (f1) VALUES (200)    
    INSERT INTO CATest (f1) VALUES (300)    
    INSERT INTO CATest (f1) VALUES (400)    
    INSERT INTO CATest (f1) VALUES (500)    
    INSERT INTO CATest (f1) VALUES (600)    
    INSERT INTO CATest (f1) VALUES (700)    
    INSERT INTO CATest (f1) VALUES (800)    
    INSERT INTO CATest (f1) VALUES (900)    
        
    IF !TABLEUPDATE(.T.)    
     	? "TABLEUPDATE is failed!"      
       AERROR(aerrs)      
       DISPLAY MEMORY LIKE aerrs      
    ELSE    
    	LIST     
    ENDIF    
        
        
    TABLEREVERT(.T.)    
    SQLDISCONNECT(0)    
    RETURN     
    
    
    DEFINE CLASS CA_SCOPE_IDENTITY AS CursorAdapter    
      IDENTITY_Field=""  
        
     PROCEDURE DataSource_ASSIGN   
        LPARAMETERS tAssign    
        this.DataSource = tAssign      
        TRY  
        SQLEXEC(this.DataSource, ;  
        		"create procedure #Get_ValHelper @in Int, @out int OUTPUT " + ;  
        		"AS SET NOCOUNT ON " + ;  
        		"SELECT @out=@in")   
  	  CATCH  
  	  ENDTRY  
     ENDPROC  
        
    	PROCEDURE BeforeInsert     
    		LPARAMETERS cFldState, lForce, cInsertCmd    
    		IF LEN(ALLTRIM(this.IDENTITY_Field))>0  
    			cInsertCmd = cInsertCmd + ;  
    				"; DECLARE @id int; SELECT @id = SCOPE_IDENTITY()" + ;  
    				"; EXEC #Get_ValHelper @id, ?@" + ;  
    				this.Alias + "." + ALLTRIM(this.IDENTITY_Field)  
    		ENDIF  
    		?    
    		? PROGRAM()    
    		? "cInsertCmd=",cInsertCmd    
    	ENDPROC     
        
    ENDDEFINE    
  ********************************