Modes of Operation (for SQL Server only)

The SpatialWare writer can have three modes of operation.

  • Insert
  • Update
  • Delete

The above three modes can be set at the three levels: at Writer level using keyword WRITER_MODE, at table level using the parameter spatialware_mode on the DEF lines, or at feature level using attribute fme_db_operation.

Writer Level

Note: Note: For more information, see the chapter Database Writer Mode.

The keyword at writer level is WRITER_MODE prefixed with the writer keyword (i.e., SPATIALWARE). Its possible values are

  • Inset – Implies insert only. Can be overridden only by table-level modes, not feature-level modes. This is the default.
  • Update – can be overridden by table and feature-level modes.
  • Delete – can be overridden by table and feature-level modes.

Example:

SPATIALWARE_WRITER_MODE INSERT

Table Level

At the table level, there will be a database-specific feature type DEF parameter called spatialware_mode.

Possible values for this attribute are:

  1. Writer Mode – The table adopts the mode set for the writer. It is the default.
  2. Insert – cannot be overridden by the feature-level modes
  3. Update – can be overridden by the feature-level modes
  4. Delete – can be overridden by the feature-level modes

Feature Level

At the feature level, there will be an FME generic attribute called fme_db_operation. Possible values for this attribute are:

  1. <no attribute> – defaults to table level mode
  2. Insert
  3. Update – ignored if the table level mode is INSERT
  4. Delete – ignored if the table level mode is INSERT

The FME generic attribute fme_db_operation overwrites the value given to the writer keyword WRITER_MODE for that feature only regardless of the value of the table mode, except for when the table level mode is INSERT.

Both Update and Delete may require additional attributes. For example:

fme_where – this can be any valid where clause (it does not contain the word where).

The value for this keyword is not meant to be parsed and split up by the writer, but passed directly to the database.

Feature Level Generic Examples

1. Insert example:

WRITER LEVEL: UPDATE
TABLE LEVEL: UPDATE, INSERT or DELETE
feature type roads
num_lanes 2
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.

2. Update example:

WRITER LEVEL: UPDATE
TABLE LEVEL: UPDATE or DELETE
feature type roads
num_lanes 2
road_id 1234
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".

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

Note: If the Writer Mode had been Insert, then all of the above examples would have attempted to perform inserts since the table and feature mode would have been ignored.

Assumptions:

  • Table level mode specification overrides writer level mode specification.
  • Feature level mode specification overrides table level mode specification when the table level mode is NOT Insert.
  • Table level mode specification defaults to Writer Mode.
  • Updates performed on rows that DO NOT EXIST are NOT turned into inserts. The user is warned and the feature is skipped.
  • Inserts performed on rows that EXIST are NOT turned into updates. The user is warned and the feature is skipped.