Identity & Auto-increment Columns

When the Database Writer inserts into a table whose key is an identity or auto-increment column, the database generates the key and it is not in the dataset. IMan can retrieve the generated value and write it back into the dataset, where the rest of the integration can use it.

This makes a parent-and-child insert possible. Write the order header and receive the key the database allocated it. The order lines can then carry that key as their foreign key. Without the write-back, the integration has no way of knowing the key.

Setting it up

In the field grid of the transaction being inserted, take the field that is to receive the generated value and set its Column to SYS.AUTOIDENTITY. The drop-down lists it under its own Auto-Insert group, below the table's real columns.

The field is normally an empty one: it has no incoming value and exists to be filled in. After the insert it holds the generated key for that record.

The field is not inserted

The writer leaves a field mapped to SYS.AUTOIDENTITY out of the generated INSERT. It builds the column list from the fields mapped to real columns. The field receives a value and never supplies one, because the database generates that column itself.

You can map only one field per transaction this way. If you map more than one, IMan uses the first.

Supported databases

IMan retrieves the value in a different way for each database, using what that server offers. So it works only where IMan has a dialect for the driver:

Database	How the value is retrieved
Microsoft SQL Server	SELECT SCOPE_IDENTITY() appended to the insert
Access	SELECT @@Identity
MySQL	SELECT last_insert_id()
Oracle	RETURNING <column> INTO :imanOutputParameter
Postgres	RETURNING <column>

With any other driver the insert still runs as an ordinary insert, and IMan writes nothing back.

Naming the field

The field's own name matters on Oracle and Postgres, and does not elsewhere.

IMan passes the name of the field mapped to SYS.AUTOIDENTITY to the dialect. The two RETURNING dialects put it straight into the SQL as a column name. The other three ask the server for the last identity it generated and never mention a column, so on those the field can have any name.

Database	Field name
Access	Anything
Microsoft SQL Server	Anything
MySQL	Anything
Oracle	Must match the column name
Postgres	Must match the column name

A field called NewOrderKey works on SQL Server and fails on Postgres. On Postgres it must be called OrderKey, the name of the column being returned. Name the field after the column on every database, and the integration will work on any of them.

Using the value

IMan writes the value back onto the record as soon as its insert completes, so anything downstream of that point can read it. That includes the child transactions of the same record, which the writer writes after their parent.

A generated value of zero is not written back

IMan writes back only a non-zero result. If a table's identity seed produces 0, IMan does not return it, and the field stays as it was.

A worked shape

The clearest use is a two-table insert:

1. The Orders transaction maps to a table whose key is an identity column, and one of its fields, for example OrderKey, is mapped to SYS.AUTOIDENTITY.
2. The writer inserts the order header, the database allocates the key, and IMan writes it into OrderKey on that record.
3. The OrderLines transaction, written next, maps its own OrderKey field to the real foreign key column and picks up the value from its parent.

Without step 2 there is nothing to put in the lines' foreign key, because the number did not exist until the writer inserted the header.