I'm calling a child Workspace using a WorkspaceRunner. The child Workspace says translation was successful as does the WorkspaceRunner, but when I reviewed the log for the child Workspace, I noticed there were warnings like this: "Provider error `(-2147217873) Violation of PRIMARY KEY constraint 'xxxx'. Cannot insert duplicate key in object 'yyyy'". If this happens, I want the Workspace to fail/terminate. Is there a way I can do this? Would a FeatureWriter transformer rather than a normal Writer give me this functionality?
Question
Can I force a translation to fail if violation of a Primary Key occurs when writing to SQL Server?
+8- Contributor
david_r wrote:
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.
+8- Contributor
david_r wrote:
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.
tim_wood wrote:
Thanks. I'll give that a go. You do realise having to fit another transformer in will upset my neatly aligned bookmarks! ;-)
+17- Contributor
If you set the transaction to a number, larger than the number of records you are inserting, the transaction will be rolled back by any database failure.
The log file will contain the message, returned by the database.
I am 100% sure this is how it works for Oracle, and more than 99% sure it works the same for SQL Server.
Hope this helps.
+8- Contributor
david_r wrote:
Sounds like you should check out the new collapsible bookmarks then ;-)
+8- Contributor
erik_jan wrote:
If you set the transaction to a number, larger than the number of records you are inserting, the transaction will be rolled back by any database failure.
The log file will contain the message, returned by the database.
I am 100% sure this is how it works for Oracle, and more than 99% sure it works the same for SQL Server.
Hope this helps.
+15- Contributor
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
gio wrote:
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
+8- Contributor
gio wrote:
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
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.
tim_wood wrote:
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.
+8- Contributor
david_r wrote:
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.
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
tim_wood wrote:
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
Since we're basically overriding the default FME termination behavior, I'm not too surprised that the "total features written" isn't quite correct.
tim_wood wrote:
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
+8- Contributor
david_r wrote:
Looks like it worked, but what does the last line of the log say? SUCCESSFUL or FAILED? I would expect it to say that the translation FAILED.
Since we're basically overriding the default FME termination behavior, I'm not too surprised that the "total features written" isn't quite correct.
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...
tim_wood wrote:
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...
Out of curiosity, what happens if you use a FeatureWriter? Does the offending feature exit the <Rejected> port?
+8- Contributor
tim_wood wrote:
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...
tim_wood wrote:
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.
+8- Contributor
tim_wood wrote:
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...
+8- Contributor
david_r wrote:
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.
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.
tim_wood wrote:
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.
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- Contributor
david_r wrote:
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
tim_wood wrote:
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"?
Regarding the line in the job log about the optional 'close' method not being present, you can safely ignore that.
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.