Hi Patrick,
You don't have to wait for the final release and you don't have to use ADO to try the AutoRefresh functionality.
Mark's example sets INSERTCMDREFRESHCMD property to an incorrect command:
INSERTCMDREFRESHCMD = [SELECT KeyID, Last_Update FROM Presidents]
This doesn't look like a command to refresh a record, there is no where clause, it returns all records from the table. Thus the error "refresh key is not unique". The example works fine for Mark because for that particular case the AutoRefresh is performed through ADODB.Recordset (AutoRefresh always uses the same data source that is used for the TABLEUPDATE) and CursorAdapter doesn't use the command. If inserts were sent through ADODB.Command object, Mark would get similar error.
Below are examples for all three data source types.
An example of how to use AutoRefresh with ODBC data source:
CLOSE DATABASES all
SQLDISCONNECT(0)
CLEAR
SET MULTILOCKS ON
LOCAL nAutoRefreshConn as Integer
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.
oCA.InsertCmdRefreshFieldList="f_IDENTITY, f_TIMESTAMP "
oCA.InsertCmdRefreshKeyFieldList="f1"
oCA.InsertCmdRefreshCmd=""
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
Output:
---------------------------------------------
1) Refresh IDENTITY field using [SELECT @@IDENTITY] command
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
2) Refresh IDENTITY and TIMESTAMP fields using
[SELECT f_IDENTITY, f_TIMESTAMP FROM #CAAutoRefreshDemo WHERE f_IDENTITY=@@IDENTITY] command
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
4 4 40 0h00000000000000CA
5 5 50 0h00000000000000CB
6 6 60 0h00000000000000CC
3) Refresh IDENTITY and TIMESTAMP fields using alternative unique key
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
4 4 40 0h00000000000000CA
5 5 50 0h00000000000000CB
6 6 60 0h00000000000000CC
7 7 700 0h00000000000000CD
8 8 800 0h00000000000000CE
9 9 900 0h00000000000000CF
---------------------------------------------An example of how to use AutoRefresh with ADO data source:
CLOSE DATABASES all
CLEAR
SET MULTILOCKS ON
LOCAL oCon as ADODB.Connection, oRS as ADODB.Recordset, oCom as ADODB.Command
oCon = CREATEOBJECT("ADODB.Connection")
oCon.Open("Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=(local)")
TEXT TO cSQL NOSHOW
CREATE TABLE #CAAutoRefreshDemo
(
f_IDENTITY int NOT NULL IDENTITY PRIMARY KEY,
f1 int NOT NULL UNIQUE,
f_TIMESTAMP timestamp
)
ENDTEXT
oCon.Execute(cSQL)
oRs = CREATEOBJECT("ADODB.Recordset")
oRs.ActiveConnection=oCon
oCom = CREATEOBJECT("ADODB.Command")
oCom.ActiveConnection=oCon
LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.Alias = "CATest"
oCA.BufferModeOverride= 5
oCA.DataSource= oRs
oCA.DataSourceType="ADO"
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.InsertCmdDataSource=oCom
oCA.InsertCmdDataSourceType="ADO"
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.
oCA.InsertCmdRefreshFieldList="f_IDENTITY, f_TIMESTAMP "
oCA.InsertCmdRefreshKeyFieldList="f1"
oCA.InsertCmdRefreshCmd=""
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.)
USE
?"4) Refresh IDENTITY and TIMESTAMP fields through ADODB.Recordset"
oRS.CursorLocation= 3
oRS.LockType= 3
oCA.InsertCmdDataSource=NULL
oCA.InsertCmdDataSourceType=""
oCA.Tables=""
oCA.UpdateNameList=""
oCA.InsertCmdRefreshKeyFieldList=""
oCA.InsertCmdRefreshCmd=""
oCA.CursorFill()
INSERT INTO CATest (f1) VALUES (1000)
INSERT INTO CATest (f1) VALUES (2000)
INSERT INTO CATest (f1) VALUES (3000)
IF !TABLEUPDATE(.T.)
? "TABLEUPDATE is failed!!!"
AERROR(aerrs)
DISPLAY MEMORY LIKE aerrs
ELSE
LIST
ENDIF
TABLEREVERT(.T.)
RETURN
Output:
---------------------------------------------
1) Refresh IDENTITY field using [SELECT @@IDENTITY] command
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
2) Refresh IDENTITY and TIMESTAMP fields using
[SELECT f_IDENTITY, f_TIMESTAMP FROM #CAAutoRefreshDemo WHERE f_IDENTITY=@@IDENTITY] command
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
4 4 40 0h000000000000014B
5 5 50 0h000000000000014C
6 6 60 0h000000000000014D
3) Refresh IDENTITY and TIMESTAMP fields using alternative unique key
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h
2 2 2 0h
3 3 3 0h
4 4 40 0h000000000000014B
5 5 50 0h000000000000014C
6 6 60 0h000000000000014D
7 7 700 0h000000000000014E
8 8 800 0h000000000000014F
9 9 900 0h0000000000000150
4) Refresh IDENTITY and TIMESTAMP fields through ADODB.Recordset
Record# F_IDENTITY F1 F_TIMESTAMP
1 1 1 0h0000000000000148
2 2 2 0h0000000000000149
3 3 3 0h000000000000014A
4 4 40 0h000000000000014B
5 5 50 0h000000000000014C
6 6 60 0h000000000000014D
7 7 700 0h000000000000014E
8 8 800 0h000000000000014F
9 9 900 0h0000000000000150
10 10 1000 0h0000000000000151
11 11 2000 0h0000000000000152
12 12 3000 0h0000000000000153
---------------------------------------------An example of how to use AutoRefresh with NATIVE data source:
CLOSE DATABASES all
CLEAR
SET MULTILOCKS ON
CREATE CURSOR CAAutoRefreshDemo ;
( ;
f_AUTOINC I NOT NULL AUTOINC, ;
f1 I NOT NULL UNIQUE, ;
f_TIMESTAMP T DEFAULT DATETIME();
)
CREATE CURSOR OneRecordCursor (ff c(1))
APPEND BLANK
LOCAL oCA as CursorAdapter
oCA=CREATEOBJECT("CursorAdapter")
oCA.Alias = "CATest"
oCA.BufferModeOverride= 5
oCA.DataSourceType="NATIVE"
oCA.SelectCmd="select * from CAAutoRefreshDemo"
oCA.Tables="CAAutoRefreshDemo"
oCA.UpdatableFieldList="f1"
oCA.UpdateNameList="f1 CAAutoRefreshDemo.f1, f_TIMESTAMP CAAutoRefreshDemo.f_TIMESTAMP, f_AUTOINC CAAutoRefreshDemo.f_AUTOINC"
oCA.CursorFill()
?"1) Refresh AUTOINC field using [SELECT GETAUTOINCVALUE(0) FROM OneRecordCursor] command"
oCA.InsertCmdRefreshFieldList="f_AUTOINC"
oCA.InsertCmdRefreshCmd="SELECT GETAUTOINCVALUE(0) FROM OneRecordCursor"
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 AUTOINC and TIMESTAMP fields using "
?" [SELECT f_AUTOINC, f_TIMESTAMP FROM CAAutoRefreshDemo WHERE f_AUTOINC=GETAUTOINCVALUE(0)] command"
oCA.InsertCmdRefreshFieldList="f_AUTOINC,f_TIMESTAMP"
oCA.InsertCmdRefreshCmd="SELECT f_AUTOINC, f_TIMESTAMP FROM CAAutoRefreshDemo WHERE f_AUTOINC=GETAUTOINCVALUE(0)"
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
WAIT TIMEOUT 2
?"3) Refresh AUTOINC and TIMESTAMP fields using alternative unique key"
oCA.InsertCmdRefreshFieldList="f_AUTOINC, f_TIMESTAMP "
oCA.InsertCmdRefreshKeyFieldList="f1"
oCA.InsertCmdRefreshCmd=""
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.)
RETURN
Output:
---------------------------------------------
1) Refresh AUTOINC field using [SELECT GETAUTOINCVALUE(0) FROM OneRecordCursor] command
Record# F_AUTOINC F1 F_TIMESTAMP
1 1 1 / / : : AM
2 2 2 / / : : AM
3 3 3 / / : : AM
2) Refresh AUTOINC and TIMESTAMP fields using
[SELECT f_AUTOINC, f_TIMESTAMP FROM CAAutoRefreshDemo WHERE f_AUTOINC=GETAUTOINCVALUE(0)] command
Record# F_AUTOINC F1 F_TIMESTAMP
1 1 1 / / : : AM
2 2 2 / / : : AM
3 3 3 / / : : AM
4 4 40 10/30/04 12:25:31 AM
5 5 50 10/30/04 12:25:31 AM
6 6 60 10/30/04 12:25:31 AM
Press any key to continue ...
3) Refresh AUTOINC and TIMESTAMP fields using alternative unique key
Record# F_AUTOINC F1 F_TIMESTAMP
1 1 1 / / : : AM
2 2 2 / / : : AM
3 3 3 / / : : AM
4 4 40 10/30/04 12:25:31 AM
5 5 50 10/30/04 12:25:31 AM
6 6 60 10/30/04 12:25:31 AM
7 7 700 10/30/04 12:25:33 AM
8 8 800 10/30/04 12:25:33 AM
9 9 900 10/30/04 12:25:33 AM
---------------------------------------------Thanks,
Aleksey.
