Hi,
sure, that is no problem. The error you're getting is because you're executing two statements (the DELETE and the COMMIT) without separating them. Rewrite like this:
---
FME_SQL_DELIMITER ;
delete from CMSS.STAGE_ASSET_INFO
where CMSS.STAGE_ASSET_INFO.ASSET_ID='$(assetId)';
commit;
---
You need to specify the FME_SQL_DELIMITER so that FME knows where to split your statements for individual execution. More info here (
http://fmepedia.safe.com/articles/Error_Unexpected_Behavior/Splitting-SQL-statements-using-the-FME-SQL-DELIMITER-directive).
David
Hi David,
Thanks for the quick response. The sql now runs without error but when the workspace tries to write the new rows into the destination oracle table I get a primary unique constraint error.
2014-03-21 12:01:20| 2.1| 0.0|ERROR |Execution of statement `INSERT INTO "CMSS"."STAGE_ASSET_INFO" ( "TIER", "ASSET_TYPE", "GEOM_TYPE", "ASSET_ID", "SYNONYMNO", "NAME", "DECRIPTION", "LABEL_TEXT", "LICENSE_INFORMATION", "INTRNL_PRVNANCE_HISTORY", "CSW_CTLG_REF", "THUMB_URL", "WFS_URL", "WFS_NAMESPCE", "WMS_URL", "TILECACHE_URL", "LEGEND_URL", "RTRVL_TYPE_NAME", "SRL_RTRVL_TYPNM_LST", "HAS_SYNPSS_SRVCS", "IS_FTR_LVL_CNTRLLED", "FLD_CNTRL_STRNG_LST", "GA_INTRNL_ONLY", "IS_DOMAIN_LCKD", "ALLWD_CLNT_DMN_IP_LST", "DELETE_ROW", "IS_RESOURCE", "EFC_DECISION_DATE" ) VALUES ( :"TIER", :"ASSET_TYPE", :"GEOM_TYPE", :"ASSET_ID", :"SYNONYMNO", :"NAME", :"DECRIPTION", :"LABEL_TEXT", :"LICENSE_INFORMATION", :"INTRNL_PRVNANCE_HISTORY", :"CSW_CTLG_REF", :"THUMB_URL", :"WFS_URL", :"WFS_NAMESPCE", :"WMS_URL", :"TILECACHE_URL", :"LEGEND_URL", :"RTRVL_TYPE_NAME", :"SRL_RTRVL_TYPNM_LST", :"HAS_SYNPSS_SRVCS", :"IS_FTR_LVL_CNTRLLED", :"FLD_CNTRL_STRNG_LST", :"GA_INTRNL_ONLY", :"IS_DOMAIN_LCKD", :"ALLWD_CLNT_DMN_IP_LST", :"DELETE_ROW", :"IS_RESOURCE", :"EFC_DECISION_DATE")' did not succeed; error was `ORA-00001: unique constraint (CMSS.PK_STAGE_ASSSET_ASSET_ID_TIER) violated'. (serverType=`ORACLE8I', serverName=`distprod', userName=`CMSS_APP', password=`***', dbname=`')
I've only got one row in the table that should have been deleted by the SQL to Execute Before Translation statement I had the initial problem with.
Has this got something to do with when the commits are actually occurring?
I read a bit about it here:
http://fmepedia.safe.com/articles/Error_Unexpected_Behavior/Writing-to-an-Oracle-table-with-Foreign-Key-s
If the commits from the SQL to Execute Before Translation aren't actually being committed before the write then it would cause the issue I'm now experiencing.
Anyone have any experience with this issue?
Rob
Hi,
my guess is that the article you linked to is unrelated to thiserror. You are probably simply trying to insert a record with a primary key that already exists.
Look at the definition of CMSS.PK_STAGE_ASSSET_ASSET_ID_TIER in your database schema for details on what the primary key is (could be more than one field).
David
Still no luck with this. I ran a test in the with only one row in the destination table and one feature (row) trying to be written to the destination. Both had the same ASSET_ID (value defined in the published parameter called 'assetId') so the SQL Statement to Execute Before Translation
FME_SQL_DELIMITER ;
delete from CMSS.STAGE_ASSET_INFO
where CMSS.STAGE_ASSET_INFO.ASSET_ID='$(assetId)';
commit;
delete from CMSS.STAGE_CARTO_RSRC_INFO
where CMSS.STAGE_CARTO_RSRC_INFO.DM_ASSETID='$(assetId)'
or CMSS.STAGE_CARTO_RSRC_INFO.REF_FC_ASSETID='$(assetId)';
commit;
Should have deleted the one record from the destination prior to writing the new one.
The sql just doesn't seem to do anything.
I tried putting the actual assetId value into the sql and it worked fine.
FME_SQL_DELIMITER ;
delete from CMSS.STAGE_ASSET_INFO
where CMSS.STAGE_ASSET_INFO.ASSET_ID='Aab84eddc68f84ca1b211f993d5b8bf56';
commit;
delete from CMSS.STAGE_CARTO_RSRC_INFO
where CMSS.STAGE_CARTO_RSRC_INFO.DM_ASSETID='Aab84eddc68f84ca1b211f993d5b8bf56'
or CMSS.STAGE_CARTO_RSRC_INFO.REF_FC_ASSETID='Aab84eddc68f84ca1b211f993d5b8bf56';
commit;
Maybe it's not possible to access published parameter values in the SQL Statement to Execute Before Translation after all.
Hi,
which version of FME are you using? Some earlier versions (at least prior to FME 2013) have known inconsistencies in the support for embedded parameter references.
It seems like this has much improved for FME 2014.
If you can't upgrade to FME 2014, consider sending this issue to your FME reseller or Safe support.
David
Hi,
I'm using 2014. I'll raise a ticket with our FME Reseller.
As a work-around, I'd like to try and put the SQL in a Python Scripted Parameter then link that to the SQL to Execute Before Translation.
This is what I've tried:
import re
sql = "FME_SQL_DELIMITER ;\\ndelete from CMSS.STAGE_ASSET_INFO\\nwhere CMSS.STAGE_ASSET_INFO.ASSET_ID=" + str(FME_MacroValueso'assetId'] + ";\\ncommit;" +
+ "delete from CMSS.STAGE_CARTO_RSRC_INFO where CMSS.STAGE_CARTO_RSRC_INFO.DM_ASSETID=" + str(FME_MacroValuesr'assetId']) +
"\\nor CMSS.STAGE_CARTO_RSRC_INFO.REF_FC_ASSETID=" + str(FME_MacroValuesc'assetId'] + ";\\ncommit;"
sqlEsc = re.escape(sql)
return sqlEsc
I'm not sure if I have to escape the special characters or what the problem is with my syntax.
Any help would be greatly appreciated.
Thanks
Rob
I fixed some of the problems in my sql. When linked to the SQL to Execute Before Translation parameter, it passes as valid but does not perform the expected delete.
sql = "FME_SQL_DELIMITER ;\\ndelete from CMSS.STAGE_ASSET_INFO\\nwhere CMSS.STAGE_ASSET_INFO.ASSET_ID=" + str(FME_MacroValuesl'assetId']) + ";\\ncommit;" + "delete from CMSS.STAGE_CARTO_RSRC_INFO where CMSS.STAGE_CARTO_RSRC_INFO.DM_ASSETID=" + str(FME_MacroValuesl'assetId']) + "\\nor CMSS.STAGE_CARTO_RSRC_INFO.REF_FC_ASSETID=" + str(FME_MacroValuesl'assetId']) + ";\\ncommit;"
return sql
Hi,
you're right about not using the "re" module. The escaping mechanism there is for regular expressions and not for SQL.
Also, it seems like FME could be taking care of the transaction for you, so you might not need the COMMIT and, as a consequence, you won't use the FME_SQL_DELIMITER either.
The following scripted parameter works for me on a PostgreSQL writer:
---
sql = "DELETE FROM test_table WHERE pk = %s" % (FME_MacroValuesc'PK_TO_DELETE'])
return sql
---
David
