Identity Column (Magic xpa 3.x)

Columns that have the IDENTITY property contain system-generated values that uniquely identify each row within a table. This is used to generate sequential numbers (for example, employee identification numbers). When inserting values into a table with an identity column, MSSQL automatically generates the next identifier based on the last used identity value (incremented by adding rows) and the increment value specified during column creation.

By default, data cannot be inserted directly into an identity column.

IDENTITY column is usually defined to be used as position for a table in Magic xpa; therefore it has to be selected in the Magic xpa task. This means that the INSERT command that will be issued by the task will contain this column. Since MSSQL does not allow updating this column’s value manually, an error message should appear. The solution is to identify to Magic xpa that a column is an IDENTITY column, and to disregard this column in Magic xpa’s INSERT commands.

This was implemented in the Magic xpa MSSQL gateway, in the following way:

1. A column will be identified as an IDENTITY column by adding the keyword “IDENTITY” to the Type Property in the SQL Tab of the Column Properties, following the SQLType of the column. This will either be done by the Magic xpa programmer (if the table is created from Magic xpa) or by the gateway when the programmer issues Get Definition. This will indicate to the gateway to handle this column as IDENTITY.

Example:

An INT column that has an IDENTITY property should be defined as:

INT IDENTITY
in the Type field in the Column Properties/SQL section.

2. An IDENTITY column will be skipped when the gateway issues an INSERT command.

3. In Get Definition, the default for IDENTITY columns will be “Non Modifiable”.