SQLite Database Questions (FireDAC)

Go Up to FAQ (FireDAC)

This topic contains a list of questions and answers related to SQLite Database.

Q1: How can I define collate sequence in SQLite database?

A: Check "FireDAC\Samples\DBMS Specific\SQLite\UserCollation" to find completed collations, functions, extensions APIs and demos.

If you need collations/functions at design time, make design time package (fake component) where you register user collations/functions.

Q2: SQLite exception "[FireDAC][DApt]-401. Record is locked by another user"

A: The exception is raised on UPDATE command, when another DB connection has a cursor on the same table and not all records are fetched from it. You must use FetchOptions.Mode=fmAll. You can set this option at ADConnection level, so that all datasets linked to it inherit this option.

Q3: Data type for non-table SELECT list item is wrong? (or) My aggregated function returns a string value. What is wrong?

A: SQLite does not return a data type for the non-table SELECT list items. If an item is an expression, FireDAC cannot get its data type. So, it just describes it as ftWideString. FireDAC has the ability to specify SELECT item data type: <item> as "<alias>::<data type>". For example:

 
SELECT count(*) as "cnt::int" from ...

Q4: It seems that character macros are not supported with SQLite. I tried with CONCAT, LEFT, SUBSTR, UCASE

A: These functions are implemented using FireDAC expression evaluator. When you create a connection at run time, you should include uADStanExprFuncs.pas into the uses clause.

SQLite has a limited set of build-in functions, but it allows to write and register custom functions. FireDAC has many custom functions implemented for the expression evaluator. Their implementations and registrations are in FireDAC.Stan.ExprFuncs unit. SQLite driver automatically registers all known expression evaluator functions with sqlite3.dll.

Q5: Is sqlite3_progress_handler implemented with FireDAC?

A: Yes. Use code like the below:

procedure Tform1.FormCreate(Sender: TObject);
begin
  FDConnection1.Open;
  with TSQLiteDatabase(FDConnection1.ConnectionIntf.CliObj) do begin
    ProgressNOpers := 50000;
    OnProgress := SQLiteOnProgress;
  end;
end;

procedure Tform1.SQLiteOnProgress(ADB: TSQLiteDatabase; var ACancel: Boolean);
begin
  Application.ProcessMessages;
end;

Q6: WAL files are getting huge (>1 GB) when working with our multi-threaded application. Do you have any suggestion on how to prevent WAL files from growing?

A: If WAL file keeps growing that means it is impossible to move data from the log into the database file. Probably the database is permanently used by reading threads and/or DML operations are performed without committing a transaction. The solutions in this case are to use only short transactions or to create a separate thread for DML operations (in this case, transactions should also be constantly committed).

If a checkpoint runs and copies all WAL data into the database file, the next writer starts writing into the start of the WAL file again. The WAL file is not usually truncated here (see PRAGMA journal_size_limit if you want it to be). The reason is that it is faster to overwrite an existing file than it is to truncate one and then start appending to it.

So, if all goes well, SQLite should start over at the start of the WAL file after each checkpoint, thus preventing the WAL file from growing indefinitely. There are two things that can go wrong:

• A reader might prevent a check-pointer from copying all data from the WAL into the database file, or
• While the checkpoint is underway, some other process may be writing to the database (appending to the WAL file).

If either of the above occurs, then the next writer will append to the WAL file, instead of writing into the start of it. If this happens for every checkpoint, then the WAL file will grow without bound.