Skip to main content
  • EN

I have two table that I am trying to keep in sync. I want a target table to match a source table. But I am trying to avoid deleting all rows from the target and reloading it. The source table is in our CIS (customer information system) Oracle database and the target is in our GIS Esri Oracle database. I am using a CRC calculator to find rows that are identical. Then I have a writer to delete rows from the target that have been updated in the source and to delete rows that no longer exist in the source. Then I have a second writer to insert rows that exist in the source but do not exist in the target or have been updated in the source. The delete writer is based on a single key column.

Okay, so it is important that the delete writer commits before the the insert writer. I have tested it and the delete writer does appears to commit first. The tables are synced properly and the FME logs show the delete writer committed first. So, my question is this: Can i count on the the delete writers always committing first in FME?

(I did just wing this work flow so I am open to suggestions for other improvements, e.g. maybe to two writer approach is misguided)  

 

 

Okay so it looks like the order of the commits is driven by the writer order in the navigator? Is that right? 

Correct, see a write up on that here:

https://support.safe.com/hc/en-us/articles/25407755593229-Write-Feature-Types-in-a-Specific-Order

There are some tools which you can use to help control the flow of features as well. 

I think what have have is the way to do it - but if you’re looking at another option or if you want to do any kind of validation then you and use FeatureWriters and a FeatureFlowValue.

If you first use a FeatureWriter with your deletes then you can then preform validation actions on the table in the same workflow. After you’ve completed your validations then you can put the other features into the Insert FeatureWriter. In this case you can use a FeatureFlowValue to block the insert features from going any further in the workspace. Once the validatoin is complete you can open the valve and let though the insert features. 

If validation is important then I can recommend this workflow.

 

Agree with Matt. I have several flows where the order of committing is very important (Foreign Keys!), and I exclusively use FeatureWriters there. You have much more control over the order in which features are written to the database. Even if your approach works, there is always a chance that it won’t at some point (maybe because the database is doing something else, or somebody else is querying it, or whatever). With featureWriters you are certain of the order. The only drawback of FeatureWriters is that they do not comply with the Redirect to Inspector directive, which makes testing a bit of a pain. But using a readonly-connection for your writers can solve that, although that’s not ideal.

Thanks for the feedback everyone. I have used feature writers the past but did not know about the feature flow valve. My in app FME help is supper thin for it. But I am guessing that the pattern would look like this:
 

 

Yes, it would. And just in case you need things from the original features (I do, because I need the ID’s to keep my Foreign Key relation in tact), you can also use the original features, by adding an output port for them.

And if you really want to make sure that stuff is written, you can actually use a Tester on the summary. It really allows for pretty strict control of what gets written to the database and when, and for alerts when things go wrong (Terminator!).

Reply


Community Stats

30,234
Posts
114,762
Replies
37,901
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.