Many writers that do not support the Feature Operation specification share a Writer Mode specification, which determines whether incoming features will be inserted, updated, or deleted from the destination tables. Some writers deviate from the specification.
The writer mode can be specified at three unique levels:
- Writer
- Table
- Feature
Writer Level
At the writer level, the keyword is <Writer>_MODE, prefixed by the writer keyword (for example, SDE30). Possible values are:
- INSERT – Implies insert only; can be overridden only by table-level modes, not feature-level modes.
- UPDATE – Update table, but can be overridden by table- and feature-level modes.
- DELETE – Delete from table, but can be overridden by table- and feature-level modes.
Table Level
At the table level, there is a format specific feature type DEF line parameter called _MODE prefixed by the writer keyword (for example, SDE30_MODE). Possible values for this attribute are:
0. INHERIT_FROM_WRITER – Inherits the writer-level writer mode.
1. INSERT
2. UPDATE
3. DELETE
Feature Level
At the feature level, the writer mode can be set by an attribute named fme_db_operation. Possible values for this attribute are:
0. <no attribute> – Defaults to table level mode
1. INSERT
2. UPDATE
3. DELETE
fme_db_operation overrides the writer and table level writer modes, except for when the table level mode is INSERT, or if the table level mode is INHERIT_FROM_WRITER and writer level mode is INSERT.
fme_db_operation is used in conjunction with update/delete key columns, or with the fme_where attribute for update and delete operations at the feature level. If a writer supports both fme_where and update/delete key columns, fme_where overrides update/delete key columns.
Insert example:
WRITER LEVEL: UPDATE
TABLE LEVEL: UPDATE, INSERT or DELETE
feature type roads
num_lanes 5
surface_type gravel
age 106
location canada
condition poor
name Highway 1
road_id 1234
fme_geometry fme_no_geom
fme_db_operation INSERT
This will insert a row into a table named "roads". This will append to an existing table. Columns not specified will receive their default values, if there are default values.
Update example:
WRITER LEVEL: UPDATE
TABLE LEVEL: UPDATE or DELETE
feature type roads
condition good
fme_db_operation UPDATE
fme_where road_id = 1234
This will update the row in the table "roads" where the road_id = 1234. The column "condition" will have its value changed from "poor" to "good".
Delete example:
WRITER LEVEL: UPDATE
TABLE LEVEL: UPDATE or DELETE
feature type roads
fme_db_operation DELETE
fme_where road_id = 1234
This will delete the row in the table "roads" where the road_id = 1234.
Notes:
- Updates performed on rows that DO NOT EXIST are NOT turned into inserts. The user is warned and the feature is rejected.
- Inserts performed on rows that EXIST are NOT turned into updates. FME will still attempt to perform the insert: if it is not prevented by a unique index, it will insert a duplicate row; if it is prevented by a unique index, the feature will be rejected, or translation will fail, depending on the writer.
- Updates are not limited to one row per feature. It is possible to update the entire table with one feature.
-
Geometry can also be updated using update mode. The geometry on the update feature will replace the geometry in all of the matched rows, with one exception. If a feature has no geometry, the destination geometry column will either be replaced with a null value, or not be updated, depending on the writer.