Original from MS
CLOSE DATABASES all
CLEAR
LOCAL con as ADODB.Connection, rs as ADODB.Recordset, com as ADODB.Command
con=CREATEOBJECT("ADODB.Connection")
con.Open("Provider=SQLOLEDB;Server=(local);UID=;PWD=;trusted_connection=yes")
com=CREATEOBJECT("ADODB.Command")
com.ActiveConnection=con
rs=CREATEOBJECT("ADODB.Recordset")
rs.ActiveConnection=con
* create table and procedures
TEXT to cSql NOSHOW
CREATE TABLE #test (fk int, f1 int, f2 varchar(10))
INSERT into #test values (1,1,'old1')
INSERT into #test values (1,2,'old2')
INSERT into #test values (2,1,'old1')
INSERT into #test values (2,2,'old2')
ENDTEXT
com.CommandText=cSql
com.Execute()
TEXT to cSql NOSHOW
CREATE PROCEDURE #test_select @fk int AS
SELECT f1,f2 from #test where fk=@fk
ENDTEXT
com.CommandText=cSql
com.Execute()
TEXT to cSql NOSHOW
CREATE PROCEDURE #test_insert @fk int, @f1 int, @f2 varchar(10) AS
INSERT into #test values (@fk,@f1,@f2)
ENDTEXT
com.CommandText=cSql
com.Execute()
TEXT to cSql NOSHOW
CREATE PROCEDURE #test_update @fk int, @oldf1 int, @f1 int, @f2 varchar(10) AS
update #test set f1=@f1, f2=@f2 where fk=@fk and f1=@oldf1
ENDTEXT
com.CommandText=cSql
com.Execute()
TEXT to cSql NOSHOW
CREATE PROCEDURE #test_delete @fk int, @oldf1 int AS
delete from #test where fk=@fk and f1=@oldf1
ENDTEXT
com.CommandText=cSql
com.Execute()
* set up cursor adapter to use procedures and ADO
LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.Alias="test"
oCA.DataSourceType="ADO"
oCA.DataSource=rs
oCA.SelectCmd="exec #test_select @fk=?this.Fk"
oCA.AddProperty("Fk",1)
oCA.InsertCmdDataSourceType="ADO"
oCA.InsertCmdDataSource=com
oCA.InsertCmd="exec #test_insert @fk=?this.Fk, @f1=?test.f1, @f2=?test.f2"
oCA.UpdateCmdDataSourceType="ADO"
oCA.UpdateCmdDataSource=com
oCA.UpdateCmd="exec #test_update @fk=?this.Fk, @oldf1=?OLDVAL('f1','test'),@f1=?test.f1, @f2=?test.f2"
oCA.DeleteCmdDataSourceType="ADO"
oCA.DeleteCmdDataSource=com
oCA.DeleteCmd="exec #test_delete @fk=?this.Fk, @oldf1=?OLDVAL('f1','test')"
oCA.BufferModeOverride= 5
rs.CursorLocation= 3 && adUseClient
oCA.CursorFill(.F.,.F.,-1,com)
SELECT (oCA.Alias)
LIST
*insert new record
INSERT into test values (3,"inserted")
*update existing record
UPDATE test set f1=24, f2="updated" where f1=2
*delete existing record
DELETE FROM test where f1=1
IF !TABLEUPDATE(.T.)
AERROR(aerrors)
DISPLAY MEMORY LIKE aerrors
ENDIF
IF REQUERY()=0
AERROR(aerrors)
DISPLAY MEMORY LIKE aerrors
ENDIF
LIST
TABLEREVERT(.T.)
oCA.CursorFill(.F.,.F.,-1,com)
SELECT (oCA.Alias)
LIST
return