Skip to main content
Solved

Run SQL whenever the workspace stops


Hi,

I have a big workspace that can potentially stop at different steps because the workspace is going through multiple readers and some of them can have no features.

Is there a way to execute a SQL script whenever the workspace is stopping ?

The goal of this script is to check some integrity rules in the database (Postgres).

Maybe the python shutdown script could do that but maybe there is a simpler way.

Thank you for any help.

Best answer by david_r

The most fool-proof way of doing such checks are by wrapping the existing workspace in an outer "master" workspace, using either the WorkspaceRunner or the FMEServerJobSubmitter.

That allows you to kick off any post-translation checks regardless of how the "child" workspace ended (no data to read, failures, etc).

View original
Did this help you find an answer to your question?

10 replies

david_r
Celebrity
  • April 5, 2019

Some writers have a setting for "SQL to run after write", which may be an option, here from the PostgreSQL writer:

My preferred option, however, is to avoid using regular writers, but to use FeatureWriters. It's then very easy to tack on one or several SQLExecutors to the FeatureWriter's "Summary" port. That way the SQLExecutor won't be called before all the write operations have terminated. This also works for any format in FME, not just those with the "SQL to run after write" setting.


  • Author
  • April 5, 2019
david_r wrote:

Some writers have a setting for "SQL to run after write", which may be an option, here from the PostgreSQL writer:

My preferred option, however, is to avoid using regular writers, but to use FeatureWriters. It's then very easy to tack on one or several SQLExecutors to the FeatureWriter's "Summary" port. That way the SQLExecutor won't be called before all the write operations have terminated. This also works for any format in FME, not just those with the "SQL to run after write" setting.

Thanks David, I should have mention that this solution is not appropriate in my case because the workspace can stop after any FeatureReader if there are no features for one of them (which can happen).

So the process won't even reach the FeatureWriter and therefore it won't execute the SQL script.

(I already use only FeatureReaders and FeatureWriters)


david_r
Celebrity
  • Best Answer
  • April 5, 2019

The most fool-proof way of doing such checks are by wrapping the existing workspace in an outer "master" workspace, using either the WorkspaceRunner or the FMEServerJobSubmitter.

That allows you to kick off any post-translation checks regardless of how the "child" workspace ended (no data to read, failures, etc).


  • Author
  • April 8, 2019
david_r wrote:

The most fool-proof way of doing such checks are by wrapping the existing workspace in an outer "master" workspace, using either the WorkspaceRunner or the FMEServerJobSubmitter.

That allows you to kick off any post-translation checks regardless of how the "child" workspace ended (no data to read, failures, etc).

Thanks David, I think you're right, it's probably the only and best solution, even if I would prefer an equivalent of "shutdown python script" (like "shutdown SQL script") to keep control over one single workspace, which is far simplier to handle.

Maybe in a future release... ;)

 


ebygomm
Influencer
Forum|alt.badge.img+33
  • Influencer
  • April 8, 2019

A creator set to create at end triggering an sql executor might be an option


  • Author
  • April 8, 2019
ebygomm wrote:

A creator set to create at end triggering an sql executor might be an option

Thanks for the tip @egomm but that doesn't work as I expect.

I tried a Creator with the option "Create at end" but it launches when all Readers have been read which is not the good timing, because the database still continues to be updated at this step.


david_r
Celebrity
  • April 9, 2019
christophe wrote:

Thanks for the tip @egomm but that doesn't work as I expect.

I tried a Creator with the option "Create at end" but it launches when all Readers have been read which is not the good timing, because the database still continues to be updated at this step.

You may want to vote for this idea then :-)

https://knowledge.safe.com/content/idea/20549/transformer-that-works-after-completion-of-transla.html

 


  • Author
  • April 9, 2019
david_r wrote:

Done ;)


gio
Contributor
Forum|alt.badge.img+15
  • Contributor
  • April 9, 2019
christophe wrote:

Thanks David, I think you're right, it's probably the only and best solution, even if I would prefer an equivalent of "shutdown python script" (like "shutdown SQL script") to keep control over one single workspace, which is far simplier to handle.

Maybe in a future release... ;)

 

You can of course use the oracle wrapper for python.

And then have a python shut-down script execute your SQL.

 


david_r
Celebrity
  • April 9, 2019
gio wrote:

You can of course use the oracle wrapper for python.

And then have a python shut-down script execute your SQL.

 

Excellent point. The cx_Oracle module works well in shutdown scripts, I've used it that way myself.


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings