This reference article has few sections:

• Introduction to SQLite: reviews SQLite features, missed features, possible applications, and applications not for SQLite.
• Using SQLite Database: explains how to create, connect to and manage SQLite database in Delphi application.
• SQLite Encrypted Database: the database encryption is one of the important SQLite features. The topic explains how it works and how to control it.
• SQLite Data Types: SQLite has an unique data type system. Without understanding of how it works, will be hard to effectively store and retrieve the data in Delphi applications.
• SQLite SQL Commands: main aspects of SQLite SQL dialect for the Delphi application developers.
• SQLite Transactions, Locking and Cursors: explains how to work with transactions in SQLite environment.
• Extending SQLite Engine: as an embedded DBMS, SQLite engine may be extended by the Delphi application code.
• Advanced SQLite Techniques: finally we want to introduce some advanced SQLite concepts, like updates logging and SQL authorization.

This article requires the knowledge of the AnyDAC basics and of the main library API's. For the beginners we will suggest to start from the Getting Started article and look into the AnyDAC\Samples\Getting Started\SQLite demo. 

The SQLite is an embedded SQL database engine, developed by SQLite Consortium. It is a DBMS most widely deployed in the world with rough estimate of 500M installations. You can find it on all iOS and Android mobile devices, on Mac OS and Linux desktop's. It is used by Firefox, Skype and McAfee anti-virus. 

The source lists:

• Transactions are atomic, consistent, isolated, and durable (ACID) even after system crashes and power failures.
• Zero-configuration - no setup or administration needed.
• Implements most of SQL92. Supports table triggers and views.
• A complete database is stored in a single cross-platform disk file.
• Supports terabyte-sized databases and gigabyte-sized strings and blobs.
• Faster than popular client/server database engines for most common operations.
• Self-contained: no external dependencies.
• Cross-platform: Windows, Linux, Mac OS X, iOS and Android are supported out of the box.
• Sources are in the public domain. Use for any purpose is free.
• Very powerful API, allowing to extend engine in practically all areas.
• SQLite achieves one of the best data access performances among other embedded, file-server and client-server database engines used by the Delphi applications. We are aware of many successful applications with multi gigabyte databases. For example Silwood Technology Saphir is built with SQLite, Delphi and AnyDAC.

According to our experience, the developers often trying to find the following features, not presented in SQLite:

• Stored procedures. AnyDAC offers custom functions API.
• Reach set of build-in functions. AnyDAC pre installs about 50 standard functions.
• Security system, including an user and a right concepts. AnyDAC offers password protected encrypted database, special callbacks to filter user actions.
• Collations (only ASCII and binary). AnyDAC allows to define custom collations.

The source lists:

• Application File Format. SQLite has been used with great success as the on-disk file format for desktop applications.
• Embedded devices and applications. Because SQLite database requires little or no administration, SQLite is a good choice for devices or services that must work unattended and without human support.
• Websites. SQLite usually will work great as the database engine for low to medium traffic websites (which is to say, 99.9% of all websites).
• Replacement for ad hoc disk files. SQLite works particularly well as a replacement for these ad hoc data files.
• Internal or temporary databases. For programs sifting and sorting the data in diverse ways, may be more simple to load data into in-memory SQLite database and use full scale SQL command.
• Stand-in for an enterprise database during demos or testing.
• Database Pedagogy.

The same source lists:

• High Concurrency. SQLite uses reader/writer locks on the entire database file. That means if any process is reading from any part of the database, all other processes are prevented from writing any other part of the database. Similarly, if any one process is writing to the database, all other processes are prevented from reading any other part of the database.
• Client/Server Applications. If you have many client programs accessing a common database over a network, you should consider using a client/server database engine instead of SQLite. SQLite will work over a network file system, but because of the latency associated with most network file systems, performance will not be great.
• Very large datasets (N tb).

To link AnyDAC SQLite driver into Delphi application add TADPhysSQLiteDriverLink to a form or data module. To connect to SQLite database, specify SQLite driver parameters, at least: 

DriverID=SQLite
Database=<path to SQLite database>

By default, all SQLite driver settings are set for the high-performance single connection access to a database in a stable environment. The PRAGMA command allows to configure SQLite. Many AnyDAC SQLite driver parameters correspond to the pragma's. Additionally SQLiteAdvanced allows to specify multiple pragma's separated by ';' as a single connection parameter. 

The additional SQLite use cases are:

N	Application specifics	Description
1	Reading large DB.	Set CacheSize to a higher number of pages, which will be used to cache the DB data. The total cache size will be CacheSize * <db page size>.
2	Exclusive updating of DB.	Consider to set JournalMode to WAL (more).
3	Long updating transactions.	Set CacheSize to a higher number of pages, that will allow to run transactions with many updates without overloading the memory cache with dirty pages.
4	A few concurrent updating processes.	Set LockingMode to Normal to enable shared DB access. Set Synchronous to Normal or Full to make committed data visible to others. Set UpdateOptions.LockWait to True to enable waiting for locks. Increase BusyTimeout to raise a lock waiting time. Consider to set JournalMode to WAL.
5	A few concurrent updating threads.	See (4). Also set SharedCache to False to minimize locking conflicts.
6	A few concurrent updating transactions.	See (4) or (5). Also set TxOptions.Isolation to xiSnapshot or xiSerializible to avoid possible transaction deadlocks.
7	High safety.	Set Synchronous to Full to protect DB from the committed data losts. Also see (3). Consider to encrypt database to provide integrity.
8	High confidentiality.	Encrypt database to provide confidentiality and integrity.
9	Development time.	Set LockingMode to Normal to enable simultaneous use of SQLite DB in IDE and a debugged program.

SQLite database will be created at a connection establishment if it does not exist. For explicit control the Delphi application may specify: 

OpenMode=CreateUTF8 | CreateUTF16 | ReadWrite | ReadOnly

First two values allow the creation and differ by encoding that will be used for the new database. Also we recommend to set page_size to 4096 or more for the database with multi row tables. That may be done by specifying at creation time: 

SQLiteAdvanced=page_size=4096

Consider to specify parameters using SQLiteAdvanced:

• auto_vacuum
• default_cache_size

Note, after the database file is created it has the zero size. As result the database encoding, page size and other persistent parameters are not recorded in the database. To make such parameters persistent, the application should create at least one table. 

Next SQLite unique feature is the ability to work with the pure in-memory databases. That means, no files will be created to store database objects and all will be kept in the memory. So, more security, more performance, less requirements to the environment rights for a Delphi application. 

To create and open a SQLite in-memory database use parameters: 

DriverID=SQLite
Database=:memory:

Or just leave Database parameter empty: 

DriverID=SQLite

One AnyDAC customer had a SQLite database on a shared network resource. The database is a read/only products catalogue with many stored product attributes. To radically improve performance, the customer used TADSQLiteBackup to move the database in whole into the in-memory database. The sample code: 

ADConnection1.DriverName := 'SQLite';
ADConnection1.Open;

ADSQLiteBackup1.Database := '\\srv\db\data.sdb';
ADSQLiteBackup1.DestDatabaseObj := ADConnection1.CliObj;
ADSQLiteBackup1.DestMode := smCreate;
ADSQLiteBackup1.Backup;

AnyDAC supports Unicode Delphi's (starting with Delphi 2009) in full. For SQLite that means:

• AnyDAC automatically setups SQLite database to exchange all metadata in UTF-16 encoding, when Delphi 2009 or higher is used. In Delphi 2007 or less the metadata is ANSI encoded.
• The data will be defined and exchanged as is described in the chapter "Mapping SQLite to AnyDAC Data Types".

SQLite allows to use multiple databases in a single connection. A DB specified by the Database parameter is the main database. To attach additional databases, Delphi application should use ATTACH command. For example: 

ADConnection1.ExecSQL('ATTACH ''c:\hr.sdb'' AS hr');
ADConnection1.ExecSQL('ATTACH ''c:\cust.sdb'' AS cust');
ADQuery1.Open('select * from "Orders" o ' +
  'left join hr."Employees" e on o.EmployeeID = e.EmployeeID ' +
  'left join cust."Customers" c on o.CustomerID = c.CustomerID');

Note, that AnyDAC interprets a database name as a catalog name. 

A good Delphi (and not only) SQLite database application must be aware of the following facts:

• SQLite database may become fragmented and non-optimal after many "hard" record updates or deletes. The TADSQLiteValidate.Sweep method call optimizes the database. This method corresponds to the VACUUM command and PRAGMA auto_vacuum. And the example:

ADSQLiteValidate1.Database := 'c:\db.sdb';
ADSQLiteValidate1.Sweep;

• SQLite query optimizer builds a better query execution plan when it has an up-to-date database statistic. SQLite does not automatically update the statistic. The TADSQLiteValidate.Analyze method call collects it. This method utilizes the ANALYZE command. An application may collect statistic for the full database:

ADSQLiteValidate1.Database := 'c:\db.sdb';
ADSQLiteValidate1.Analyze;

• SQLite database may become corrupted or malformed. To verify it integrity use the TADSQLiteValidate.CheckOnly method. Note, to repair a broken SQLite database, Delphi application needs to restore it from a backup. CheckOnly method uses OnProgress event handler to notify about issues. This method performs PRAGMA integrity_check command.

procedure TForm1.ADSQLiteValidate1Progress(ASender: TADPhysDriverService; const AMessage: String);
begin
  Memo1.Lines.Add(AMessage);
end;

ADSQLiteValidate1.Database := 'c:\db.sdb';
ADSQLiteValidate1.OnProgress := Form1Progress;
ADSQLiteValidate1.CheckOnly;

• SQLite database must be backed up regularly to preserve the data loss. The TADSQLiteBackup component performs the database backup copy. This is how the simplest backup code looks:

ADSQLiteBackup1.Database := 'c:\db.sdb';
ADSQLiteBackup1.DestDatabase := 'c:\db.backup';
ADSQLiteBackup1.DestMode := smCreate;
ADSQLiteBackup1.Backup;

One of the distinctive SQLite features is the high-speed strong database encryption. It allows to make database file content confidential and enforce integrity control on the database file. 

The encrypted database format is not compatible with other similar SQLite encryption extensions. That means, you cannot use an encrypted database, encrypted with non-AnyDAC libraries. If you need that, then you have to decrypt a database with original tool and encrypt it with AnyDAC. 

The encryption is provided through the officially supported SQLite approach - custom codec code and compilation with SQLITE_HAS_CODEC defined. All encryption routines are implemented on Delphi and embedded into sqlite3 code. As result, the encryption is correctly handled for:

• ATTACH;
• VACUUM;
• database recovery;
• database backup.

There NNN is the key size, which may be 128, 192 or 256 bits. 

The encryption may be controlled:

• through the connection definition parameters Encrypt, NewPassword and Password;
• through the TADSQLiteSecurity service component.

The password connection definition parameters may have a form: 

[aes-128 | aes-192 | aes-256 | aes-ctr-128 | aes-ctr-192 | aes-ctr-256 |
 aes-ecb-128 | aes-ecb-192 | aes-ecb-256:] password

There "aes-XXX-NNN:" is an optional prefix, controlling the cipher algorithm to use. If it is not specified, then will be used:

• algorithm, specified by Encrypt parameter;
• aes-256 if nothing is specified.

AnyDAC supports encryption operations:

The ATTACH command got an extension. The full syntax of the ATTACH now is: 

ATTACH [DATABASE] 'filename' [AS name] [KEY 'password']

When KEY is omitted, then the password value will be inherited from the main database. To specify an empty password to attach an unencrypted database, use something like that: 

ATTACH 'D:\tmp\test.db' AS tst KEY ''

SQLite has "type less" data type system. Practically that means, that you can use any identifier as a column data type name. For example "DELPHI" will work too and will correspond to the string data type. To make SQLite approach more compatible with other DBMS's and Delphi, and more comfortable for the Delphi application developers, AnyDAC recognizes data type names as described in the table: 

Note, with SQLite the FormatOptions.StrsTrim works for all string data types. 

To add an auto incrementing column to a table, define a column as INTEGER PRIMARY KEY AUTOINCREMENT. This type is mapped to dtInt32, Attrs = [caAutoInc]. For more details about handling auto-incrementing columns read "Auto-Incremental Fields". 

The columns with ROWID, _ROWID_ or OID type names are considered as identifying row columns. This types are mapped to dtInt64, Attrs = [caSearchable, caAllowNull, caROWID]. For more details about handling row identifying columns read "Unique Identifying Fields". SQLite ROWID is the fastest way to get an access to a specific row: 

SELECT * FROM Orders WHERE ROWID = :RID

Some SQLite driver parameters allow for Delphi application to adjust the data representation:

Note, that changing GUIDFormat or DateTimeFormat, when the database is not empty, may lead to errors, as AnyDAC may fail to read and parse the stored values. 

For an expression in a SELECT list the SQLite avoids type name information. When result set is not empty, AnyDAC will use the value data types from the first record. When empty, AnyDAC will describe those columns as dtWideString. To explicitly specify the column data type, append ::<type name> to the column alias: 

SELECT count(*) as "cnt::INT" FROM mytab

When Delphi application requires SQLite native data type representation, then use AnyDAC mapping rules. For example, map TEXT columns to dtAnsiString and INT columns to dtInt64: 

with ADQuery1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules do begin
    SourceDataType := dtMemo;
    TargetDataType := dtAnsiString;
  end;
  with MapRules do begin
    SourceDataType := dtInt32;
    TargetDataType := dtInt64;
  end;
end;

Although SQLite closely follows to the ANSI SQL 92, some features and commands are not supported, and some powerful are added. You can find more info about SQLite SQL dialect at:

• SQL As Understood By SQLite.
• SQLite / ANSI SQL Commands and Features Not Supported.

AnyDAC SQLite driver supports the SQL command batches. SQL commands must be separated by ';'. SQLite allows to mix any commands in a batch, including DDL and DML. For example: 

with ADQuery1.SQL do begin
  SQL.Clear;
  SQL.Add('create table dbms (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''sqlite'');');
  SQL.Add('insert into tab values (2, ''mysql'');');
  SQL.Add('insert into tab values (3, ''firebird'');');
  SQL.Add('create table langs (id integer, name varchar(20));');
  SQL.Add('insert into tab values (1, ''delphi'');');
  SQL.Add('insert into tab values (2, ''c'');');
  SQL.Add('insert into tab values (3, ''c++'');');
  SQL.Add('select * from dbms;');
  SQL.Add('select * from langs;');
end;
ADQuery1.Open;
// process here the DBMS list
ADQuery1.NextRecordSet;
// process here the programming languages list

AnyDAC TADScript does not support SQLite syntax, where script control commands are starting from '.'. 

Starting from v 3.7.11 the SQLite supports INSERT command with multiple VALUES. AnyDAC uses this feature to implement Array DML, when Params.BindMode = pbByNumber. Otherwise AnyDAC will emulate Array DML. For example: 

// here ADQuery1.Params collection is filled by 3 parameters
ADQuery1.SQL.Text := 'insert into MyTab values (:p1, :p2, :p3)';
// set "by number" parameter binding mode
ADQuery1.Params.BindMode := pbByNumber;
ADQuery1.Params.ArraySize := 100;
for i := 0 to ADQuery1.Params.ArraySize - 1 do begin
  ADQuery1.Params[0].AsIntegers[i] := i;
  ADQuery1.Params[1].AsStrings[i] := 'qwe';
  ADQuery1.Params[2].Clear(i);
end;
ADQuery1.Execute(ADQuery1.Params.ArraySize);

Read the following original SQLite articles:

• BEGIN TRANSACTION
• SQLite Shared-Cache Mode
• Corruption Following Busy

SQLite, as a file-server DBMS, likes to lock the database tables at updates. The following settings affect the concurrent access:

• when multiple threads are updating the same database, set SharedCache connection parameter to False. That will help to avoid some possible deadlocks.
• when multiple processes or threads are updating the same database tables, set set LockingMode to Normal to enable concurrent access to the tables. Also set Synchronous connection parameter to Full or Normal. So, SQLite will update a database file right after the transaction is finished. And other connections will see the updates on the predictable basis.
• to avoid locking conflicts between connections, set UpdateOptions.LockWait to True and set BusyTimeout to a higher value.
• to avoid locking conflicts between long running updating transactions, set TADConnection.TxOptions.Isolation to xiSnapshot or xiSerializible.

SQLite supports transactions in full, including check points. Further is a list of isolation modes supported by SQLite:

Mode	Corresponds to
xiDirtyRead	PRAGMA read_uncommitted = 1
xiReadCommitted	BEGIN TRANSACTION DEFERRED
xiRepeatableRead	The same as xiReadCommitted.
xiSnapshot	BEGIN TRANSACTION IMMEDIATE
xiSerializible	BEGIN TRANSACTION EXCLUSIVE

Surrounding writing commands into a transaction may radically improve SQLite performance. That is notable at large data modifications. The same is applicable to AnyDAC Array DML feature. So, surround the data modification code into a transaction to get the best performance: 

ADConnection1.StartTransaction;
try
  ADQuery1.SQL.Text := 'insert into tab values (:id, :name)';
  ADQuery1.Params.ArraySize := 10;
  for i := 0 to ADQuery1.Params.ArraySize - 1 do begin
    ADQuery1.Params[0].AsIntegers[i] := i;
    ADQuery1.Params[0].AsStrings[i] := 'name' + IntTostr(i);
  end;
  ADQuery1.Execute(ADQuery1.Params.ArraySize, 0);
  ADConnection1.Commit;
except
  ADConnection1.Rollback;
  raise;
end;

SQLite does not allow to rollback a transaction, when there are commands with not yet fetched result sets. To workaround that, AnyDAC will fetch all remaining records from a result set on a Rollback method call. See the FetchOptions.AutoFetchAll. 

SQLite does not support stored procedure or function concept, as it allows to use the host language environment to extend the engine functionality. SQLite allows to register host language functions in SQLite engine and use them in the SQL commands. AnyDAC simplifies this by introducing the TADSQLiteFunction component. 

To build a function, the developer has to set FunctionName, ArgumentsCount and create the OnCalculate event handler. Setting Active to True will register custom function at SQLite engine. For example: 

procedure TForm1.ADSQLiteFunction1Calculate(AFunc: TSQLiteFunction;
  AInputs: TSQLiteInputs; AOutput: TSQLiteOutput; var AUserData: TObject);
begin
  AOutput.AsInteger := AInputs[0].AsInteger * AInputs[1].AsInteger;
end;

ADSQLiteFunction1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteFunction1.FunctionName := 'XmY';
ADSQLiteFunction1.ArgumentsCount := 2;
ADSQLiteFunction1.OnCalculate := ADSQLiteFunction1Calculate;
ADSQLiteFunction1.Active := True;

This function usage: 

ADQuery1.Open('select RegionID, XmY(RegionID, 10) from "Region"');

A function may call the AnyDAC methods to query a database. To create a custom function with the default or different number of arguments you will need to specify the same FunctionName and different number of arguments. That will register an overloaded function inside the SQLite engine. 

The above and other function samples you can find in AnyDAC\Samples\DBMS Specific\SQLite\UserFunc folder. 

AnyDAC implements and installs to a SQLite connection about 50 functions, which are standard de-facto for many DBMS and are implemented by the AnyDAC local expressions engine. Note, when you are creating SQLite connection at run-time, you should include uADStanExprFuncs unit into "uses" clause, otherwise you will get an exception: 

[AnyDAC][Phys][SQLite] ERROR: no such function: UCASE.

To make custom functions accessible at design-time, create a custom design-time package with a data module, drop the components on this module and setup properly. Create the module in the module unit initialization section, and destroy in the finalization section. Then install your package into Delphi IDE. 

And the Ron Grove movie: 

SQLite stores and handles all character data either in UTF8 or UTF16, depending on the OpenMode connection parameter. When SQLite needs to compare or sort a character data, it must know what rules to use for that. The rules are known as a collation. 

SQLite has few build-in collations. None of them produces a correct sorting for German, Cyrillic, Arabian, etc phrases. You have to use TADSQLiteCollation component to build your own collation. Set CollationName, Flags, Locale, then set Active to True to register the collation with SQLite engine. For example: 

ADSQLiteCollation1.DriverLink := ADPhysSQLiteDriverLink1;
ADSQLiteCollation1.CollationName := 'UTF16NoCase';
ADSQLiteCollation1.Flags := [sfIgnoreCase];
ADSQLiteCollation1.Active := True;

Above component setup with default CollationKind=scCompareString implements a standard Win32 Unicode collation. The application may implement custom collations using CollationKind=scCustomUTF16 or scCustomUTF8 and implementing OnCompare event handler. And how to use this collation: 

SELECT * FROM "Employees" ORDER BY LastName COLLATE UTF16NoCase

To specify default collation for a column you can do: 

CREATE TABLE IF NOT EXISTS test_col (f1 VARCHAR(10) COLLATE UTF16NoCase)

Note, that there is no ability to specify default collation for a connection, a database or a table. The above collation samples you can find in AnyDAC\Samples\DBMS Specific\SQLite\UserCollation folder. 

If you do not use custom collations, then by default SQLite will use a binary sorting order. For TADTable Live Data Window mode is important to have the same client side and database sorting orders. To enable client side binary sorting order set FormatOptions.SortLocale to 0. 

AnyDAC supports notification of an Delphi application from a SQLite database trigger about some events, like a data change. For that AnyDAC uses similar to Firebird approach and registers POST_EVENT custom function. To call it from a trigger do: 

CREATE TRIGGER update_orders UPDATE ON "Orders"
BEGIN
  SELECT POST_EVENT('Orders');
END;

To receive an event notification the Delphi application uses TADEventAlerter component. For example: 

ADEventAlerter1.Names.Text := 'Orders';
ADEventAlerter1.Options.Synchronize := True;
ADEventAlerter1.OnAlter := DoAlert;
ADEventAlerter1.Active := True;

procedure TForm1.DoAlert(ASender: TADCustomEventAlerter;
  const AEventName: String; const AArgument: Variant);
begin
  if CompareText(AEventName, 'Orders') = 0 then
    qryOrders.Refresh;
end;

Local SQL engine allows to use TDataSet descendants in your SQL queries. AnyDAC uses SQLite Virtual Table API to implement Local SQL. 

SQLite provides an unique API allowing to monitor all updates to a database. This feature may be used, for example, to log all updates to a DB. To work with this API, a Delphi application should set OnUpdate event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened. For example: 

procedure TForm1.DoUpdate(ADB: TSQLiteDatabase; AOper: Integer; const ADatabase, ATable: String; ARowid: sqlite3_int64);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %u', [AOper, ADatabase, ATable, ARowid]));
end;

ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnUpdate := DoUpdate;

This sample you can find in AnyDAC\Samples\DBMS Specific\SQLite\OnUpdate folder. 

The SQLite is an embedded DBMS. That implies that it is a single user DBMS and does not need such concepts as a user, access rights, etc. Still some application may benefit from an access right control, for example:

• An application may restrict rights depending on a end-user license. Demo license - less possibilities, full license - all possibilities.
• Multi-tier data access frameworks may use their own user concept and to control data access rights using some generic approach.

And again, SQLite provides an unique feature allowing to authorize or not SQL commands. To work with this API, a Delphi application should set OnAutorize event handler of the TSQLiteDatabase object, which is a database connection wrapping object. Hook this event after a database connection is opened. For example: 

procedure TForm1.DoAuthorize(ADB: TSQLiteDatabase; ACode: Integer; const AArg1, AArg2, AArg3, AArg4: String; var AResult: Integer);
begin
  Memo1.Lines.Add(Format('%d - %s - %s - %s - %s', [ACode, AArg1, AArg2, AArg3, AArg4]));

  // Deny any delete operation
  if ACode = SQLITE_DELETE then
    AResult := SQLITE_DENY
  else
    AResult := SQLITE_OK;
end;

ADConnection1.Connected := True;
TSQLiteDatabase(ADConnection1.ConnectionIntf.CliObj).OnAutorize := DoAuthorize;

This sample you can find in AnyDAC\Samples\DBMS Specific\SQLite\OnAuthorize folder. 

When you need to get the maximum SQLite data access performance, then you should consider to use AnyDAC SQLite API wrapping classes. This is a low-level thin object oriented API, which is used by the AnyDAC SQLite driver. This API is not documented and not officially supported. 

The following example shows how to control transactions and fetch records using a parameterized SELECT command: 

uses
  uADPhysSQLiteWrapper;

procedure TForm1.FormCreate(Sender: TObject);
var
  oDB: TSQLiteDatabase;
  oTran: TSQLiteStatement;
  oStmt: TSQLiteStatement;
  i: Integer;
begin
  ADConnection1.Connected := True;
  oDB := TSQLiteDatabase(ADConnection1.CliObj);

  oTran := TSQLiteStatement.Create(oDB);
  try
    // start transaction
    oTran.Prepare('BEGIN');
    oTran.Execute;

    oStmt := TSQLiteStatement.Create(oDB);
    try
      // prepare statement
      oStmt.Prepare('select * from "Orders" where OrderID > :ID1 and OrderID < :ID2');

      // add bind variables (parameters)
      for i := 1 to oStmt.ParamDefsCount do
        TSQLiteBind.Create(oStmt.Params);

      // add column variables (fields)
      for i := 1 to oStmt.ColumnDefsCount do
        TSQLiteColumn.Create(oStmt.Columns).Index := i - 1;

      // set parameter values and execute
      oStmt.Params[0].AsInteger := 11000;
      oStmt.Params[1].AsInteger := 12000;
      oStmt.Execute;

      // fetch records and read columns
      while oStmt.Fetch do
        Memo1.Lines.Add(Format('OrderID: %d, CustomerID: %s',
          [oStmt.Columns[0].AsInteger, oStmt.Columns[1].AsString]));
    finally
      oStmt.Free;
    end;

    // commit transaction
    oTran.Unprepare;
    oTran.Prepare('COMMIT');
    oTran.Execute;
  finally
    oTran.Free;
  end;
end;

This article has provided the benefits of combining SQLite and AnyDAC to get premier embedded DBMS solution. The articles describes all aspects of the AnyDAC SQLite driver usage.

Connect to SQLite database, uADPhysSQLite Namespace