The list of questions and answers related to SQLite Database.
A: There are completed collations, functions, extensions API's and demos. Check "Demos\DBMS Specific\SQLite\UserCollation".
If you need collations / functions at design-time make design-time package (fake component) where you register user collations / functions.
A: The exception is raised on UPDATE command, when another DB connection has cursor on the same table and not all records are fetched from it. You must use FetchOptions.Mode=fmAll. Even you can set this option at ADConnection level, so all linked to it datasets will inherit this option.
A: SQLite does not return a data type for non-table SELECT list items. IOW, if an item is an expression, then AnyDAC cannot get it data type. So, it just describes it as ftWideString. AnyDAC has ability to specify SELECT item data type: <item> as "<alias>::<data type>". For example:
SELECT count(*) as "cnt::int" from ...
A: These functions are implemented using AnyDAC expression evaluator. When you are creating a connection at run-time, you should include uADStanExprFuncs.pas into "uses" clause.
SQLite has a limited set of build-in functions. But it allows to write and register custom functions. AnyDAC has many custom functions implemented for the expression evaluator. They implementations and registrations are in ADStanExprFuncs. SQLite driver automatically registers all known expression evaluator functions with sqlite3.dll.
A: Yes. Use the code like that:
procedure Tform1.FormCreate(Sender: TObject); begin ADConnection1.Open; with TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj) do begin ProgressNOpers := 50000; OnProgress := SQLiteOnProgress; end; end; procedure Tform1.SQLiteOnProgress(ADB: TSQLiteDatabase; var ACancel: Boolean); begin Application.ProcessMessages; end;
A: If WAL file keeps growing that means it’s impossible to move data from the log into the database file. Probably the database is permanently used by reading threads and/or there are performed DML operations without committing a transaction. The possible 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 (see PRAGMA journal_size_limit if you want it to be) here. The reason being 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. Preventing the WAL file from growing indefinitely. There are two things that can go wrong:
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 every checkpoint, then the WAL file will grow without bound.
What do you think about this topic? Send feedback!