Thread: FoxPro Some General Questions/Using CursorAdapter with stored procedures

Using CursorAdapter with stored procedures
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