Database change detection

 

Use this dialog to enable automatic database change detection.

You must create a stored procedure or SQL Statement returning only changed records.

 

Enable detection

Must be checked to enable auto detection.

 

Execute every X seconds

This number determinates execution frequency (in background) of your SQL statement.

 

 Advanced registry setting (Changing this value, can cause database deadlocks):

Set Max. Number of simultaneous SQL executions for DB Detection threads.

You MUST restart Outlook when changing this value!

Path: HKEY_CURRENT_USER\Software\Genius@Work\GeniusConnect\Settings\AdminOptions

Key: MaxDBDetectExec

Type: REG_DWORD

Value: This value must be greater than or equal to one (1 decimal)

3=Default

 

Insert Parameters
Use this button to insert dynamic parameters. (See topic Filter rows for param. description)

 

Test…

Use this button to test your SQL / stored procedure.
GeniusConnect will replace dynamic parameters with runtime values and execute the statement.

 

Change detection SQL command

This command can be any SQL command or stored procedure returning data in the following format:

Transaction nr, Change Type, Primary key 1, Primary key X….

 

Transaction number

Data type: any
Column Name: any

Value: The value must be unique (no two transaction records may have same value)

 

Change Type
Data type: char, length 1
Column Name: any
Value: i, u, d or r
i=insert (a new record has been inserted)

u=update (a record has been updated)

d=delete (a records has been deleted)

r=refresh (refresh all database records, same as Load All from database)
(primary key columns will be ignored with ‘r’ transactions)
It can be faster to refresh all data then huge number of individual transactions.

Primary key
Data type: same as your main table (table linked to the Outlook folder)
Column Name: same as your main table

Value: The value must be unique (no two records may have same value)

 

If your main table uses 1 primary key, result must return 1 primary key.
If your main table uses X primary keys, result must return X primary keys.

 

Change processed SQL command (optional)
This command can be any SQL command or stored procedure accepting Transaction number as parameter. Tag value: {_DETECT_TRAN_NR}.

You can use also any other dynamic parameters.
GeniusConnect will replace the tag {_DETECT_TRAN_NR} with real transaction number before executing the SQL command.

You do not need to enter this SQL command if your detection command (previous field) has mechanism to mark processed transactions.

Detection steps

  1. GeniusConnect detection process executes your detection command
  2. Result(s) will be transferred to the main (Outlook) process and GeniusConnect will start processing
  3. GeniusConnect detection process executes your “Change processed SQL command”(if any) with transaction numbers of the successfully transferred transactions.
  4. GeniusConnect detection process waits X seconds (see Execute every X seconds) before starting again (step 1)

 

Note:
The transactions will not be processed or step 3 will not be executed if:

    • User aborts execution during processing
    • User exits Outlook during processing

 

 

Database change detection Examples

To implement change detection, you must implement:

  1. mechanism to collect data changes in your table
  2. implement a SQL command to return the results to GeniusConnect
  3. implement a mechanism to mark processed transactions

 

Implementation depends on:

  1. your data model
  2. your database system (SQL Server, Oracle etc..)
  3. number of processes changing your data
  4. number of Outlook Folders using the same table and detection mechanism

 

Example is based on MS SQL Server 2005 and Calendar example table. See Microsoft SQL Server

 

Example scenario

  • Multiple user calendar folders are mapped to the table Calendar
  • All users can add, change and delete calendar items
  • Outlook Folder (full path and name) column is mapped to Calendar column gc_OutlookFolder
  • CalendarSignal table receives data from triggers implemented on Calendar table
  • Outlook user changing the data (in Outlook) must not receive his own changes (from DB detection SQL command)
  • Detection SQL command marks processed transactions per Folder and host PC

 

Database change detection Example SQL Scripts

(example stores HOST_NAME() to gc_orgPcName column. To detect changes made from the same PC but from a different process(not GeniusConnect) you can use for example: HOST_NAME() +'-'+APP_NAME())

Signal table
Collect all changes in CalendarSignal table

CREATE TABLE [dbo].[CalendarSignal](

 [TranNr] [int] IDENTITY(1,1) NOT NULL,

 [TranType] [char](1) NOT NULL,

 [gc_id] [uniqueidentifier] NULL,

 [gc_orgFolder] [varchar](100) NULL,

 [gc_orgPcName] [varchar](100) NULL,

 [TranDateTime] [datetime] NULL CONSTRAINT [DF_CalendarSignal_TranDateTime] DEFAULT (getdate()),

CONSTRAINT [PK_CalendarSignalTable] PRIMARY KEY CLUSTERED

(

 [TranNr] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]


Mark process Transaction in CalendarSignalDone table

CREATE TABLE [dbo].[CalendarSignalDone](

 [LastTranNr] [int] NULL,

 [gc_orgFolder] [varchar](100) NOT NULL,

 [gc_orgPcName] [varchar](100) NOT NULL,

CONSTRAINT [PK_CalendarSignalDone_1] PRIMARY KEY CLUSTERED

(

 [gc_orgFolder] ASC,

 [gc_orgPcName] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

 

Triggers to insert the changes in CalendarSignal table

 

CREATE TRIGGER [dbo].[dt_DeleteSignal]

ON [dbo].[Calendar]

AFTER DELETE

AS

BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from

 -- interfering with SELECT statements.

 SET NOCOUNT ON;

 --this is a delete, we do not want old transactions with insert or update

 delete t1 from CalendarSignal t1, deleted t2 where t1.gc_id=t2.gc_id

 -- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'd', gc_id, gc_OutlookFolder, HOST_NAME() from deleted

END

 

GO

CREATE TRIGGER [dbo].[it_InsertSignal]

ON [dbo].[Calendar]

AFTER INSERT

AS

BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from

 -- interfering with SELECT statements.

 SET NOCOUNT ON;

 -- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'i', gc_id, gc_OutlookFolder, HOST_NAME() from inserted

END

GO

CREATE TRIGGER [dbo].[ut_UpdateSignal]

ON [dbo].[Calendar]

AFTER UPDATE

AS

BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from

 -- interfering with SELECT statements.

 SET NOCOUNT ON;

 -- Insert records to signal table

insert into CalendarSignal (TranType, gc_id, gc_orgFolder, gc_orgPcName) select 'u', gc_id, gc_OutlookFolder, HOST_NAME() from inserted

END

 

 

Stored procedure used in GeniusConnect to delete a record

 

CREATE PROCEDURE [dbo].[sp_DeleteCalendarItem]

 -- Add the parameters for the stored procedure here

 @gc_id uniqueidentifier,

 @FolderName varchar(50)

AS

BEGIN

 

if EXISTS (select 1 from Calendar where gc_id=@gc_id AND gc_OutlookFolder=@FolderName)

 BEGIN

  delete from Calendar where gc_id=@gc_id

 END

 ELSE

 BEGIN

  begin tran tranDeleteCalenderItem

  SET NOCOUNT ON;

  --Update the FolderName to the deleting/current folder

  update Calendar SET gc_OutlookFolder=@FolderName where gc_id=@gc_id

  --delete it, it will now use correct foldername in the CalendarSignal Table

  SET NOCOUNT OFF;

  delete from Calendar where gc_id=@gc_id

 

  if @@error <> 0

  rollback tran tranDeleteCalenderItem

  else 

  commit tran tranDeleteCalenderItem

 END

END

 

 

Stored procedure used in GeniusConnect to return the changes

 

CREATE PROCEDURE [dbo].[sp_get_changes]

 @FolderName varchar(50)

AS

BEGIN

 -- SET NOCOUNT ON added to prevent extra result sets from

 SET NOCOUNT ON;

 declare @nLastDone int

 declare @nMaxTran int

 

 -- Select last processed transaction for a folder and host name

select @nLastDone=LastTranNr from CalendarSignalDone where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME()

 

-- Select last not processed transaction (ignore transactions triggered from folder and host)

select @nMaxTran=max(TranNr) from CalendarSignal where (gc_orgFolder <> @FolderName OR gc_orgPcName <> HOST_NAME())

 

-- Insert or update Last processed Transaction nr. For Folder and host

if EXISTS (select 1 from CalendarSignalDone where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME() )

 BEGIN

update CalendarSignalDone SET LastTranNr=@nMaxTran where gc_orgFolder=@FolderName AND gc_orgPcName = HOST_NAME()

 END

 else

 BEGIN

insert into CalendarSignalDone (LastTranNr, gc_orgFolder, gc_orgPcName) VALUES(@nMaxTran, @FolderName, HOST_NAME())

 END

 

-- We want to return result, set NOCOUNT off

 SET NOCOUNT OFF;

 

-- return transactions between lastDone (not included) and nMaxTran (included), (ignore transactions triggered from folder and host)

 select TranNr, TranType, gc_id from CalendarSignal

 where  @nMaxTran IS NOT NULL AND

 (@nLastDone IS NULL or TranNr > @nLastDone)

 AND

 (TranNr <= @nMaxTran)

 AND

 (gc_orgFolder <> @FolderName OR gc_orgPcName <> HOST_NAME())

END

 

 

GeniusConnect implementation

 

Use delete stored procedure in GeniusConnect

SQL Command: EXECUTE sp_DeleteCalendarItem '@gc_id','{OUTLOOK_FOLDER_FULL}'

 

image\DetectionDeleteSP.jpg

 

 

Use detect stored procedure in GeniusConnect

SQL Command: {call sp_get_changes('{OUTLOOK_FOLDER_FULL}')}

 

image\dbdetect_getchanges.jpg