0

I am uploading spatial data in PostgreSQL DB using FME Form. I am using FeatureWriter transformer to do that. When objects get uploaded in the DB, they are assigned by DataID attribute generated by DB. How I can I retrieve this DataID value for each object for further error logging?

I have connected SQLExecutor after FeatureWriter. But it returns all list of DataIDs for each object. E.g. if I upload 12 objects, SQLExecutor return 144 DataIDs that are 12 times duplicated. I don't know how to link particular DataID to the object itself 1 to 1. Most likely I have wrong SQL query:

 select "Id" from "schema"."table"

In general, I need to implement functionality similar to this description, but implemented in FME:

-- Insert into the student table and return the generated student_id

INSERT INTO student (student_name, student_grade)
VALUES ('John Doe', 'A')
RETURNING student_id INTO student_id_value; -- Store the generated student_id
 
-- Insert into the student_error table using the retrieved student_id

INSERT INTO student_error (student_id, error_message)
VALUES (student_id_value, 'No errors');

But for now, I don't understand how to do that.

1
  • Depending on your database setup you can retrieve the nextID by using SQL SELECT nextval('schema."data_id"'); using sql executor transformer.
    – Mapperz
    Commented May 16 at 21:13

2 Answers 2

1

I think there is no out of the box option do this in FME, so the SQLExecutor downstream of the FeatureWriter is probably the way to go.

One way to do this is to activate the outputport on the FeatureWriter and use that as input for the SQLExecutor. You will need a unique value to ask for the ID to make sure you get the correct ID returned. You will need to write this ID in the FeatureWriter to get this to work. One way to generate unique ID's is the UUIDGenerator.

The query should be something like

select "Id" as "Id"  from "schema"."table" where GUID = <guid>

It is probably smart to add an attribute index on the GUID column, this will improve performance of the SQLExecutor.

0

This is not a unique FME issue, if you use an SQLExecutor for your INSERT statement (in SQL) you can return the id that way! See https://stackoverflow.com/questions/19167349/postgresql-insert-from-select-returning-id

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.