Solved

Writing to SQL using database key

mathiku
Rank
Badge +6

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?

icon

Best answer by markatsafe 2 July 2020, 23:13

View original

3 replies

erik_jan
Rank
Userlevel 2
Badge +16

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.

M
Badge +2

@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
Rank
Badge +6

@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

28,707
Posts
109,479
Replies
36,552
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.