A Delphi SQLite Wrapper

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.MakeTable method 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.
- ```
FieldName[Index:Integer]: WideString
```
  The name of the field in the Index'th column of the recordset.
- ```
FieldByIndex[Index:Integer]: Variant
```
  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.MakeTable method, 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 FieldAsBlob returns 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.
- The MoveFirst, MoveLast, Next and Previous methods 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 MakeTable.

Run the sample application in the download, below, and then look at the source code. The download contains all source code except for the SQLite wrapper itself which is available at just $9.99. Download

▼ Jump To...

◄ ►