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.