I have a SQLite3 database integrated into my application where the database is used as an underlying project container rather than a normal database.

In other words, it is used to store the current state of the project rather than as list management or normal database usage.

As such, I’ve created a lot of triggers to create automatic undo actions which may or may not be relevant to the problems I’m running into. An example of such a trigger is below. What is does is before an update on the Timepoints Table, it creates a new record in the UNDO TABLE with an UndoEventType of 3 (meaning it’s an update), and a dynamically generated SQL Query to restore that record to the current state.

CREATE TRIGGER Timepoints_BeforeUpdate BEFORE UPDATE ON TimePoints 
 FOR EACH ROW
   BEGIN
      INSERT INTO UndoTable(UndoGroupID, UndoEventType, SQLText)
      VALUES(-1,3,"UPDATE TimePoints SET 
      TimePointID="||quote(old.TimePointID)||", FrameID="||quote(old.FrameID)||",
      TimelineID="||quote(old.TimelineID)||", TimePointInMS="||quote(old.TimePointInMS)||", 
      CameraInstanceID="||quote(old.CameraInstanceID)||", BinaryWorld="||quote(old.BinaryWorld)||", 
      HoldTimeInMS="||quote(old.HoldTimeInMS)||", IsWayPoint="||quote(old.IsWayPoint)||"
      WHERE TimePointID = "||new.TimePointID);
    END

Again, I don’t know if that’s relevant but suddenly I’m getting query errors on queries that used to execute fine and still do when I execute them in DB Manager rather than my application.

An example of such a query that’s failing is:

VACUUM INTO "C:UsersSupportAppDataLocalTempprevis.vac"

My application is suddenly saying…

SQL Error: SQL logic error or missing database
Message : near “INTO”: syntax error
Code: 1 SQL: VACUUM INTO “C:UsersSupportAppDataLocalTempprevis.vac”‘.

This used to work fine and I don’t know what’s changed. Anyone have any insight?

And yes, I’ve verified that the directory exists, the file doesn’t as the exact same query works no issue in DB Manager even though I have all the same triggers.

The reason I brought up the triggers in the first place is that I ran into this issue with it recently failing with the same type of error on

INSERT INTO ... ON CONFLICT 

Where it said there was an SQL error at ON CONFLICT. When I removed the triggers that fixed it but at least that made some sense to me…

Any insight is greatly appreciated.

For full disclosure this is a Delphi FireMonkey App using Zeoslib 8.0