This wrapper comes in the form of two classes
- TSQLTables: Use one instance of TSQLTables for each SQLite database you want to connect to. It has four principal methods
constructor CreateEx(const AFile:WideString; AMode:TSyncModes; ATimeOut:Integer)
This is the object constructor. Pass the name of the SQLite database file you want to connect to as the first parameter. WARNING:SQLite simply creates a new database if the file does not exist! The second parameter should normally have the value smNormal. Make sure you understand SQLite Pragmas before experimenting with the alternatives. Provide a timeout period, in milliseconds, as the third parameter. If a SQLite API call fails because the database is locked by another application, the SQLite engine will keep trying until ATimeOut milliseconds have elapsed before returning an error.
function MakeTable(const SQL: WideString;MaxMem,MaxRows: Integer):TSQLTable
Call MaxTable to execute a SQL SELECT and extract data from tables in the database. MaxMem is the maximum in-memory size of the table in kilobytes. MaxRows is the maximum number of rows that the query is allowed to return. The first parameter is the actual SQL statement that is executed to generate the table, e.g.
SELECT * FROM Countries.
procedure ExecSQL(const ASQL:WideString)
Use this method to execute single SQL statements.
procedure ExecSQLEx(const ASQL:WideString)
Use this method to execute multiple SQL statements, separated by a semicolon.
- TSQLTable: The
TSQLTables.MakeTablemethod returns an instance of TSQLTable by executing the SQL SELECT statement passed to it. In ADOSpeak the return value from MakeTable is a
recordset. Its properties are listed below
- BOF: Boolean – true if the cursor is at the beginning of the recordset.
- EOF: Boolean – true if the cursor is at the end of the recordset.
- ColCount: Integer – The number of columns in the recordset.
- RowCount: Integer – The number of rows in the recordset.
- Row: Integer – The current row, i.e. the position of the cursor in the recordset.
The name of the field in the Index’th column of the recordset.
This returns the value of the Index’th field of the current row formatted as a string. This is the default property.
FieldByName[const Index:WideString]: Variant
Same as the above but we locate the field via its column field name.
The principal methods are as follows
constructor Create(const SQL:WideString; MaxMem,MaxRows: Integer): TSQLTable
Called by the
TSQLTables.MakeTablemethod, described above.
function FieldIndex(const AField: WideString): Integer
returns the location of AField in the recordset columns. If no field bearing that name exists an ESQLite exception will be triggered.
function FieldAs#(constIndex: Integer):?
There are variants of this method to return the Index’th field as an Integer, Double, WideString etc. If the field type at Index is unsuitable or if Index is invalid, an ESQLite exception will be triggered. It should be noted that
FieldAsBlobreturns a raw pointer which should be typecast to a TMemoryStream before use. The memory stream is owned by TSQLTable. It should not be released by the caller.
MoveFirst, MoveLast, Nextand
Previousmethods are used to move the position of the cursor in the recordset. These methods return false the operation would be place the cursor in an invalid position.
Usage: Create one instance of TSQLTables for each SQLite database you want to connect to concurrently. If the database is being accessed by multiple applications make sure you use a sensible timeout to avoid frequent failures to access the database. Use the
MakeTable method to extract a selection of data from the database. Remember to free all the TSQLTables instances you have created as well as all the TSQLTable instances returned by calls to
Run the sample application in the download, below, and then look at the source code.