Concept

As the need for users to have applications (as well as SQL stored procedures and triggers) that modify Control data, it has become necessary to have a mechanism to notify Control of records that have changed.

To handle this, Chapi now monitors the data for refresh notifications.

Stored Procedures

There are SQL stored procedures in the Control data for adding records to the refresh monitor queue.

To update a single record, use the stored procedure called sp_RefreshRecord.

Its parameters are:

~ Parameter Name	~ Data Type	~ Required?
@ID	INT	Yes
@ClassTypeID	INT	Yes
@SeqID	INT	No. The default is -1
@IsDeleted	BIT	No. The default is 0

To update a multiple records at one time, use the stored procedure called sp_RefreshRecords.
Warning: sp_RefreshRecords is not available if you are using SQL Server 2005. It has a single table parameter of type RefreshRecordsTableType. RefreshRecordsTableType has the following fields:

~ Field Name	~ Data Type	~ Notes
ID	INT	PRIMARY KEY
ClassTypeID	INT	NOT NULL
SeqID	INT
IsDeleted	BIT

Examples

sp_RefreshRecord

-- This psuedo example would refresh the last order in the database.
-- The ClassTypeID for orders is 10000
 
DECLARE @ID INT = ( SELECT MAX(ID) FROM TransHeader WHERE TransactionType IN (1,6) );
 
EXEC sp_RefreshRecord @ID, 10000;

sp_RefreshRecords

-- This psuedo example would refresh the last 10 odd numbered orders in the database.
-- The ClassTypeID for orders is 10000
 
DECLARE @t RefreshRecordsTableType
;
INSERT INTO @t (ID, ClassTypeID)
SELECT TOP 10 ID, 10000
FROM TransHeader
WHERE (ID % 2) <> 0
ORDER BY ID DESC
;
EXEC sp_RefreshRecords @t

Notes

If SeqID is -1 (not set), then Control will force a refresh of all objects with the given ID and ClassTypeID. Otherwise, only objects that are out of sync will be refreshed.
Chapi processes changes once every 60 seconds. Hence, it may take up to a minute for the refresh to be seen on all clients.
Post multiple updates on the same record with different sequence IDs will only generate one refresh (which is all that is needed).

Source

Contributor: Greg Schroeder, Cyrious

Date:06/04/2015

Version: Control 5.6T