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:
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:
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 masked
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:
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:
Enables access to Exchange JDBC connections in Groovy/Velocity scripts.
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.
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.
Permits sending meeting invitations and canceling a meeting.
Adding file attachments to Exchange objects, local saving of file attachments, copying / moving of objects to other folders.
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.
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.
The value of "true" activates the detailed log messages of the JDBC driver (default is "false").
The value of "true" uses the MediaGateway ODBC driver via the Sun JDBC-ODBC bridge instead of the native JDBC driver (default is "false".)
This 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.
…where (Exchange) and (Shared) and (ArrayTable) and (one of the tables) and …(one of the three table groups)
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'
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.
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