Solved

Dynamic workflow sync 2 database

  • 26 June 2018
  • 9 replies
  • 12 views

Badge +3

Hello There

we are looking to sync MSSQL DB with PostGIS DB , we used the dynamic workflow in this article

https://knowledge.safe.com/articles/1050/dynamic-workflow-tutorial-introduction.html?smartspace=dynamic-workspaces

https://knowledge.safe.com/questions/55645/dynamic-output-to-sql-server-no-table-qualifierdat.html?%CF%87ldToView=71254#answer-71254

we want to do is each time we run this workspace it will update the existing table and create a new table if needed, we configure the writer as shown, the problem how we should know match column parameter in order to execute the update,

icon

Best answer by boubcher 26 June 2018, 09:51

View original

9 replies

Userlevel 2
Badge +17

Hi @boubcher, In the fixed "Update" mode as shown in your screenshot, no records can be inserted into the destination table if the table doesn't exist before run, since no records to be updated don't exist in that case.

If you need to perform these two operations with a single writer feature type,

  1. CREATE a new table and INSERT all records into the new table if the table doesn't exist
  2. UPDATE records that match some condition if the table does exist

you want to control DB opration through "fme_db_operation" attribute and set the update condition (WHERE Clause) through an attribute e.g. "fme_where".

The "fme_db_operation" attribute should store "UPDATE" if the table exists, "INSERT" otherwise. So, you have to determine if the destination table exists or not at some point in the workspace at run-time and set one of "UPDATE" or "INSERT" to the attribute.

The "fme_where" attribute should store an expression that specifies a record to be updated (e.g. id = '@Value(id)'). It's easy if the expression is identical through all the tables, but you have to make efforts to define expressions dynamically if different expressions are required for each table. The solution depends on actual conditions and requirements.

See here to learn more about Feature Operations: Feature Operations

Badge +3

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

Userlevel 2
Badge +17

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID )  if not route them to the DB writer with the create option.

is this correct 

That is one of ways, but I think it would be better to use the SQLCreateor or the SQLExecutor with this SQL statement (for example) to detect existing table names from the destination PostGIS database, not to read all existing data records wastefully.

 

select tablename
from pg_tables where schemaname = 'Tansiq'

 

Userlevel 2
Badge +17

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

Note I assume that you need to perform only these operations, as I mentioned before.

 

  1. CREATE a new table and INSERT all records into the new table if the table doesn't exist
  2. UPDATE records that match some condition if the table does exist
If you need to perform other operations you haven't mentioned yet (e.g. INSERT new records into EXISTING table if there are no matched existing records), the solution should be reconsidered.

 

 

Badge +3

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

yes , you are right we need also to add the record to an existing table if it doesn't exist

 

do we need to follow the same process as the previous operation.

 

 

 

 

Userlevel 2
Badge +17

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

Ignore my answers and learn this tutorial.

 

Tutorial: Updating Databases

 

Badge +3

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

@takashi

 

I did creat the WS , but I am not sure about the update operation, for the existing record we need to out a where close there , but it should be like dynamic

 

any Help

 

Thanks

 

sync-2-database.fmw
Userlevel 2
Badge +17

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

I told you ignore my answers if "insert records into existing table" could also be required. Please learn first the tutorial.

 

Badge +3

@takashi

if I understand you well, we need to have 2 dynamic readers one for the source DB and second for the destination DB then compare them using fme_feature_type attribute by using a tester after that if they are equal route them to the DB writer with the update option ( where clause ID = ID ) if not route them to the DB writer with the create option.

is this correct

@takashi

 

 

thanks for the reference

 

Sorry I get your message after my last comment

 

Thanks again

 

Reply