Skip to content

TSQLite3Update

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

Table of contents

About

TSQLite3Update class encapsulates the SQLite3 database update of query.

uses
  sqlite3.update;
  
type
  TSQLite3Update = class

Create

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

constructor Create (AErrorsStack : PSQL3LiteErrorsStack; ADBHandle :
  ppsqlite3; ATableName : String);
Example
uses
  libpassqlite, sqlite3.errors_stack, sqlite3.connection, sqlite3.update;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  update : TSQLite3Update;
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := TSQLite3Update.Create(@errors, @handle, 'table_name');
  
  FreeAndNil(update);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Or use TSQLite3Builder object.

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

Value

Value

Set update column value.

function Value (AColumnName : String; AValue : String) : TSQLite3Update;
function Value (AColumnName : String; AValue : Integer) : TSQLite3Update;
function Value (AColumnName : String; AValue : Double) : TSQLite3Update;
function Value (AColumnName : String; AValue : TStream) : TSQLite3Update;
function Value (AColumnName : String; AValue : TMemoryBuffer) : 
  TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 23);
  
  FreeAndNil(builder);
end;

ValueNull

Set update column value to null.

function ValueNull (AColumnName : String) : TSQLite3Update;

Where

Where comparison operators

There are several comparison operators which can used in where clause.

TWhereComparisonOperator = (
COMPARISON_EQUAL

Equal comparison operator.

  COMPARISON_EQUAL,               { =   }
COMPARISON_NOT_EQUAL

Not equal comparison operator.

  COMPARISON_NOT_EQUAL,           { <>  }
COMPARISON_LESS

Less comparison operator.

  COMPARISON_LESS,                { <   }
COMPARISON_GREATER

Greater comparison operator.

  COMPARISON_GREATER,             { >   }
COMPARISON_LESS_OR_EQUAL

Less or equal comparison operator.

  COMPARISON_LESS_OR_EQUAL,       { <=  }
COMPARISON_GREATER_OR_EQUAL

Greater or equal comparison operator.

  COMPARISON_GREATER_OR_EQUAL,    { >=  }
COMPARISON_NOT

Not comparison operator.

  COMPARISON_NOT                  { IS NOT }
);

Where

If a WHERE clause is specified, the WHERE expression is evaluated for each row in the input data as a boolean expression. Only rows for which the WHERE clause expression evaluates to true are included from the dataset before continuing. Rows are excluded from the result if the WHERE clause evaluates to either false or NULL. Details on SQLite C/C++ API page.

function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : String) : TSQLite3Update;
function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Integer) : TSQLite3Update;
function Where (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Double) : TSQLite3Update;

For equal comparison operator there are present short form.

function Where (AColumnName : String; AValue : String) : TSQLite3Update;
function Where (AColumnName : String; AValue : Integer) : TSQLite3Update;
function Where (AColumnName : String; AValue : Double) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', COMPARISON_GREATER, 2);
  
  FreeAndNil(builder);
end;

WhereNull

It is a short form of where clause with value equal NULL .

function WhereNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .WhereNull('value');
  
  FreeAndNil(builder);
end;

WhereNotNull

It is a short form of where clause with value not equal NULL .

function WhereNotNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .WhereNotNull('value');
  
  FreeAndNil(builder);
end;

AndWhere

Adds AND WHERE clause to query.

 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : String) : TSQLite3Update;
 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : Integer) : TSQLite3Update;
 function AndWhere (AColumnName : String; AComparison : 
   TWhereComparisonOperator; AValue : Double) : TSQLite3Update;

For equal comparison operator there are present short form.

function AndWhere (AColumnName : String; AValue : String) : TSQLite3Update;
function AndWhere (AColumnName : String; AValue : Integer) : TSQLite3Update;
function AndWhere (AColumnName : String; AValue : Double) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .AndWhere('id', COMPARISON_GREATER, 5);
  
  FreeAndNil(builder);
end;

AndWhereNull

It is a short form of where clause with value equal NULL .

function AndWhereNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .AndWhereNull('value');
  
  FreeAndNil(builder);
end;

AndWhereNotNull

It is a short form of where clause with value not equal NULL .

function AndWhereNotNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .AndWhereNotNull('value');
  
  FreeAndNil(builder);
end;

OrWhere

Adds OR WHERE clause to query.

function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : String) : TSQLite3Update;
function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Integer) : TSQLite3Update;
function OrWhere (AColumnName : String; AComparison : 
  TWhereComparisonOperator; AValue : Double) : TSQLite3Update;

For equal comparison operator there are present short form.

function OrWhere (AColumnName : String; AValue : String) : TSQLite3Update;
function OrWhere (AColumnName : String; AValue : Integer) : TSQLite3Update;
function OrWhere (AColumnName : String; AValue : Double) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .OrWhere('id', COMPARISON_GREATER, 54);
  
  FreeAndNil(builder);
end;

OrWhereNull

It is a short form of where clause with value equal NULL .

function OrWhereNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .OrWhereNull('value');
  
  FreeAndNil(builder);
end;

OrWhereNotNull

It is a short form of where clause with value not equal NULL .

function OrWhereNotNull (AColumnName : String) : TSQLite3Update;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  update : TSQLite3Update;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  update := builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .OrWhereNotNull('value');
  
  FreeAndNil(builder);
end;

Get

Run query and return number of updated columns.

function Get : Integer;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.update;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  builder.Table('table_name').Update
    .Value('value', 11)
    .Where('id', 2)
    .OrWhereNotNull('value')
    .Get;
  
  FreeAndNil(builder);
end;
Clone this wiki locally