Question

Workspace Spanning Database Transactions

  • 23 October 2019
  • 1 reply
  • 18 views
P

At the most basic level, I am looking for a way to

1) execute a stored procedure

2) execute a multi-step process that populates multiple database tables

3) executes another stored procedure

on MS SQL Server, as a single transaction. Setting "a very large transaction interval on your database writer" is not really a good answer; it is not a guarantee. See this answer

Technically, step 2 would happen only if the result returned from the stored procedure in step 1 indicated it was safe to. But one step a time; I need to figure this part out before that becomes relevant.

Is there a way to have a database transaction span a workspace, or some subset of it?

See this related question and my comment there.

Note: Getting FME to call a stored procedure was actually pretty straightforward, even for this FME n00b; it is the environment in which it needs to happen (a single transaction) that is proving bothersome.

1 reply

M
Badge +2

@pakicetus You might be able to use fme_db_transaction. This obscure database functionality is documented for each database format that supports it. i.e. SQL Server. To use fme_db_operation:

  • Set Bulk Insert = No (Bulk Insert = Yes ignores triggers and constraints)
  • Set the Features per Transaction = 0 (this enables the fme_db_operation mode)
  • Set the value of fme_db_operation to one of the documented values: COMMIT_BEFORE, COMMIT_AFTER, ROLLBACK_AFTER or IGNORE

The trick is: how to order the features. This depends on the data your reading. In the attached example I used Sorter and the Set Connection Runtime Order to ensure that the data is grouped together and so the parent / child tables get written in the right order . An alternative is to use SQLExecutor with a WHERE, the Results for each 'where' and Initiator are returned together. FeatureHolder sometimes works.

Example workspace (2019): databasetransactions2019.fmwt

Reply


Community Stats

28,670
Posts
109,366
Replies
36,531
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.