FME has disabled truncating a Spatialite table, so I need a hack to do it anyway. I want it to exist so that other applications can read it after it is updated.
I need to truncate rather than drop_and_create to refresh the table with an insert because I have another transformer right afterwards doing an SQLExecutor that does a selection from a large table in the same sqlite database. It seems that a lock is added that prevents the drop/create.
So you might think that I could hack the truncate using SQLExecutor, but no! All database operators are disabled in FME for spatialite so the "DELETE FROM table" is not allowed.
Never mind I will use Python. Ah, but now that I am using FME 64 bit I need to use Python3.6 and that is now from Anaconda which does not have sqlite as a standard module for ArcGISPro, so my python hack also fails.
Well then, I will just shell out to sqlite.exe. But wait, FME implements it as sqlite_fme.dll, not a EXE.
I can truncate the table if I use a filegeodatabase but then I cannot run SQLExecutor because a filegeodatabase does not have an SQL interface. That leaves me reading the whole table and doing a databaseJoin which takes 100 times longer and is too long (5 minutes v 13 seconds) for a real-time application.
The simple solution is for FME to upgrade the Spatialite/Sqlite writers to be fully SQL compliant. There is already an old 2017 idea posted for that, hence the need for a hack.
Best answer by kimo
View original
