Just wondered if there was anything similar to the Format Attribute 'fme_db_operation' which allows control for INSERT UPDATE DELETE at a feature level.

---

Hi Mark,

 

 

I don't know about the sequenced table columns, but if you want feature-level control over INSERT/UPDATE/DELETE, your best option might be to have three writers with corresponding writer modes (but otherwise identical), then using e.g. a TestFilter at the end of your process to route each feature to the correct writer depending on the value of fme_db_operation.

 

 

David

---

Hi David,

Think the format param 'fme_db_operation' does this for you at a feature level even with a single writer if configured correctly. Your suggestion may work better in some situations and explicitly shows what's going where.

Mark

---

Hi Mark,

 

 

yes, you're right about fme_db_operation, which also makes me realize that I misunderstood your original question somewhat :-)

 

 

I have made some small tests regarding the sequence table column setting, but I cannot find a way to set it at feature level, either. The help is also suspiciously silent on the matter.

 

 

David

---

Hi Mark,

 

Just a thought..

 

What about using the begin/end sql on the writer to do the job?

 

Cheers,

 

Itay

 

 

---

or specifying all your sequenced table columns  

 >oracle_sequenced_cols columnl:seqname][;columne:seqname]]...] \

---

Itay,

 

 

Thanks for the suggestions. Columns are all named GID on multiple tables, column is defined as Primary Key so NOT NULL with one sequence per table named <table_name>_SEQ . This rules out End SQL as it will need a value. Only way I can see it working is to get the MAX(GID) for each table and set that as the lowest value and count up from there. Still messy and not worth it IMHO.

 

 

oracle_sequenced_cols I think has to be a parameter and not an attribute. Might ping off an email to Safe and see what they suggest. 

 

 

Mark

---

Mark,

 

 

does your tables already exist before you run FME? If so, could it be a possibility to set triggers on the GID columns in Oracle (short example).

 

 

This way you wouldn't have to specify anything for these values in FME.

 

 

David

---

David,

 

 

I don't 'own' the database so permanent changes may not be possible. That said I could add a trigger with Begin SQL and remove it with End SQL.

 

 

 

---

Mark,

 

 

to go off on a tangent here, if the GID is supposed to be a primary key (like ObjectID in ArcGIS), I would personally argue strongly with the DB owner to permanently implement these triggers.

 

 

Auto-incrementing the primary key should be handled on the DB side on not in the client, in my humble opinion. It is transactionally safer and has a more logical separation of concerns.

 

 

Writing the data first and then populating the GID after might technically be a viable solution if there is no "unique not null" constraint on the column (a big no-no in itself), but I would never dare to put such a solution into an enterprise production environment...

 

 

David

 

 

---

Initially I didn't explain the whole problem very clearly expecting an "FME can't do it" reply but instead getting the usual excellent suggestions from the community on how to work around the "FME can't do it".   The Tables already exist containing data. I'm updating (INSERT/UPDATE) the tables using the 'Writer Mode' Format Parameter with new and modified data. The table contains a column named GID which is defined as a Primary Key so therefore NOT NULL and must be set at insertion.

 

  My intention was to use a Fanout but for reasons explained above it doesn't work without a workaround. The sugestion of a permanent Oracle Trigger added by the DBA is probably the best solution.

 

 

In hindsight the idea of creating and removing a Trigger using BEGIN/END SQL statements probably won't work as the user may not have CREATE TRIGGER system privilege. The same applies for DROP/CREATE SEQUENCE to amend it based on a new value calculated by FME.   Thanks David and Itay for all the suggestions.    Mark  

---