******************************
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  
 ********************************