Hi,
Not sure if this is a bug.
I've found that the PostGIS writer's SQL Statement to Execute Before Translation can accidentally double encode the SQL such that it results in a fatal error on trying to write.
As a process;
- Enter this SQL code in the advanced editor (by clicking the elipsis button on the writer's Edit Parameters pane);
FME_SQL_DELIMITER ;
DROP INDEX IF EXISTS "TEMPSpeciesSightings_Catalogue_Number_Index";
DROP INDEX IF EXISTS "TEMPSpeciesSightings_Conservation_Index";
DROP INDEX IF EXISTS "TEMPSpeciesSightings_Date_Modified_Index";
DROP INDEX IF EXISTS "TEMPSpeciesSightings_Spatial_Index";
DROP INDEX IF EXISTS "TEMPSpeciesSightings_stateConservation_Index";
- FME encodes this to;
FME_SQL_DELIMITER<space><semicolon><lf>DROP<space>INDEX<space>IF<space>EXISTS<space><quote>TEMPSpeciesSightings_Catalogue_Number_Index<quote><semicolon><lf>DROP<space>INDEX<space>IF<space>EXISTS<space><quote>TEMPSpeciesSightings_Conservation_Index<quote><semicolon><lf>DROP<space>INDEX<space>IF<space>EXISTS<space><quote>TEMPSpeciesSightings_Date_Modified_Index<quote><semicolon><lf>DROP<space>INDEX<space>IF<space>EXISTS<space><quote>TEMPSpeciesSightings_Spatial_Index<quote><semicolon><lf>DROP<space>INDEX<space>IF<space>EXISTS<space><quote>TEMPSpeciesSightings_stateConservation_Index<quote><semicolon>- Click OK and then OK to exit the Parameters pane
- Re-enter the advanced SQL editor, click OK again back out without changing anything
- Go all the way back in and check the encoded SQL and it should look similar to this;
FME_SQL_DELIMITER<lt>space<gt><lt>semicolon<gt><lt>lf<gt>DROP<lt>space<gt>INDEX<lt>space<gt>IF<lt>space<gt>EXISTS<lt>space<gt><lt>quote<gt>TEMPSpeciesSightings_Catalogue_Number_Index<lt>quote<gt><lt>semicolon<gt><lt>lf<gt>DROP<lt>space<gt>INDEX<lt>space<gt>IF<lt>space<gt>EXISTS<lt>space<gt><lt>quote<gt>TEMPSpeciesSightings_Conservation_Index<lt>quote<gt><lt>semicolon<gt><lt>lf<gt>DROP<lt>space<gt>INDEX<lt>space<gt>IF<lt>space<gt>EXISTS<lt>space<gt><lt>quote<gt>TEMPSpeciesSightings_Date_Modified_Index<lt>quote<gt><lt>semicolon<gt><lt>lf<gt>DROP<lt>space<gt>INDEX<lt>space<gt>IF<lt>space<gt>EXISTS<lt>space<gt><lt>quote<gt>TEMPSpeciesSightings_Spatial_Index<lt>quote<gt><lt>semicolon<gt><lt>lf<gt>DROP<lt>space<gt>INDEX<lt>space<gt>IF<lt>space<gt>EXISTS<lt>space<gt><lt>quote<gt>TEMPSpeciesSightings_stateConservation_Index<lt>quote<gt><lt>semicolon<gt>Note all the previous encoding is doubly encoded. For example, <space> is now <lt>space<gt>.
If I were to accept this statement and try and run the workspace the write will fail with a syntax error.
Thanks.
