It would be good if the SQL Executor operates the same way on any database formats its supports.
Recently I've had an issue whereby I cannot execute a 'DELETE from <my-table>' statement on SpatiaLite, but I can execute the same statement on Oracle Spatial.
See the following Knowledge Centre question and supporting answers/comments at
https://knowledge.safe.com/questions/50230/sql-executor-and-spatialite-delete-or-drop-command.html
I have a feeling this is also the cause why "with" statements don't work either.
I think my issues with creating views (and indices, though they can be made using the SQLite non-spatial) are due to the same limitations.
But why? I am using spatialite precisely to do fast sql selections for real-time updates. I really need a Truncate function. Drop and Create is not possible because it invokes a file lock.
I'm please to let you know that a recent update to FME 2020.2 beta (build 20709 and newer) has enabled SQL executing statements for the SpatiaLite format in the SQLCreator and SQLExecutor. We now support more than read-only SQL statements when using the SpatiaLite format (eg. INSERT/UPDATE/DELETE/CREATE/DROP).
You can find the latest beta build here: www.safe.com/beta
- Andrea