Skip to main content
  • EN

Hello,

I am authoring a workspace, that uploading data in PostgreSQL database.

And I have difficulties to implement one function.

There is a validation that checks if value is not empty or null. If object has missing unique S_ID,  it is still gets uploaded in the “Data” table. But also, I add a record in the “Errors” table, that feature has missing S_ID.

When features get uploaded in the “Data” table they are automatically assigned another unique key “ID” this is just a sequential number in “Data” table. 

 

The task for me is to add “ID” from “Data”  table when I create a record with error in “Error” table.   Because it will be the only unique attribute by which feature can be identified in case it is missing S_ID. 

 

The difficulties for me is that I am logging errors earlier than ID attribute is assigned to the uploaded features. I thought to use VariableSetter and Variable Retriever that should help in this case. But I don't know how to extract “ID” attribute anyway. I am using FeatureWriter transformer for final data upload in the DB. And I tried to add FeatureReader after it, but it doesn't see new “Id” attribute that is generated by DB. 

I hope I managed to explain. Will appreciate any thoughts. Thank you!


 

have a look at the SQLExecuter. You should be able to add this after your initial write to then extract erroneous data in the DB along with the attributes to populate your errors table

have a look at the SQLExecuter. You should be able to add this after your initial write to then extract erroneous data in the DB along with the attributes to populate your errors table

Thank you! I have connected SQL Executor after the Writer and it indeed extracts “ID” field, but for each record it gives all set of ID’s. Struggle to understand how to set SQL query properly, in order ID was extracted specifically for the objects 1 to 1 that were written to the DB. It is my current query:
select "Id" from "schema"."table"
As a result, if I have 12 objects written in the table and DB generated 12 IDs for them, when I run SQLExecutor, I am getting list of 144 records that are repeating.

 

SQLExecutor will perform your query once for every feature that enters the SQLExecutor.

So when 12 features enter the SQLExecutor, and your query returns 12 records, you will get 12 times 12 = 144 records (12 records, all repeated 12 times).

Solution: change the query so it returns only the corresponding record for each feature that enters the SQLExecutor.

SQLExecutor will perform your query once for every feature that enters the SQLExecutor.

So when 12 features enter the SQLExecutor, and your query returns 12 records, you will get 12 times 12 = 144 records (12 records, all repeated 12 times).

Solution: change the query so it returns only the corresponding record for each feature that enters the SQLExecutor.

Thank you for for your answer. Yes, I understand this what happens. I don’t understand how to modify the query so SQLexecutor would provide 1 ID per feature. 

Perhaps something like 

Select ID from TABLE where ID = ID_from_Feature

 

Or maybe something like
Select distinct ID from TABLE 

Perhaps something like 

Select ID from TABLE where ID = ID_from_Feature

 

The problem is that I don’t have ID_from_Feature. The main reason why I need to get assigned by the database ID is to have unique identifier for written objects.

After you have written the features to the database table, surely there must be some way to identify which record corresponds to which feature?

After you have written the features to the database table, surely there must be some way to identify which record corresponds to which feature?

Yes, you are right. We came up to this with dev team that there is a need to add additional field in the DB, otherwise it is hardly possible. Thank you

Reply


Community Stats

30,108
Posts
114,291
Replies
37,743
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.