The SQLite3 writer allows the user to specify a writer mode, which determines what database command should be issued for each feature received. Valid writer modes are INSERT, UPDATE and DELETE.
Writer Modes
In INSERT mode, the attribute values of each received feature are written as a new database record.
In UPDATE mode, the attribute values of each received feature are used to update existing records in the database. The records which are updated are determined via the sqlite3_update_key_columns DEF line parameter, or via the fme_where attribute on the feature.
In DELETE mode, existing database records are deleted according to the information specified in the received feature. Records are selected for deletion using the same technique as records are selected for updating in UPDATE mode.
Writer Mode Constraints
In UPDATE and DELETE mode, the fme_where attribute always takes precedence over the sqlite3_update_key_columns DEF line parameter. If both the fme_where attribute and the sqlite3_update_key_columns DEF line parameter are not present, then UPDATE or DELETE mode will generate an error.
When the fme_where attribute is present, it is used verbatim as the WHERE clause on the generated UPDATE or DELETE command. For example, if fme_where were set to ‘id<5’, then all database records with field ID less than 5 will be affected by the command.
When the fme_where attribute is not present, the writer looks for the sqlite3_update_key_columns DEF line parameter and uses it to determine which records should be affected by the command. Please refer to DEF for more information about the sqlite3_update_key_columns DEF line parameter.
Writer Mode Selection
The writer mode can be specified at three unique levels. It may be specified on the writer level, on the feature type or on individual features.
At the writer level, the writer mode is specified by the WRITER_MODE keyword. This keyword can be superseded by the feature type writer mode specification. Note: For more information on this directive, see the chapter Database Writer Mode.
At the feature type level, the writer mode is specified by the sqlite3_writer_mode DEF line parameter. This parameters supersedes the WRITER_MODE keyword. Unless this parameter is set to INSERT, it may be superseded on individual features by the fme_db_operation attribute. Please refer to the DEF line documentation for more information about this parameter.
At the feature level, the writer mode is specified by the fme_db_operation attribute. Unless the parameter at the feature type level is set to INSERT, the writer mode specified by this attribute always supersedes all other values. Accepted values for the fme_db_operation attribute are INSERT, UPDATE or DELETE.
fme_db_transaction
As each feature is processed by the writer, it is checked for an attribute called fme_db_transaction.
The value of this attribute specifies whether the writer should commit or rollback the current transaction. The value of the attribute can be one of:
COMMIT_BEFORECOMMIT_AFTERROLLBACK_AFTERIGNORE
If the fme_db_transaction attribute is not set in any features, the entire write operation occurs in a single transaction.
Note: To use this capability, the Transaction Interval (for ArcSDE, this is called Features to Write Per Transaction) must be set to VARIABLE.