Skip to content

TSQLite3Database

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

Table of contents

About

TSQLite3Database class is SQLite3 database object wrapper.

uses
  sqlite3.database;
  
type
  TSQLite3Database = class

Create database

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

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

AFlags is set of TSQLite3DatabaseConnection flag.

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

Errors processing

To get database errors use Errors property.

property Errors : TSQL3LiteErrorsStack;

Method returns TSQLite3ErrorsStack object.

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

Run query

Run database sql query possible by using Query function.

function Query (AQuery : String; AFlags : TPrepareFlags = 
  [SQLITE_PREPARE_NORMALIZE]) : TSQLite3Query;

Method returns TSQLite3Query object.

Example
uses
  sqlite3.database;
  
var
  database : TSQLite3Database;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  database.Query('SELECT * FROM sqlite_master;', [SQLITE_PREPARE_NORMALIZE]);
  
  FreeAndNil(database);
end;

Create new table

Example
uses
  sqlite3.database;
  
var
  database : TSQLite3Database;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  database.Query('CREATE TABLE table_name (id INTEGER PRIMARY KEY, ' +
    'value TEXT NOT NULL);', [SQLITE_PREPARE_NORMALIZE]).Run;
  if database.Errors.Count > 0 then
    ;
  
  FreeAndNil(database);
end;

Select data

Example
uses
  sqlite3.database, sqlite3.result, sqlite3.result_row;
  
var
  database : TSQLite3Database;
  res : TSQLite3Result;
  row : TSQLite3ResultRow;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  res := database.Query('SELECT * FROM table_name', [SQLITE_PREPARE_NORMALIZE]).Run;
  
  for row in res do
    writeln(row.GetStringValue('name'));
    
  FreeAndNil(database);
end;

Insert data

Example
uses
  sqlite3.database, sqlite3.result, sqlite3.result_row;
  
var
  database : TSQLite3Database;
  res : TSQLite3Result;
  row : TSQLite3ResultRow;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  res := database.Query('INSERT INTO table_name (value) VALUES (?);', [SQLITE_PREPARE_NORMALIZE])
    .Bind('value', 'string').Run;
  if database.Errors.Count > 0 then
    ;
    
  FreeAndNil(database);
end;

LastInsertID

Return last database insert row id.

function LastInsertID : int64;
Example
uses
  sqlite3.database, sqlite3.result, sqlite3.result_row;
  
var
  database : TSQLite3Database;
  res : TSQLite3Result;
  row : TSQLite3ResultRow;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  res := database.Query('INSERT INTO table_name (value) VALUES (?);', [SQLITE_PREPARE_NORMALIZE])
    .Bind('value', 'string').Run;
  if database.Errors.Count > 0 then
    ;
 
  writeln(database.LastInsertID); 
    
  FreeAndNil(database);
end;

Database handle

Return database raw handle.

property Handle : psqlite3
Example
uses
  libpassqlite, sqlite3.database;
  
var
  database : TSQLite3Database;
  handle : psqlite3;
  
begin
  database := TSQLite3Database.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  handle := database.Handle;
  
  FreeAndNil(database);
end;

Raw bindings

It is possible use raw SQLite3 API functions.

Clone this wiki locally