Skip to content

TSQLite3Builder

Jump to bottom
Ivan Semenkov edited this page Feb 2, 2021 · 1 revision

Table of contents

About

TSQLite3Builder class is SQLite3 database query builder wrapper.

uses
  sqlite3.builder;
  
type
  TSQLite3Builder = class

Create database

Create new or open exists database file possible by call TSQLite3Builder constructor.

constructor Create (AFilename : String; AFlags : TConnectFlags = 
  [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);

AFlags is set of TSQLite3DatabaseConnection flag.

Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  
  FreeAndNil(builder);
end;

Errors processing

To get database errors use Errors property.

property Errors : TSQL3LiteErrorsStack;

Method returns TSQLite3ErrorsStack object.

Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  if builder.Errors.Count > 0 then
    ;
  
  FreeAndNil(builder);
end;

Raw query

It is possible to run raw sql query by using RawQuery function.

function RawQuery (ASQL : String; AFlags : TPrepareFlags =
  [SQLITE_PREPARE_NORMALIZE]) : TSQLite3Query;

Method returns TSQLite3Query object.

Example
uses
  sqlite3.builder, sqlite3.query;
  
var
  builder : TSQLite3Builder;
  query : TSQLite3Query;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := builder.RawQuery('SELECT * FROM sqlite_master;', [SQLITE_PREPARE_NORMALIZE]);
  
  FreeAndNil(builder);
end;

Table

To manipulate database table use TSQLite3Table.

function Table (ATableName : String) : TSQLite3Table;
Example
uses
  sqlite3.builder, sqlite3.table;
  
var
  builder : TSQLite3Builder;
  table : TSQLite3Table;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  table := builder.Table('table_name');
  
  FreeAndNil(builder);
end;

Transaction

TransactionType

There are several database transactions type.

TTransactionType = (
DEFERRED

Means that the transaction does not actually start until the database is first accessed.

  DEFERRED,
IMMEDIATE

Cause the database connection to start a new write immediately, without waiting for a write statement.

  IMMEDIATE,
EXCLUSIVE

Prevents other database connections from reading the database while the transaction is underway.

  EXCLUSIVE
);

BeginTransaction

Start new database transaction. Details on SQLite C/C++ API page.

procedure BeginTransaction (AType : TTransactionType = DEFERRED; 
  ATransactionName : String = '');
Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  builder.BeginTransaction;
  
  builder.EndTransaction;
  
  FreeAndNil(builder);
end;

EndTransaction

End database transaction. Details on SQLite C/C++ API page.

procedure EndTransaction (ATransactionName : String = '');
Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  builder.BeginTransaction;
  
  builder.EndTransaction;
  
  FreeAndNil(builder);
end;

RollbackTransaction

Rollback database transaction. Details on SQLite C/C++ API page.

procedure RollbackTransaction (ATransactionName : String = '');
Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  builder.BeginTransaction;
  
  if builder.Errors.Count > 0 then
    builder.RollbackTransaction
  else
    builder.EndTransaction;
  
  FreeAndNil(builder);
end;

LastInsertID

Return last database insert row id.

function LastInsertID : int64;
Example
uses
  sqlite3.builder;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  writeln(builder.LastInsertID); 
  
  FreeAndNil(builder);
end;
Clone this wiki locally