Skip to main content
Question

Workspace Spanning Database Transactions

  • October 23, 2019
  • 1 reply
  • 89 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.

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

1 reply

M
Forum|alt.badge.img+2
  • markatsafe
  • 1891 replies
  • October 24, 2019

@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

Community Stats

32,079
Posts
121,810
Replies
39,714
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
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.