Primary and Mandatory keys

A primary key column(s) must get a value from a SQL statement.

PRIMARY KEY constraints identify the column or set of columns whose values uniquely identify a row in a table. No two rows in a table can have the same primary key value.

Mandatory keys, which are not linked to an Outlook field and are not getting value automatically in the database (DEFAULTS etc.), must get a value from a SQL statement.

GeniusConnect will execute these SQL Statements and the result values will be used to insert a new record into the database table.

The SQL Statement can be any valid SQL Statement returning 1 single value.

The result value must be:

Unique for primary key columns or columns with unique index
Compatible with data type of the table column

For every Outlook Folder at least one primary key MUST be defined!

SQL Statement for a new Primary key will be executed only for inserting of a new record to the table.

SQL Statement for a Mandatory key will be executed (and the result value will be used) also for record updates.

Example:

GeniusConnect steps during saving a new Outlook Item into a database table.

Table: MyContactTable

Primary Key: ID

SQL Statement to generate a new ID: select max(ID)+1 from MyContactTable

Steps:

Execute select max(ID)+1 from MyContactTable
Get results, result=100
Execute: insert into MyContactTable (ID, All Other columns…) VALUES(100, All Other column values)

The columns in the list view are:

Database field name
Type (primary or mandatory)
SQL statement (must be entered by the user)
Identity (also Auto Increment, AutoNumber etc.)
Yes=database automatically generates values during insert. (disabled when specifying mandatory keys for Attachments mapping )
No=Value must be provided with the Insert statement

GeniusConnect will try to detect the primary/mandatory keys using functionality of ODBC driver.

Not every ODBC driver supports this feature. If it is not possible to detect primary/mandatory fields, the fields must be added manually by clicking the Add button.

In the first column the field can be selected from a drop down list.

In the second column the key type must be specified.

In the third column the SQL statement must be specified.

If you are using Identity columns, set the value to “Yes” and specify a SQL statement which returns the last-inserted identity value.

Example:

NOTE:
If you are using MS Access auto numbers, you do not have to set the Identity to YES!
MS Access allows to provide values for auto numbers with the Insert statement.

By clicking on the row of the database field at the ‘SQL statement’ column the user can enter the SQL statement.

You can use the “Test” button to test your SQL Statement.

If your SQL statement results in an error (or is empty),

GeniusConnect will not be able to insert new records into the database table during the real synchronization.

Example SQL Statements:

Note: SQL syntax is database dependent; these examples will not work for every database system!

Primary key

For integer fields:

Database Independent:

select max(YOUR_COLUMN_NAME) + 1 from YOUR_TABLE_NAME

MS Access:
select Iif(max(YOUR_COLUMN_NAME) is null, 0,max(YOUR_COLUMN_NAME) + 1)
from YOUR_TABLE_NAME

SQL Server:
select ISNULL(max(YOUR_COLUMN_NAME)+1,1) from YOUR_TABLE_NAME

Last identity Value
SQL Server and MySQL:

select @@IDENTITY

For SQL Server GUID:

select newid()

Mandatory fields

For char fields:

select ‘always this text’

For integer fields

select 6

For current date

select GETDATE()

For advanced users:
Dynamic parameters defined in Filter rows can be used and will be replaced with runtime values.