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?
Best answer by markatsafe
@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
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
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.
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
