Referencing a published parameter in Oracle Writer SQL Statement To Execute Before Translation

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

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,

 

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_MacroValues['assetId'] + ";\\ncommit;" +

 

+ "delete from CMSS.STAGE_CARTO_RSRC_INFO where CMSS.STAGE_CARTO_RSRC_INFO.DM_ASSETID=" + str(FME_MacroValues['assetId']) +

 

"\\nor CMSS.STAGE_CARTO_RSRC_INFO.REF_FC_ASSETID=" + str(FME_MacroValues['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

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_MacroValues['PK_TO_DELETE'])

 

 

return sql

 

---

 

 

David