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