The connector for Microsoft Exchange Server was designed to make it as easy as possible for you to integrate mailboxes. Nevertheless, working with data from the Exchange Server requires attention to a number of technical considerations, which are addressed individually below. As a rule, Intrexx handles these specific requirements. However, if you take a non-standard approach when creating applications or access data more deeply during development using Groovy or Velocity, it is important to be aware of the specific characteristics.
Primary keys of the connector tables
Primary keys are used to uniquely identify a record. If you want to select exactly one record from a list of records, you'll need the primary key. The following list provides you with the necessary overview:
|
Table |
Primary Key |
|---|---|
|
Appointment |
ID |
|
Brochure |
ID |
|
Message |
ID |
|
Contact |
ID |
|
Task |
ID |
One distinctive feature of the Exchange Store compared to relational databases is that changing a record may, under certain circumstances, cause the ID value to change. Therefore, ID is not suitable for linking data in Exchange with data from other applications. For this purpose, all Exchange tables include a PermanentURL field. This ensures a consistently unique value for identifying a data record.
Fields that cannot be sorted
Data from mailboxes is formatted for Intrexx in the same way as data from databases, in order to provide the simplest possible form of integration. However, Exchange Server is not a database, and not all available fields can be used as sort fields. Below is a list of the relevant fields:
|
Fields that cannot be sorted |
Alternative |
|---|---|
|
Bcc |
|
|
ETag |
|
|
FKID |
|
|
FKItemID |
|
|
FKUserMailbox Email |
|
|
HasSubfolders |
|
|
HRef |
|
|
HtmlDescription |
|
|
ItemLink |
|
|
ParentName |
|
|
Permanent URL |
|
|
Read |
|
|
ReplicationUID |
|
|
ResourceTag |
|
|
UnicodeSubject |
NormalizedSubject |
|
VisibleCount |
|
|
UnreadCount |
|
|
Value |
Hiding Fields
Some field names for Exchange Server objects (emails, appointments, notes, tasks) are named after reserved keywords in the SQL database query language. These names must be redacted accordingly.
|
Field to be masked |
Masking |
|---|---|
|
to |
[to] |
Custom Fields
In addition to the standard fields in the Exchange Store, the connector allows you to create custom fields. The following additional fields are automatically created when the connector is installed:
|
Field Name |
Data Type |
|---|---|
|
IntrexxID |
Integer |
|
IntrexxFKID |
Integer |
|
IntrexxGUID |
String |
|
IntrexxApplication |
String |
|
IntrexxLastUpdated |
Date and Time |
|
IntrexxReserved1 |
String |
|
IntrexxReserved2 |
String |
|
IntrexxReserved3 |
String |
|
IntrexxReserved4 |
Integer |
Objects in the Groovy Context
Some desired features may not be possible to implement using the Intrexx interface, such as attaching an email from a mailbox as a file to a customer record in your CRM. Exchange Server itself does not provide this functionality. To help you address these requirements, there are a number of methods available to assist you:
|
Object |
Description |
|---|---|
|
ExchangeConnectionUtil |
Enables access to Exchange JDBC connections in Groovy/Velocity scripts. |
|
ExchangeMessageUtil |
Provides methods for accessing and managing Exchange messages, such as saving a message locally in MSG or EML format, copying or moving messages to folders, sending or forwarding messages, or managing attachments. |
|
ExchangE-MailboxUtil |
Provides information about a user's Exchange mailbox, such as the names of the default folders, a list of all folders, a list of authorized users, and managing the out-of-office message. |
|
ExchangeAppointmentUtil |
Allows you to send meeting invitations and cancel meetings. |
|
ExchangeItemUtil |
Adding file attachments to Exchange objects, saving file attachments locally, copying/moving objects to other folders |
|
ExchangeUserMailboxInfo |
Provides information about the Exchange mailbox of the currently logged-in Intrexx user. The object is retained in the Intrexx session to ensure high-performance access. |
Special Filters
For some customizations, the specific features of the Exchange server and the capabilities of the connector in tables require the use of special filters, such as for the language-dependent display of folder names (Inbox, Sent Items, etc.) or for filtering the current user's email address. Even if, for example, you only want to display items from your Inbox, you'll need to filter the email items accordingly.
Intrexx System Properties
The following Intrexx system properties allow for additional technical configuration of the connector. The properties must be entered in the portal.cfg file in the portal directory internal/cfg.
|
System Property |
Description |
|---|---|
|
en.uplanet.lucy.exchange.exchangeConnectionStringLog |
The value "true" enables detailed logging by the JDBC driver (default: "false"). |
|
de.uplanet.lucy.exchange.useJdbcOdbcDriver |
The value "true" uses the MediaGateway ODBC driver via the Sun JDBC-ODBC bridge instead of the native JDBC driver (default: "false"). |
|
de.uplanet.lucy.exchange.useOWAVirtualDirectory |
This property can be set to true if connection problems occur with Exchange Server 2007 (default: false). |
Unsupported WHERE Statements
The MediaGateway tables are virtually divided into three groups. These groups are relevant to the WHERE clause in SQL statements. You can write any type of WHERE clause, as long as it is a valid SQL statement and all columns within the WHERE clause either come directly from a single group or are connected to other groups using the AND operator. Each group must be enclosed in parentheses if that group has more than one nested statement.
MediaGateway Table Groups
|
Group Type |
Tables |
|---|---|
|
Array |
MessageCategories, AppointmentCategories, TaskCompanies, ContactChildrensNames, MessageVotingOption |
|
Shared |
ExchangeSharedUsers |
|
Exchange |
All others |
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 query. The "subject='mysubject'" part comes from the Exchange group. The "item='item of category'" part comes from the array group. It is possible to use parentheses in the query as long as the groups remain logically separated by an AND operator.
select * from Message inner join MessageCategories on where (subject = 'my subject' or body='my body') and value='item of category' , Here's a more complicated statement:
(subject='my subject' or ( body='my body' or (subject= 'my second option' or subject like 'my third option'))) and value='item of category'.All queries should be structured according to this simple rule.
What Is Not Supported in WHERE Statements
…where (Exchange or Shared or ArrayTable) – oder jede Kombination von OR and AND Operatoren.For example, neither the query
where (subject='my subject') and value='item of category' or body='my body'or the query
where (subject='my subject') and (value='item of category' or body='my body')be performed because one column from one group overlaps another. If this rule is violated and the inconsistency is detected, the software triggers the "Inconsistent branch has been found" exception. In this case, an alternative solution is needed that follows the same logic but uses syntax that complies with the rule.
Example:
Where subject IS NULL AND value ='my first value' OR subject IS NULL AND value ='my second value' can be converted to
Where subject is null and (value ='my first value' or value ='my second value')This simple transformation produces two logically separate, clearly distinguishable groups that are linked by the AND operator.