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.

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:

Table	Primary key
Appointment	ID
Folder	ID
Message	ID
Contact	ID
Task	ID

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.

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 fields	Replaced features
Bcc
ETag
FKID
FKItemID
FKUserMailboxE-Mail
HasSubfolders
HRef
HtmlDescription
ItemLink
ParentName
PermanentUrl
Read
ReplicationUID
ResourceTag
ResourceTag	NormalizedSubject
VisibleCount
UnreadCount
Value

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 masked	Masking
to	[to]

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 name	Data type
IntrexxID	Integer
IntrexxFKID	Integer
IntrexxGUID	String
IntrexxApplication	String
IntrexxLastUpdated	Datetime
IntrexxReserved1	String
IntrexxReserved2	String
IntrexxReserved3	String
IntrexxReserved4	Integer

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:

Object	Description
ExchangeConnectionUtil	Enables access to Exchange JDBC connections in Groovy/Velocity scripts.
ExchangeMessageUtil	Offers 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-MailboxUtil	Offers 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.
ExchangeAppointmentUtil	Permits sending meeting invitations and canceling a meeting.
ExchangeItemUtil	Adding file attachments to Exchange objects, local saving of file attachments, copying / moving of objects to other folders.
ExchangeUserMailboxInfo	Offers information about the Exchange mailbox of the currently logged in Intrexx user. The object is reserved for high-performance access in the Intrexx session. The object is reserved for high-performance access in the Intrexx session.

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.

Intrexx system properties

The following Intrexx system properties enable an additional technical configuration of the Connector. The properties are to be entered in the portal.cfg file in the portal directory internal/cfg.

System Property	Description
de.uplanet.lucy.exchange.exchangeConnectionStringLog	The value of "true" activates the detailed log messages of the JDBC driver (default is "false").
de.uplanet.lucy.exchange.useJdbcOdbcDriver	The 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.useOWAVirtualDirectory	This property can be set to "true" in the event that connection problems occur with Exchange Server 2007 (default is "false").

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.

MediaGateway table groups

Group type	Tables
Array	MessageCategories, AppointmentCategories, TaskCompanies, ContactChildrensNames, MessageVotingOption
Share	ExchangeSharedUsers
Exchange	All other controls

The Where statement schema

…where (Exchange) and (Shared) and (ArrayTable) and (eine der Tabellen) and …(eine der drei Tabellen-Gruppen)

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.

What is not supported in Where statements

…where (Exchange or Shared or ArrayTable) – oder jede Kombination von OR and AND Operatoren.

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".