MyNextHome
2010-01-20T12:25:00Z
Hello,

I don't want to implement triggers, stored procedures etc... to have full DB detection support. Is it possible to use simple SQL statements without extensive DB programming?

Thanks
Sponsor
GeniusConnect documentation search (User Manual Downloads...)
Administrator
2010-01-20T12:32:00Z
Hello,

To have 100% DB detection  you will have to implement triggers etc...

You can use "fake" SQL Statements but the detection will never cover all changes (like deletes etc..).
(Combine these statemens with scheduled "Load All" to sync all changes)

Example 1:

Sync only last (1 day) inserts/updates (YOU WILL MISS DELETES!):
SELECT myID as TranNr, 'i' as TranType, myID FROM MyTable where LastModification  > DATEADD(day, -1, GETDATE())

(myID=your primary key)

Example 2:
Using stored procedure storing last sync date/time:

Sample table for storing last sync date/time:
CREATE TABLE MyLastSyncTable(LastSync datetime)


CREATE PROCEDURE spMyGetChanges
AS
BEGIN

SET NOCOUNT ON 

declare @nLastSync datetime

select @nLastSync=MAX(LastSync) from MyLastSyncTable

if ( @nLastSync IS NULL)
BEGIN

//remember last sync date
INSERT INTO MyLastSyncTable VALUES(GETDATE())

//Force refresh of all rows (Load All)
SET NOCOUNT OFF 
//Force full refresh (Load All)
SELECT 1 as TranNr, 'r' as TranType, 1 as myID 

END
else
BEGIN

UPDATE MyLastSyncTable SET LastSync=GETDATE() 
where LastSync = @nLastSync

SET NOCOUNT OFF 

SELECT myID as TranNr, 'i' as TranType, myID FROM MyTable where LastModification  > @nLastSync

END
END

Please note:
This is only an example, you will have to modify the code based on your datamodel and database used!

If you are syncing multiple users, you must extend the example with user or folder identification:

CREATE TABLE MyLastSyncTable(LastSync datetime, UserOrFolder varcharXX) etc....
Administrator
2010-02-12T02:52:00Z
Example 3:
(Also here: !NO delete detection!)

No Stored procedure, just using SQL Script in GeniusConnect, using Dyn.Param for Outlook folder.

Table Example:
CREATE TABLE LastSyncTable(Folder varchar(255), LastSync datetime)

Script in GeniusConnect:

BEGIN

SET NOCOUNT ON 

declare @nLastSync datetime

select @nLastSync=MAX(LastSync) from LastSyncTable where Folder =  '{OUTLOOK_FOLDER_FULL}'


if ( @nLastSync IS NULL)
BEGIN

INSERT INTO LastSyncTable VALUES( '{OUTLOOK_FOLDER_FULL}', GETDATE())

SET NOCOUNT OFF 
SELECT 1 as TranNr, 'r' as TranType, 1 as MyPrimaryKey


END
else
BEGIN

UPDATE LastSyncTable SET LastSync=GETDATE() 
where Folder = '{OUTLOOK_FOLDER_FULL}'

SET NOCOUNT OFF 

SELECT MyPrimaryKey as TranNr, 'i' as TranType, MyPrimaryKey FROM MyTableOrView where ModificationTime > @nLastSync

END

END
wong, andy
2012-08-01T17:40:00Z
I tried with the following code. Even with a record delete "physically deleted" from the table. I have to hit load item, load all to see the contacts deleted from outlook. Any reason why?

BEGIN
SET NOCOUNT ON 
declare @nLastSync datetime
select @nLastSync=MAX(LastSync) from LastSyncTable where Folder = '{OUTLOOK_FOLDER_FULL}'
if ( @nLastSync IS NULL)
BEGIN
INSERT INTO LastSyncTable VALUES( '{OUTLOOK_FOLDER_FULL}', GETDATE())
SET NOCOUNT OFF 
SELECT 1 as TranNr, 'r' as TranType, 1 as MyPrimaryKey
END
else
BEGIN
UPDATE LastSyncTable SET LastSync=GETDATE() 
where Folder = '{OUTLOOK_FOLDER_FULL}'
SET NOCOUNT OFF 
SELECT OCS_ID as TranNr, 'u' as TranType, OCS_ID FROM Contacts where gc_LastModificationTime > @nLastSync AND ocs_owner ='{EXCHANGE_FOLDER_OWNER}'  AND OCS_DELETE_ON IS NULL 
END
END
Administrator
2012-08-03T12:57:00Z
-To return physical delete you will have to track the deletes in a trigger.

-To return soft deletes to GeniusConnect, investigate this statement:

SELECT OCS_ID as TranNr, 'u' as TranType, OCS_ID FROM Contacts where gc_LastModificationTime > @nLastSync AND ocs_owner ='{EXCHANGE_FOLDER_OWNER}' AND OCS_DELETE_ON IS NULL 

TranType must be 'd' (=delete) for records where OCS_DELETE_ON is NOT null. You will have to remove OCS_DELETE_ON from your where clause, and implement case statment. something like this:

SELECT xxx 
   TranType=CASE 
      WHEN OCS_DELETE_ON IS NULL THEN 'u' 
      ELSE 'd' 
   END