Connector for Microsoft Exchange - Technical features

The Connector for Microsoft Exchange was developed to make the integration of mailboxes as easy as possible for you. However, the handling of data on the Exchange server should take a number of technical special cases into account, which will be noted in the following. As a rule, dealing with these special features will be taken over by Intrexx. However, if you need to break new ground when creating applications, or dig deeper in developing with Groovy or Velocity into the data, it is important to recognize these special cases.

1. Primary keys of the Connector tables

Primary keys serve to exactly identify a specific data record. If you wish to select a specific data record from a list of data records, you will require the primary key. The following list will give you the required overview to do so:

TablePrimary key
AppointmentID
FolderID
MessageID
ContactID
TaskID

One special property of the Exchange store in comparison to relational databases is that, by changing a data record, in certain circumstances the ID value can also change. For this reason, ID values are not designed to link data in Exchange with data from other applications. In this case, the "PermanentURL" field exists in all Exchange tables. This ensures a constant unique value to identify a data record.

2. Non-sortable fields

The data in the mailboxes will be prepared for Intrexx like data in databases, in order to offer the simplest possible form of integration. However, the Exchange Server does not act exactly like a database, and not all available fields can be used as sorting fields.

In the following, you will find a list of these corresponding fields:

Non-sortable fieldsAlternative
Bcc
ETag
FKID
FKItemID
FKUserMailboxE-Mail
HasSubfolders
HRef
HtmlDescription
ItemLink
ParentName
PermanentUrl
Read
ReplicationUID
ResourceTag
UnicodeSubjectNormalizedSubject
VisibleCount
UnreadCount
Value

3. Masking fields

Certain field names of the objects on the Exchange server (emails, appointments, notes, tasks) have the name of protected terms of the database query language SQL. These names must correspondingly be masked.

Fields to be maskedMasking
to[to]

4. User-defined fields

In addition to the standard fields of the Exchange store, the Connector offers the ability to create user-defined fields. The following additional fields will be automatically created when the connector is installed:

Field nameData type
IntrexxIDInteger
IntrexxFKIDInteger
IntrexxGUIDString
IntrexxApplicationString
IntrexxLastUpdatedDatetime
IntrexxReserved1String
IntrexxReserved2String
IntrexxReserved3String
IntrexxReserved4Integer

5. Objects in Groovy context

Certain desired functions may not be represented using the interface in Intrexx, such as the assignment of an email from a mailbox as a file attachment in a customer data record in your CRM. The Exchange server itself offers no functionality for this. In order to be able to represent such requirements, there are a number of methods that will support you in this:

ObjectDescription
ExchangeConnectionUtilEnables access to Exchange JDBC connections in Groovy/Velocity scripts.
ExchangeMessageUtilOffers methods for access to and administration of Exchange messages, such as saving a message in MSG or EML format locally, copying/moving messages to folders, sending/forwarding messages, or administrating attachments.
ExchangE-MailboxUtilOffers information about the Exchange mailbox of a user, such as name of the default folder, list of all orders, list of public users, or administration of away messages.
ExchangeAppointmentUtilPermits sending meeting invitations and canceling a meeting.
ExchangeItemUtilAdding file attachments to Exchange objects, local saving of file attachments, copying / moving of objects to other folders.
ExchangeUserMailboxInfoOffers information about the Exchange mailbox of the currently logged in Intrexx user. The object is reserved for high-performance access in the Intrexx session.

6. Special filters

For some adjustments, the special properties of the Exchange server and the possibilities of the Connector for Microsoft Exchange in tables make the use of special filters necessary, such as when folder names should be shown language-dependent (Inbox, Sent items, etc.), or when filtering the email address of the current user. Even if you just want to show elements in the Inbox, for example, you must filter the email elements correspondingly.

7. Intrexx system properties

The following Intrexx System Properties make additional technical configuration of the connector possible. The properties are to be entered to the file "portal.cfg" in the portal directory /internal/cfg.

System propertyDescription
de.uplanet.lucy.exchange.exchangeConnectionStringLogThe value of "true" activates the detailed log messages of the JDBC driver (default is "false").
de.uplanet.lucy.exchange.useJdbcOdbcDriverThe value of "true" uses the MediaGateway ODBC driver via the Sun JDBC-ODBC bridge instead of the native JDBC driver (default is "false".)
de.uplanet.lucy.exchange.useOWAVirtualDirectoryThis property can be set to "true" in the event that connection problems occur with Exchange Server 2007 (default is "false").

8. Unsupported Where statements

The MediaGateway tables are virtually divided into three groups. These groups are relevant to the Where statement in SQL statements. It is possible to write any kind of "Where" statement as long as it is a valid SQL statement and all the columns within the "Where" statement comes either directly from a single group or the groups are coupled by operator "AND" with other groups. Each group needs to be surrounded by parentheses, if the particular group has got more than just one sub-statement.

9. MediaGateway table groups


Group typeTables
ArrayMessageCategories, AppointmentCategories, TaskCompanies, ContactChildrensNames, MessageVotingOption
SharedExchangeSharedUsers
ExchangeAll others

10. The Where statement schema

…where (Exchange) and (Shared) and (ArrayTable) and (one of the tables) and …(one of the three table groups)
Examples:
select * from Message inner join MessageCategories on where subject = 'my subject' and value='item of category'
There are two table groups in this request. The part subject='mysubject' comes from Exchange group. The part item='item of category' comes from Array group. It is possible to use brackets in a query as long as the groups stay logically separated by the AND operator.
select * from Message inner join MessageCategories on where (subject = 'my subject' or  body='my body') and value='item of category'. 
A more complicated Where statement:
(subject='my subject' or ( body='my body' or (subject= 'my second option' or subject like 'my third option'))) and value='item of category'.
This is a simple rule all the queries should be created accordingly.

11. What is not supported in Where statements

…where (Exchange or Shared or ArrayTable) – or any combination of OR and AND operators.
For example it is neither possible to execute:
where (subject='my subject') and value='item of category' or body='my body'
nor:
where (subject='my subject') and (value='item of category' or body='my body')
because a column from one group is exceeding to another one. If the rule is broken and the inconsistency is discovered the software is throwing exception: "Inconsistent branch has been found". In this case an alternative solution is needed that follows the same logic, but where the syntax corresponds to the rule.

Example:
Where subject IS NULL AND value ='my first value' OR subject IS NULL AND value ='my second value' 
can be transformed to
Where subject is null and (value ='my first value' or value ='my second value')
After this simple transformation, two logically separated groups with a clear distinction are created, coupled together with an operator "AND".