Can I force a translation to fail if violation of a Primary Key occurs when writing to SQL Server?

Which writer format is this? Pure SQL Server or is there some ArcSDE in the mix as well?

There's a little known trick to catching almost any message in the FME log and to act on it, using the fmeobjects.FMELogFile callback method.

Try putting the following in the Python startup script:

import fmeobjects

global AN_ERROR_OCCURRED
AN_ERROR_OCCURRED = None

def LogSkimmer(severity, text):
    if text.find('Violation of PRIMARY KEY constraint') > -1:
        global AN_ERROR_OCCURRED
        AN_ERROR_OCCURRED = text

fmeobjects.FMELogFile().setCallBack(LogSkimmer)

Then just before your SQL Server Spatial writer, insert a PythonCaller with the following code:

import fmeobjects

def detect_errors(feature):
    global AN_ERROR_OCCURRED
    if AN_ERROR_OCCURRED:
        raise fmeobjects.FMEException(AN_ERROR_OCCURRED)

Let me know how that works for your case.

Thanks. I'll give that a go. You do realise having to fit another transformer in will upset my neatly aligned bookmarks! ;-)

 

 

Sounds like you should check out the new collapsible bookmarks then ;-)

Sounds like you should check out the new collapsible bookmarks then ;-)

I tried collapsing bookmarks and found that doing that removes all the vertexes on connections between bookmarks...

 

 

Use a sql executor to get all constraints (usually at begin of your script) Process them and make your data respect the constraints before trying to write it to the table(s).

Here is a oracle sql version

select search_condition,table_name

 

from user_constraints

 

where table_name =

 

and constraint_type ='C'Choose the constraint type you require. You might need to access all_constraints in stead of the user_constraints

Use a sql executor to get all constraints (usually at begin of your script) Process them and make your data respect the constraints before trying to write it to the table(s).

Here is a oracle sql version

select search_condition,table_name

 

from user_constraints

 

where table_name =

 

and constraint_type ='C'Choose the constraint type you require. You might need to access all_constraints in stead of the user_constraints

Nice idea. I have some columns which can't have null values so that would help to validate those too, but it's very unlikely the input data will violate the no nulls constraints (how pessimistic should you be when writing a Workspace?!).

 

 

Your idea has got me thinking that I could put a DuplicateFilter in for the primary key attribute and connect the Duplicate port to a Terminator. The Python code option might make for a quicker translation though....

 

 

Plus the DuplicateFilter would only pick up features being processed, not conflicts with data already in the SQL table.

 

There's a little known trick to catching almost any message in the FME log and to act on it, using the fmeobjects.FMELogFile callback method.

Try putting the following in the Python startup script:

import fmeobjects

global AN_ERROR_OCCURRED
AN_ERROR_OCCURRED = None

def LogSkimmer(severity, text):
    if text.find('Violation of PRIMARY KEY constraint') > -1:
        global AN_ERROR_OCCURRED
        AN_ERROR_OCCURRED = text

fmeobjects.FMELogFile().setCallBack(LogSkimmer)

Then just before your SQL Server Spatial writer, insert a PythonCaller with the following code:

import fmeobjects

def detect_errors(feature):
    global AN_ERROR_OCCURRED
    if AN_ERROR_OCCURRED:
        raise fmeobjects.FMEException(AN_ERROR_OCCURRED)

Let me know how that works for your case.

Has it worked, or it is just that the Python has failed?

 

 

Also, it says 1 feature was written when it clearly wasn't.

 

 

2018-09-21 14:39:41|  4.0|  0.1|WARN  |Microsoft SQL Server Spatial Writer: Failed to write a feature of type `yyyy' to the database. Provider error `(-2147217873) Violation of PRIMARY KEY constraint 'xxxx'. Cannot insert duplicate key in object 'yyyy'. The duplicate key value is (1234).'. SQL Command `DECLARE @WKB0 varbinary(max); SET @WKB0 = ?; DECLARE @SRID0 INTEGER; SET @SRID0 = ?; INSERT INTO yyyy ([field1], [field2], [etc]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE @WKB0 WHEN '' THEN NULL ELSE geometry::STGeomFromWKB(@WKB0,@SRID0) END)'

 

...

 

2018-09-21 14:39:41|  4.0|  0.0|ERROR |Microsoft SQL Server Spatial Writer: Failed to write a feature of type `yyyy' to the database. Provider error `(-2147217873) Violation of PRIMARY KEY constraint 'xxxx'. Cannot insert duplicate key in object 'yyyy'. The duplicate key value is (1234).'. SQL Command `DECLARE @WKB0 varbinary(max); SET @WKB0 = ?; DECLARE @SRID0 INTEGER; SET @SRID0 = ?; INSERT INTO yyyy ([field1], [field2], [etc]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE @WKB0 WHEN '' THEN NULL ELSE geometry::STGeomFromWKB(@WKB0,@SRID0) END)'

 

2018-09-21 14:39:41|  4.0|  0.0|ERROR |Error encountered while calling function `detect_errors'

 

2018-09-21 14:39:41|  4.0|  0.0|FATAL |PythonCaller(PythonFactory): PythonFactory failed to process feature

 

2018-09-21 14:39:41|  4.0|  0.0|ERROR |A fatal error has occurred. Check the logfile above for details

 

2018-09-21 14:39:41|  4.0|  0.0|ERROR |

 

2018-09-21 14:39:41|  4.0|  0.0|INFORM|Microsoft SQL Server Spatial Writer: Closing `zzzz' . Write operation complete

 

2018-09-21 14:39:41|  4.0|  0.0|INFORM|=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41|  4.0|  0.0|INFORM|Feature output statistics for `MSSQL_SPATIAL' writer using keyword `MSSQL_SPATIAL_2':

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |  Features Written

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |yyyy  1

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |==============================================================================

 

2018-09-21 14:39:41|  4.0|  0.0|STATS |Total Features Written 

 

Has it worked, or it is just that the Python has failed?

 

 

Also, it says 1 feature was written when it clearly wasn't.

 

 

2018-09-21 14:39:41| 4.0| 0.1|WARN |Microsoft SQL Server Spatial Writer: Failed to write a feature of type `yyyy' to the database. Provider error `(-2147217873) Violation of PRIMARY KEY constraint 'xxxx'. Cannot insert duplicate key in object 'yyyy'. The duplicate key value is (1234).'. SQL Command `DECLARE @WKB0 varbinary(max); SET @WKB0 = ?; DECLARE @SRID0 INTEGER; SET @SRID0 = ?; INSERT INTO yyyy ([field1], [field2], [etc]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE @WKB0 WHEN '' THEN NULL ELSE geometry::STGeomFromWKB(@WKB0,@SRID0) END)'

 

...

 

2018-09-21 14:39:41| 4.0| 0.0|ERROR |Microsoft SQL Server Spatial Writer: Failed to write a feature of type `yyyy' to the database. Provider error `(-2147217873) Violation of PRIMARY KEY constraint 'xxxx'. Cannot insert duplicate key in object 'yyyy'. The duplicate key value is (1234).'. SQL Command `DECLARE @WKB0 varbinary(max); SET @WKB0 = ?; DECLARE @SRID0 INTEGER; SET @SRID0 = ?; INSERT INTO yyyy ([field1], [field2], [etc]) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, CASE @WKB0 WHEN '' THEN NULL ELSE geometry::STGeomFromWKB(@WKB0,@SRID0) END)'

 

2018-09-21 14:39:41| 4.0| 0.0|ERROR |Error encountered while calling function `detect_errors'

 

2018-09-21 14:39:41| 4.0| 0.0|FATAL |PythonCaller(PythonFactory): PythonFactory failed to process feature

 

2018-09-21 14:39:41| 4.0| 0.0|ERROR |A fatal error has occurred. Check the logfile above for details

 

2018-09-21 14:39:41| 4.0| 0.0|ERROR |

 

2018-09-21 14:39:41| 4.0| 0.0|INFORM|Microsoft SQL Server Spatial Writer: Closing `zzzz' . Write operation complete

 

2018-09-21 14:39:41| 4.0| 0.0|INFORM|=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41| 4.0| 0.0|INFORM|Feature output statistics for `MSSQL_SPATIAL' writer using keyword `MSSQL_SPATIAL_2':

 

2018-09-21 14:39:41| 4.0| 0.0|STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41| 4.0| 0.0|STATS | Features Written

 

2018-09-21 14:39:41| 4.0| 0.0|STATS |=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-

 

2018-09-21 14:39:41| 4.0| 0.0|STATS |yyyy 1

 

2018-09-21 14:39:41| 4.0| 0.0|STATS |==============================================================================

 

2018-09-21 14:39:41| 4.0| 0.0|STATS |Total Features Written

 

To add: what we're seeing in the log above is that first there's a WARN about the primary key, followed by an ERROR with the exact same message. The WARN comes from the writer, the ERROR comes from the Python script which basically escalated the WARN to an ERROR. The ERROR is what makes FME terminate the translation.

Sorry - should have included that line. Yes it says...

 

A fatal error has occurred. Check the logfile above for details Program Terminating

 

Translation FAILED.

 

 

Regarding features written, it's not the overriding of FME behaviour that's the issue. If I run the translation without the Python, FME still says it's written the features, even though they all failed to write to the SQL Server table because of a Primary Key violation. I know we're talking semantics about what "written" means here, but I would argue that if it's not written, it's not written! I'm less decided about whether some features violating a Primary Key constraint but not others should constitute a successful or failed translation...

 

Indeed, it's getting close to being about semantics. The issue is that FME has "decided" that the feature was written even when it didn't, that's why the features written is wrong. I would not disagree if you would call that a bug...

 

Out of curiosity, what happens if you use a FeatureWriter? Does the offending feature exit the <Rejected> port?

I've not used FeatureWriter before so I might have missed something in configuring it, but it doesn't appear to offer a Rejected port.

 

You're right, I seem to have confused it with the SQLExecutor. Too bad, it would've been very helpful.

There's a little known trick to catching almost any message in the FME log and to act on it, using the fmeobjects.FMELogFile callback method.

Try putting the following in the Python startup script:

import fmeobjects

global AN_ERROR_OCCURRED
AN_ERROR_OCCURRED = None

def LogSkimmer(severity, text):
    if text.find('Violation of PRIMARY KEY constraint') > -1:
        global AN_ERROR_OCCURRED
        AN_ERROR_OCCURRED = text

fmeobjects.FMELogFile().setCallBack(LogSkimmer)

Then just before your SQL Server Spatial writer, insert a PythonCaller with the following code:

import fmeobjects

def detect_errors(feature):
    global AN_ERROR_OCCURRED
    if AN_ERROR_OCCURRED:
        raise fmeobjects.FMEException(AN_ERROR_OCCURRED)

Let me know how that works for your case.

Me again.  I have now replaced my Writers with FeatureWriters and have noticed this line appearing in the translation log:

 

2018-10-08 11:58:17|  18.8|  0.0|INFORM|Optional `close' method not present; not called

 

I've looked this up and read a couple of forum topics about it but I'm not sure I completely understand what's going on.  I assume it has something to do with the fact that a Writer is the end of a translation whereas the translation can continue after a FeatureWriter.  It doesn't look as though anything bad is happening, but is there something I need to add to the Python script as best practice?

 

 

BTW, this is for a successful translation.  I haven't yet tested whether Primary Key violations still terminate the translation.

 

Is it possible that you're tweaked the code above to have a class definition rather than a function definition?

 

The log message seems to indicate that you have a class method that's missing the close() method. This is nothing to worry about, it's just for information. You can also get rid of the messge by adding the following method to your class definition:

 

    def close(self):
        pass

Thanks.  I've added those lines after the existing code as shown below.  How do I know if it's a Function or a Class?  I've read the help page but it doesn't really help!  Is it something to do with "Class or Function to Process Features"?