Skip to main content
Solved

How can I get the ID of new row in a Non Spatial SQL Database


mlupien
Contributor
Forum|alt.badge.img+7

Hi,

I am writing to a Non spacial SQL Database, using a FeatureWriter.

When INSERTing new rows in the table, a UniqueID is created by SQL database.

Is there a way to retrieve that UniqueID after INSERT completed sucessfully ?

I would need the UniqueID for the next step of my Workbench.

I do a SQL Executor to select the last inserted row, it will work, but, if multiple rows are inserted simultaneously, there is a chance that I won't get the right UniqueID.

 

Any Idea ?

Thank you.

Best answer by nielsgerrits

The way I do this is to create and write an attribute with an id (UUIDGenerator) and then use a FeatureReader with a where clause on this attribute to read back the feature. FeatureWriter can be configured to have an outputport for each FeatureType.

View original
Did this help you find an answer to your question?

4 replies

Forum|alt.badge.img+2

@mlupien​ If possible, I think I might turn the problem around and use something like SQLCreator to get the current identity:

SELECT IDENT_CURRENT ('Person.Address'AS Current_Identity;

then start reading with FeatureReader and use FME Counter to add your own unique ID starting at the Current_Identity value. If you have to query back for each record to get the ID  you're going see a big performance degradation.


nielsgerrits
VIP
Forum|alt.badge.img+54
  • Best Answer
  • March 10, 2023

The way I do this is to create and write an attribute with an id (UUIDGenerator) and then use a FeatureReader with a where clause on this attribute to read back the feature. FeatureWriter can be configured to have an outputport for each FeatureType.


mlupien
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • March 13, 2023
markatsafe wrote:

@mlupien​ If possible, I think I might turn the problem around and use something like SQLCreator to get the current identity:

SELECT IDENT_CURRENT ('Person.Address'AS Current_Identity;

then start reading with FeatureReader and use FME Counter to add your own unique ID starting at the Current_Identity value. If you have to query back for each record to get the ID  you're going see a big performance degradation.

Unfortunately, in this case, it won't be a solution because we only process 1 feature each time. I'll go with nielsgerrits suggestion.

Thank you anyways for your reply :)


mlupien
Contributor
Forum|alt.badge.img+7
  • Author
  • Contributor
  • March 13, 2023
nielsgerrits wrote:

The way I do this is to create and write an attribute with an id (UUIDGenerator) and then use a FeatureReader with a where clause on this attribute to read back the feature. FeatureWriter can be configured to have an outputport for each FeatureType.

Thank you ! I really like this idea ! It will do the job !


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings