Solved

SQL Executor and spatialite - delete or drop commands rejected

1
Badge +1

I have some tables in a spatialite database, and I want to truncate the tables.

It seems there is no TRUNCATE command in sqlite/spatialite so I've used 'DELETE FROM <my table>' in SQL Exector.

I get a rejected feature with the following log message (in red text).

"SPATIALITE reader: In ExecuteSQL(): sqlite3_step(DELETE FROM boundaries): SQL logic error or missing database

SPATIALITE reader: Unable to execute SQL statement 'DELETE FROM boundaries'

SPATIALITE reader: Unable to read feature type '__fme_execute_sql_results__'

A fatal error has occurred. Check the logfile above for details."

I can execute the same command using SQLExecutor on an Oracle database, so the issue seems to be with the spatiaLite reader/writer format.

I've also tried 'DROP TABLE <my table>', but it returns the same rejection message.

Reading some documentation online, it seems you cannot delete on views in sqlite/spatialite, but I'm trying to access a table, not a view.

Any ideas?

Has anyone else encountered this? Have I missed something?

Thanks for any help

Mary

icon

Best answer by mark_f 11 August 2017, 12:24

View original

11 replies

david_r
Rank
Userlevel 4

My first hunch would be that your database file is corrupt in some way, your syntax looks good.

Can you open your database file using something like sqlite Browser? http://sqlitebrowser.org/

What happens if you try to execute your commands there?

T
Rank
Badge +6

Hello @1spatialmary,

 

 

It appears on the Spatialite format, we support SQL statements for read-only. As such INSERTS, UPDATES, or DELETES will not work through the SQLCreator or SQLExecutor. If you would like this functionality to be implemented. You can create an idea for the developers to look at!

https://knowledge.safe.com/content/idea/list.html

I hope that helps.

1
Badge +1

Hello @1spatialmary,

 

 

It appears on the Spatialite format, we support SQL statements for read-only. As such INSERTS, UPDATES, or DELETES will not work through the SQLCreator or SQLExecutor. If you would like this functionality to be implemented. You can create an idea for the developers to look at!

https://knowledge.safe.com/content/idea/list.html

I hope that helps.

Thanks @david_r and @trentatsafe.

 

I have been able to run the command from within sqlite browser, but thanks for pointing me to that.

 

I don't necessarily need to use SpatiaLite, but I do need to use something that is self-contained, and stores spatial and non-spatial data. So I had figured SpatiaLite for the spatial data, sqLite for the non-spatial. I've since tried OGC GeoPackage format, but with the same result - this seems to use SQLite also, so that makes sense. And I've tried SQLite Spatial (FDO) from your other pointer, but while the FeatureReader lists it as a format, the SQLExecutor does not so that format won't work for me either.

 

A colleague has suggested a FileCopyWriter to overwrite the db with a template db, and that works well enough for what I'm trying to do.

 

Thanks for your help
takashi
Rank
Userlevel 2
Badge +17

Hello @1spatialmary,

 

 

It appears on the Spatialite format, we support SQL statements for read-only. As such INSERTS, UPDATES, or DELETES will not work through the SQLCreator or SQLExecutor. If you would like this functionality to be implemented. You can create an idea for the developers to look at!

https://knowledge.safe.com/content/idea/list.html

I hope that helps.

I confirmed that the SQLExecutor cannot execute SQL statements (INSERT, UPDATE, DELETE, etc.) to update a SpatiaLite database.

 

It's not an urgent matter for me, but I feel something uncomfortable inconsistent in the design concept, since the transformer can update databases with other DB formats including SQLite.

 

Personally, I think the SQLExecutor should naturally support to execute any SQL statement on every DB format that FME supports, even if no user posted a related Idea.

 

david_r
Rank
Userlevel 4
I confirmed that the SQLExecutor cannot execute SQL statements (INSERT, UPDATE, DELETE, etc.) to update a SpatiaLite database.

 

It's not an urgent matter for me, but I feel something uncomfortable inconsistent in the design concept, since the transformer can update databases with other DB formats including SQLite.

 

Personally, I think the SQLExecutor should naturally support to execute any SQL statement on every DB format that FME supports, even if no user posted a related Idea.

 

I totally agree. Sounds like a bug to me.
1
Badge +1

Hello @1spatialmary,

 

 

It appears on the Spatialite format, we support SQL statements for read-only. As such INSERTS, UPDATES, or DELETES will not work through the SQLCreator or SQLExecutor. If you would like this functionality to be implemented. You can create an idea for the developers to look at!

https://knowledge.safe.com/content/idea/list.html

I hope that helps.

I've added an idea to the board to improve the SQLExecutor so that it operates the same way on any dtabase format it supports. (https://knowledge.safe.com/idea/50358/improve-sqlexecutor-to-operate-the-same-way-on-all.html)

 

Thanks all

 

 

M
Badge +2

@1spatialmary Try using SQLite Spatial (FDO) format, but then use SQLExecutor with SQLite Non-Spatial format to delete from the tables. This seems to work as you are only clearing down the table rather than inserting new rows.

Example attached.

sqllitecleanup.fmwt

 

1
Badge +1

@1spatialmary Try using SQLite Spatial (FDO) format, but then use SQLExecutor with SQLite Non-Spatial format to delete from the tables. This seems to work as you are only clearing down the table rather than inserting new rows.

Example attached.

sqllitecleanup.fmwt

 

Thanks @mark_1spatial

 

- that's worked great
C
Badge +1

@1spatialmary Try using SQLite Spatial (FDO) format, but then use SQLExecutor with SQLite Non-Spatial format to delete from the tables. This seems to work as you are only clearing down the table rather than inserting new rows.

Example attached.

sqllitecleanup.fmwt

 

Is there a workaround for creating SpatiaLite views that include a geometry columns too?

 

 

CREATE VIEW IF NOT EXISTS my_view AS SELECT OGC_FID, GEOMETRY FROM my_table

 

 

It results in the same: SQL logic error or missing database

 

kimo
Badge +1

All these operations can of course be done on spatialite databases at the command line level of sqlite and also using a python connector, so I cannot see why they are disabled in FME. You can also attach multiple sqlite databases and run SQL queries across separate databases. That is much faster than having to copy large tables into one database before running a query.

I have tried the InlineQuerier and DatabaseJoiner which all work but are very slow because the data must be read first. If I could do the equivalent of an IN (id, id,...id) statement the same as I can in Python that would be perfect.

A
Rank
Badge +10

Hi @1spatialmary​  et al.,

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

Reply


Community Stats

28,731
Posts
109,527
Replies
36,565
Members
Terms & ConditionsCookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.