Skip to main content
  • EN

I am parsing an XML and writing the header-content into a table. When inserted to database the inserted row (always 1 row) will be assigned a primary key. The same XML contains a number elements that are written to a separate table in the same database. The two tables are connected by former database-assigned key.

How do I add the database-assigned key to the features before writing to the second table?

 

Can I handle it in FME?

I tried using a SQLexecutor, but performing a call to get the key seems like a bad idea.

Do I have to go to T-SQL and write to both tables in a single SQLexecutor?

What I usually do is create the key in the SQLExecutor (before the split to the two tables), using a query to the database Sequence object.

Assuming Oracle that would look like:

Select <sequence>.nextval as ID from Dual

with <sequence> being the name of the used database sequence.

This would only work if the trigger to generate the key would execute only for an empty key field.

Hope this helps.

@mathiku

You'll have to call the database to get the next key. If you can make the key a sequence then you only need one call and then you can use the Counter to increment the id. If it's a GUID or UUID then you'll have to make the call just before the AttributeCreator, once for each XML record

You're also likely to run into feature ordering issue. If you do you can vote on this Idea

Your workspace contains 'blocker' transformers (Aggregator) so these will hold back the child features and they will most likely get written last, instead of first. You options are:

  • use two separate writers, one for the parent & the other for the child features. You can order writers in the navigator so make the child writer the first one.
  • You can try FeatureHolder on the parent stream, sometimes this will work.
  • Use FeatureWriter, and include the attributes you need for the parent record on the Summary feature

@mathiku

You'll have to call the database to get the next key. If you can make the key a sequence then you only need one call and then you can use the Counter to increment the id. If it's a GUID or UUID then you'll have to make the call just before the AttributeCreator, once for each XML record

You're also likely to run into feature ordering issue. If you do you can vote on this Idea

Your workspace contains 'blocker' transformers (Aggregator) so these will hold back the child features and they will most likely get written last, instead of first. You options are:

  • use two separate writers, one for the parent & the other for the child features. You can order writers in the navigator so make the child writer the first one.
  • You can try FeatureHolder on the parent stream, sometimes this will work.
  • Use FeatureWriter, and include the attributes you need for the parent record on the Summary feature

Thanks for the reply.

I decided to go for 2 SQLexecutors where the parent executor returns the assigned key.

 

Reply


Community Stats

30,193
Posts
114,604
Replies
37,855
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.