Question

Suggestions for try/except blocks around SQL transformers...?

  • 17 December 2019
  • 2 replies
  • 3 views
A
Badge

When I first started working with FME a few years ago, I basically only used the pattern:

Reader = > Transformer = > Writer.

Most of my work was around geospatial data. As I've dug in and had to decipher piles of legacy FMW's that my predecessors have built, I have learned about the power of SQL Creators and Executors, and non spatial SQL readers. What has been tripping me up with these is that I have a number of workspaces in FME Server that appear to complete successfully but when I review the logs, I realize that a SQL connection or SQL query failed, returning no results, yet the workspace completed. So I get no alert that the process actually bombed.

What would be a good practice of wrapping these SQL connectors in a try/except block to catch any instances when the SQL call returns an error or zero feature because of an error or bad connection?

Would love to have your 2 cents on this, so I don't have to reinvent the wheel. Thanks!

2 replies

B
Badge +3

I suspect this is going to be highly situational and depend on your SQL and DB environment.

There are things you can do in the SQL itself to return an error record such as TRY...CATCH in SQL Server.

On the FME End, you can test the outputs of Transformers using things like Tester, TestFilter and Sampler etc. that are effectively "If Then....Else" type branching points.

If any one of those "Test" type transformers find something that shouldn't be there.......or the inverse, doesn't find something that SHOULD be there, then this can be sent to a special Transformer called "Terminator", which is kind of an Inspector that pushes the offending feature(s)/message(s) out for review and halts the Workspace.

A
Badge

Hey thanks. I understand that I could harden my SQL code for the event that something goes wrong. I was hoping to test for bad output in FME so that I can leverage the notifications/subscriptions.

But I had never heard of the "Terminator" - so thanks for throwing that out there, I will take a look.

Reply


Community Stats

28,724
Posts
109,518
Replies
36,564
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.