Skip to content

TSQLite3Query

Jump to bottom
Ivan Semenkov edited this page Jan 31, 2021 · 1 revision

Table of contents

About

TSQLite3Query class encapsulates the SQLite3 query.

uses
  sqlite3.query;
  
type
  TSQLite3Query = class

Create

A new database query can be created by call its constructor.

constructor Create (AErrorsStack : PSQL3LiteErrorsStack; ADBHandle : 
  ppsqlite3; AQuery : String; AFlags : TPrepareFlags);

AErrorsStack is a pointer to SQLite3ErrorsStack class.

ADBHandle is a pointer to SQLite3 database handle.

AFlags is a additional database prepare flags.

Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.query;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  query : TSQLite3Query;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := TSQLite3Query.Create(@errors, @handle, 'SELECT * FROM sqlite_master;',
    [SQLITE_PREPARE_NORMALIZE]);
    
  FreeAndNil(query);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Prepare flags

Option that is used for special purposes.

TPrepareFlags = set of TPrepareFlag;
TPrepareFlag = (
SQLITE_PREPARE_PERSISTENT

The SQLITE_PREPARE_PERSISTENT flag is a hint to the query planner that the prepared statement will be retained for a long time and probably reused many times.

SQLITE_PREPARE_PERSISTENT,
SQLITE_PREPARE_NORMALIZE

The SQLITE_PREPARE_NORMALIZE flag is a no-op. This flag used to be required for any prepared statement that wanted to use the sqlite3_normalized_sql() interface. However, the sqlite3_normalized_sql() interface is now available to all prepared statements, regardless of whether or not they use this flag.

SQLITE_PREPARE_NORMALIZE,
SQLITE_PREPARE_NO_VTAB

The SQLITE_PREPARE_NO_VTAB flag causes the SQL compiler to return an error (error code SQLITE_ERROR) if the statement uses any virtual tables.

SQLITE_PREPARE_NO_VTAB
);

Bind

Binding values to prepared query.

function Bind(AIndex : Integer) : TSQLite3Query;
function Bind(AIndex : Integer; AValue : Double)  : TSQLite3Query;
function Bind(AIndex : Integer; AValue : Integer) : TSQLite3Query;
function Bind(AIndex : Integer; AValue : Int64)   : TSQLite3Query;
function Bind(AIndex : Integer; AValue : String)  : TSQLite3Query;
function BindBlob(AIndex : Integer; AValue : Pointer; ASize : Int64) :
  TSQLite3Query;
function BindBlobZero(AIndex : Integer; ASize : Int64) : TSQLite3Query;
Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.query;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  query : TSQLite3Query;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := TSQLite3Query.Create(@errors, @handle, 
    'INSERT INTO table_name (value) VALUES (?);', [SQLITE_PREPARE_NORMALIZE]);
  query.Bind(0, 'string value');  
    
  FreeAndNil(query);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

ClearBindings

Reset all bindings on a prepared query.

procedure ClearBindings;
Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.query;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  query : TSQLite3Query;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := TSQLite3Query.Create(@errors, @handle, 
    'INSERT INTO table_name (value) VALUES (?);', [SQLITE_PREPARE_NORMALIZE]);
  query.ClearBindings;
    
  FreeAndNil(query);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Run

Run the SQL query.

function Run : TSQLite3Result;

Method return TSQLite3Result object.

Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.query,
  sqlite3.result;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  query : TSQLite3Query;
  result : TSQLite3Result;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := TSQLite3Query.Create(@errors, @handle, 'SELECT * FROM sqlite_master;',
    [SQLITE_PREPARE_NORMALIZE]);
  result := query.Run;
    
  FreeAndNil(query);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Reset

Reset a prepared query. Reset a prepared query object back to its initial state, ready to be re-executed.

procedure Reset;
Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.query;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  query : TSQLite3Query;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  query := TSQLite3Query.Create(@errors, @handle, 'SELECT * FROM sqlite_master;',
    [SQLITE_PREPARE_NORMALIZE]);
  query.Reset;
    
  FreeAndNil(query);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;
Clone this wiki locally