+26Not sure if this is a bug or I'm just doing it wrong.
I've got a FeatureWriter that does an Update on a SQLite Table, and then follows this by doing an Insert on the same SQLite Table.
It doesn't work. The Update operation runs, but the Insert doesn't. If I split the FeatureWriter into 2 separate FeatureWriters, one for Update and one for Insert, then it works.
Is this just a limitation, a bug, or I've set this up wrong?
Best answer by markatsafe
@bwn I've attached a revised workspace (2019.2) with the SQLite writer replaced with a SQLite FeatureWriter and it seems to work OK.
The SQLite writer has not yet been harmonized with other database writers in FME and so the fme_db_operation is not available on the Feature Type Write Mode. But just set the fme_db_operation in an AttributeCreator. Also, make sure Writer Mode is set to Inherit from Writer on the feature type parameters
Example: sqliteupdates.fmwt
@bwn I couldn't reproduce the issue. In the attached workspace INSERT,UPDATE & DELETE are successful. FME doesn't support "upsert" so you need to explicitly say if it's an INSERT or UPDATE - using fme_db_operation, or separate writers
Example workspace (2019.2): sqliteupdates.fmwt
+26@bwn I couldn't reproduce the issue. In the attached workspace INSERT,UPDATE & DELETE are successful. FME doesn't support "upsert" so you need to explicitly say if it's an INSERT or UPDATE - using fme_db_operation, or separate writers
Example workspace (2019.2): sqliteupdates.fmwt
Thanks @markatsafe. The example workspace appears to use SQLite Writers rather than a SQLite FeatureWriter, and similarly appears to break up the INSERT and UPDATE into two separate write operations.
I had similarly looked at a workaround of using an fme_db_operation Attribute for a combined Insert/Update, but fme_db_operation is not a supported function of the SQLite FeatureWriter, only Insert, Update, Delete or Inherit From Writer are Table Mode writing options. Instead the FeatureWriter attempts this by using the same Table Name twice, once with an Update Table Mode, and the next with an Insert Table Mode. It appears the FeatureWriter will only do 1 Table Operation only, and skip any others that use the same Table Name.
Attached example WorkSpace + SQLite DB (zipped) that successfully Updates with 1 x Feature, but fails to Insert a Separate 1 x feature (The SQLite database stays at 10 records in length before/after running the workspace), even though the Log reports both successfully written.
@bwn I've attached a revised workspace (2019.2) with the SQLite writer replaced with a SQLite FeatureWriter and it seems to work OK.
The SQLite writer has not yet been harmonized with other database writers in FME and so the fme_db_operation is not available on the Feature Type Write Mode. But just set the fme_db_operation in an AttributeCreator. Also, make sure Writer Mode is set to Inherit from Writer on the feature type parameters
Example: sqliteupdates.fmwt
+26@bwn I've attached a revised workspace (2019.2) with the SQLite writer replaced with a SQLite FeatureWriter and it seems to work OK.
The SQLite writer has not yet been harmonized with other database writers in FME and so the fme_db_operation is not available on the Feature Type Write Mode. But just set the fme_db_operation in an AttributeCreator. Also, make sure Writer Mode is set to Inherit from Writer on the feature type parameters
Example: sqliteupdates.fmwt
Thanks @markatsafe , I hadn't considered fme_db_operation might be respected in this way, so is useful tip for these situations.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
