Sync with a fictitious SQL Server database

The fictitious SQL Server back-end database contains contact information (companies, contacts, record status, etc). It is used as part of a secure web-site, where users can view/modify their information. The managing organization already uses InfoQube for CRM type information and wanted to link to the web-site information as its agents were answering calls/emails.

The following configuration file can be used:

<ConnectionString>PROVIDER=SQLOLEDB.1;PASSWORD=SomePassword;PERSIST SECURITY INFO=TRUE;USER ID=SomeUser;INITIAL CATALOG=YourDataBase;DATA SOURCE=SQLServerIP;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096
</ConnectionString>

<Password></Password>
<FieldMap>
[company_id] IDSQL
[steward_no2] NoEnterprise
[company_name] Item
[address1] Address
[address2] Address2
[address3] Address3
[city] City
[province] Province
[postal_code] PostalCode
[country] Country
[website] WWW
[phone_number] Tel
[fax_number] Fax
[first_name] FirstName
[last_name] LastName
[title] Titre
[telephone_no] TelContact
[telephone_ext] Ext
[last_modified] DateUpdate
[email_address] email
IMPORT IDStatusSQL
1 Contacts
</FieldMap>
<Source>
SELECT dbo.COMPANIES.*, dbo.COMPANIES_S.steward_no AS steward_no2, dbo.CONTACTS.first_name, dbo.CONTACTS.last_name,
dbo.CONTACTS.title, dbo.CONTACTS.telephone_no, dbo.CONTACTS.telephone_ext, dbo.CONTACTS.email_address
FROM dbo.CONTACTS_S RIGHT OUTER JOIN
dbo.CONTACTS ON dbo.CONTACTS_S.contact_id = dbo.CONTACTS.contact_id RIGHT OUTER JOIN
dbo.COMPANIES INNER JOIN
dbo.COMPANIES_S ON dbo.COMPANIES.company_id = dbo.COMPANIES_S.company_id ON
dbo.CONTACTS.company_id = dbo.COMPANIES.company_id
WHERE (dbo.CONTACTS_S.primary_contact = 1) AND (dbo.COMPANIES.last_modified > CONVERT(DATETIME, '[LASTUPDATE]', 102))
</source>
<RecordIDField>IDSQL</RecordIDField>
<RecordStatus>IDStatusSQL ADD UPDATE</RecordStatus>
<RecordDateField>DateUpdate</RecordDateField>
<LastUpdate>2007-03-15 07:00:00</LastUpdate>

Notes:

  • The Password key is optional and if present, is the encrypted password (inquire about the encryption utility)
  • Field mapping format is: [BackEndField] TABKey InfoQubeField or Value TABKey InfoQubeField. In the latter case, InfoQube field will be assigned Value. In the above example, Contact field is checked for all imported data and the string "IMPORT" is placed in the field IDStatusSQL
  • Source: standard SQL select statement with 1 exception. The string [LASTUPDATE] will be replaced by the LastUpdate key. This way, only changed data is read. If this is not used, all recordset data will be read on each update, which may take a long time.
  • RecordIDField is the InfoQube field to hold the back-end database record key
  • RecordStatus format is: InfoQubeField TABKey AddString TABKey UpdateString. This step is optional but will fill the InfoQubeField with AddString if adding, and UpdateString on record update. In the above example, ADD and UPDATE will be placed in the field. This way managers can process these separately
  • RecordDateField is the InfoQube field which contains the record last change date (used in conjunction with the LastUpdate)
  • LastUpdate is the date/time of the last update. Used in conjunction with the SQL Select statement to read new/changed data only