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.
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.
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)
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).
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... ;)
A creator set to create at end triggering an sql executor might be an option
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.
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 :-)
You may want to vote for this idea then :-)
Done 
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.
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.