werkilla
2009-04-24T11:33:00Z
Hi!

Info:
OutlookConnect.dll 4,0,0,8
DBContact.dll 1,1,1,3
OutlookInterface.dll 1,1,3,8
mapi32.dll 1.0.2536.0
OUTLOOK.EXE 11.0.8169
Microsoft Exchange Server 2003 (SP2)
License: MultiUser Buisiness
Oracle DB

The problem is that the contacts are updated every time,
even if the info has not changed neither in Outlook nor in the DB.

I turned Debug on, and I get this message:

Field [Body] differs from DB value.

The thing is, that the field is not different at all!
I though that Outlook might be reformatting (adding/omiting spaces,..)
but when I compare the text in Outlook and in the DB I don´t see any difference.

Any help would be appreciated!
Administrator
2009-04-24T11:44:00Z
Hello,

you can try to use the "Compare Only LastModificationTime" feature.
(Assign Table->Options->Advanced)

werkilla
2009-04-24T12:01:00Z
That doesn't solve it, because for an also unknown reason,
when I use this option, and I change data in my DB it always displays that the LastModificationTime equals the DB time (even when it does not).

It works well from Outlook to the DB, but not the opposite way.
werkilla
2009-04-24T12:19:00Z
I just realized that if I for example insert '---' into the DB
it will be saved in italics and green as if it were a comment,
which I really don't want to..
Datatype is varchar2 (4000Bytes)
I guess that's why the fields are always different?

Oh, and thank you!
Administrator
2009-04-24T12:37:00Z
"when I use this option, and I change data in my DB it always displays that the LastModificationTime equals the DB time"
if an other app. updates your DB data, you must update also your LastModificationTime.

This can be done for example in a trigger.
Example:
MS SQL Server Trigger example to auto update LastModificationTime.
This trigger is part of the MS SQL server example creation script.

CREATE TRIGGER tuAutoUpdateContacts ON [dbo].[CONTACTS] 
FOR UPDATE not for replication
as 
begin
if @@rowcount = 0
return

if not update(gc_LastModificationTime)
begin
update c
set gc_LastModificationTime = CURRENT_TIMESTAMP
from CONTACTS c inner join inserted i on (c.OCS_ID=i.OCS_ID)

end
end


"it will be saved in italics and green as if it were a comment"
If you don't want the formatting info (font etc...), map you DB Body Column to Outlook Body (plain text only)instead of HTMLBody
werkilla
2009-04-24T13:13:00Z
"if an other app. updates your DB data, you must update also your LastModificationTime."
The LastModificationTime IS updated when modified.
I also tried setting it manually (an hour later) but it always displays that the OL time equals the DB.

"If you don't want the formating info (font etc...), map you DB Body Column to Outlook Body (plain text only)instead of HTMLBody"
It is mapped to Outlook Body (plain text). It's in the DB where it is saved with format, and I think this is what is causing the problem, isn't it?

And thank you for the quick reply! 🙂
Administrator
2009-04-24T13:21:00Z
Check the mapping/config, there is no way GeniusConnect will display "OL time equals the DB" if there is 1 hour difference!!
werkilla
2009-04-24T14:22:00Z
I think that's correct, and as I said it works well Ol -> DB.
But not the opposite way.

Last_Modified DB Field: 24.04.2009 14:13:31
OL LastModificationTime: Fr 24.04.2009 13:54

Action: Load All
Result: OL time = DB time Ignoring Item

I have compare LMT tolerance set to 0
the Last_modified is updated in a trigger with SYSDATE
DB detection is not enabled (yet).
************************************************************
# GeniusConnect settings file
# Comments are allowed on a single line only!

[Fields]
# Format: outlookfield=column,conversion
Body=T792_BODY,
Business Phone 2=BUSINESS_PHONE2,
Business Address City=BUSINESS_ADDRESS_CITY,
Business Address Country=BUSINESS_ADDRESS_COUNTRY,
Business Address Postalcode=BUSINESS_ADDRESS_PLZ,
Business Address Street=BUSINESS_ADDRESS_STREET,
Business Fax=BUSINESS_FAX,
Business Phone=BUSINESS_PHONE1,
Company (Name)=COMPANY_NAME,
Email=EMAIL,
Email 2=EMAIL2,
First Name=FIRST_NAME,
Home Fax=FAX_2,
Home Phone=HOME_PHONE,
Job Title=JOB_TITLE,
Last Name=LAST_NAME,
Mobile Phone=MOBILE_PHONE,
Title=TITLE,
User field 1=GROUP_1,
User field 2=OUTLOOK_FLAG,
LastModificationTime=LAST_MODIFIED,

[PrimaryKeys]
# Format: column=newstatement
ID=select S1.nextval from dual

[MandatoryKeys]
# Format: column=newstatement

[WhereFilter]
# Format: WHERE=statement
WHERE=

[OptionsWhereFilter]
# Format: WHEREOPTIONS=X,X 0 or 1 IgnoreForUpdate,IgnoreForDelete
WHEREOPTIONS=0,0

[FolderOptions]
# Format: OPTIONS=X,X,X. 0 or 1 values slash for advanced options in format:NOPRIVATE,NOOUTLOOKUPDATE,NODBUPDATE,,,DELOUTLOOK_ITEM,DELDB_RECORD,BLOCKDELETECONFIRM,DBTIMEOUT,BLOCKDB_DELETECONFIRM\USE_LASTMODIFICATION_TIME,CONFLICT_RESOLUTION
OPTIONS=0,0,0,,,1,0,0,30,0,0,0,0,0,0,1\1,3,1

# Format: InsertSQLCmd=statement,UpdateSQLCmd=statement,DeleteSQLCmd=statement
[InsertSQLCmd]
InsertSQLCmd=

[UpdateSQLCmd]
UpdateSQLCmd=UPDATE CONTACTS
SET CONTACTS.BUSINESS_PHONE1 = '@BUSINESS_PHONE1',
CONTACTS.BUSINESS_PHONE2 = '@BUSINESS_PHONE2',
CONTACTS.MOBILE_PHONE = '@MOBILE_PHONE',
CONTACTS.HOME_PHONE = '@HOME_PHONE',
CONTACTS.BUSINESS_FAX = '@BUSINESS_FAX',
CONTACTS.FAX_2 = '@FAX_2',
CONTACTS.OUTLOOK_FLAG = 1
WHERE CONTACTS.ID = '@ID';

[DeleteSQLCmd]
DeleteSQLCmd=

# Format: DB Detection params
[DBDetectEnable]
DBDetectEnable=0

[DBDetectExecSeconds]
DBDetectExecSeconds=30

[DBDetectSQL]
DBDetectSQL=select id as TranNr,
'u' as ChangeType, id
from contacts
where last_modified > (SYSDATE-1);

[DBDetectUpdateSQL]
DBDetectUpdateSQL=

# Format: Primary Key storage
[PrimKeyStoreType]
PrimKeyStoreType=0

[PrimKeyDirectory]
PrimKeyDirectory=

# Format: Disable Toolbar buttons
[DisableToolbarButtons]
DisableToolbarButtons=0,0,0,0
Administrator
2009-04-24T17:56:00Z
Your [UpdateSQLCmd] is not using the Exchange/Outlook LastModificationTime ?

Anyway, we can check your config remote, you can send an email to techsupport at geniusconnect.com, and we will send you a GoTo Meeting invitation.
werkilla
2009-05-05T14:00:00Z
I don't think I'm allowed to do that.

Anyway I solved the problem.
It was a CHR(13) || CHR(10) problem.

I guess Outlook was adding the missing character.

And the Option "Compare LastModificationTime Only" doesn't completely satisfy what I'm looking for.

Now, with the same info,
how are the DB change detection settings so far?
Administrator
2009-05-05T14:06:00Z
You need to implement a stored procedure to return changes to GeniusConnect.

There is an example for MS SQL Server with example tables, you can use this example and change it for your data model.

See held file topic(F1 or question mark in toolbar): Database change detection



werkilla
2009-05-05T14:36:00Z
Fast answer!
Thank you very much! 🙂
Similar Topics