Solved

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

M
Rank
Badge +2

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.

icon

Best answer by nielsgerrits 10 March 2023, 08:02

View original

4 replies

M
Badge +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
Rank
Userlevel 6
Badge +33

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.

M
Rank
Badge +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.

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 :)

M
Rank
Badge +2

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 !

Reply


Community Stats

28,729
Posts
109,527
Replies
36,565
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.