Skip to content

TSQLite3Insert

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

Table of contents

About

TSQLite3Insert class encapsulates the SQLite3 database insert query.

uses
  sqlite3.insert;
  
type
  TSQLite3Insert = class

Create

A new insert 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.insert;
  
var
  errors : TSQLite3ErrorsStack;
  handle : psqlite3;
  connection : TSQLite3DatabaseConnection;
  insert : TSQLite3Insert
  
begin
  errors := TSQLite3ErrorsStack.Create;
  connection := TSQLite3DatabaseConnection.Create(@errors, @handle, 'database', 
    [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := SQLite3Insert.Create(@errors, @handle, 'table_name');
  
  FreeAndNil(insert);
  FreeAndNil(connection);
  FreeAndNil(errors); 
end;

Or use TSQLite3Builder object.

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

Value

There are two ways to insert values into table.

Insert single row values

Value

Set the value of the insertion row column.

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

Set the null value of the insertion row column.

function ValueNull (AColumnName : String) : TSQLite3Insert;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  insert : TSQLite3Insert;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := builder.Table('table_name').Insert
    .ValueNull('value');
  
  FreeAndNil(builder);
end;

Insert multiple rows

It is very important set a correct value types. It is raising TException('Mistmatch column type.') if column type is not equal value type.

Column

Set multiple insert column name.

function Column (AColumnName : String; AColumnType : TDataType) : 
  TSQLite3Insert;

AColumnType is one of fundamental database column data types.

TDataType = (
  SQLITE_INTEGER,
  SQLITE_FLOAT,
  SQLITE_BLOB,
  SQLITE_TEXT
);
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  insert : TSQLite3Insert;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := builder.Table('table_name').Insert
    .Column('val_1', SQLITE_INTEGER)
    .Column('val_2', SQLITE_TEXT);
  
  FreeAndNil(builder);
end;
Row

Start new insert row.

function Row : TSQLite3Insert;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  insert : TSQLite3Insert;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := builder.Table('table_name').Insert
    .Column('val_1', SQLITE_INTEGER)
    .Column('val_2', SQLITE_TEXT)
    .Row;
  
  FreeAndNil(builder);
end;
Value

Add values to insert row.

function Value (AValue : Integer) : TSQLite3Insert;
function Value (AValue : Double) : TSQLite3Insert;
function Value (AValue : String) : TSQLite3Insert;
function Value (AValue : TStream) : TSQLite3Insert;
function Value (AValue : TMemoryBuffer) : TSQLite3Insert;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  insert : TSQLite3Insert;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := builder.Table('table_name').Insert
    .Column('val_1', SQLITE_INTEGER)
    .Column('val_2', SQLITE_TEXT)
    .Row 
      .Value(12)
      .Value('some text')
    .Row
      .Value(-3)
      .Value('another text');
  
  FreeAndNil(builder);
end;
ValueNull

Add null value to insert row.

function ValueNull : TSQLite3Insert;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  insert : TSQLite3Insert;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  insert := builder.Table('table_name').Insert
    .Column('val_1', SQLITE_INTEGER)
    .Column('val_2', SQLITE_TEXT)
    .Row 
      .Value(12)
      .Value('some text')
    .Row
      .Value(-3)
      .ValueNull;
  
  FreeAndNil(builder);
end;

Get

Run query and return number of inserted columns.

function Get : Integer;
Example
uses
  sqlite3.builder, sqlite3.table, sqlite3.insert;
  
var
  builder : TSQLite3Builder;
  
begin
  builder := TSQLite3Builder.Create('database', [SQLITE_OPEN_CREATE, SQLITE_OPEN_READWRITE]);
  builder.Table('table_name').Insert
    .Column('val_1', SQLITE_INTEGER)
    .Column('val_2', SQLITE_TEXT)
    .Row 
      .Value(12)
      .Value('some text')
    .Row
      .Value(-3)
      .ValueNull
    .Get;
  
  FreeAndNil(builder);
end;
Clone this wiki locally