This tutorial describes how to delete data from tables using the TLiteQuery and TLiteTable components.
This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQLite Database"), how to create necessary objects in a database (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").
Data in the database can be deleted using Data Manipulation Language (DML), which is a part of SQL. There are two ways to manipulate a database. You can build DML statements manually and run them within some component like TLiteQuery. Another way is to use the dataset functionality (the Delete method) of the TLiteQuery and TLiteTable components. We will discuss both ways. The goal of this tutorial is to delete a record in the table dept.
The Delete method of the TLiteQuery and TLiteTable components allows deleting data without using DML statements. DML statements are generated by LiteDAC components internally. The code below demonstrates using this method:
[Delphi]
var
LiteQuery: TLiteQuery;
begin
LiteQuery := TLiteQuery.Create(nil);
try
// LiteConnection is either TLiteConnection already set up
LiteQuery.Connection := LiteConnection;
// retrieve data
LiteQuery.SQL.Text := 'SELECT * FROM dept';
LiteQuery.Open;
// delete the current record
LiteQuery.Delete;
finally
LiteQuery.Free;
end;
end;
[C++Builder]
{
TLiteQuery* LiteQuery = new TLiteQuery(NULL);
try
{
// LiteConnection is either TLiteConnection already set up
LiteQuery->Connection = LiteConnection;
// retrieve data
LiteQuery->SQL->Text = "SELECT * FROM dept";
LiteQuery->Open();
// delete the current record
LiteQuery->Delete();
}
__finally
{
LiteQuery->Free();
}
}
DML Statements can contain plain text and text with parameters. This section describes both ways.
[Delphi]
var
LiteQuery: TLiteQuery;
begin
LiteQuery := TLiteQuery.Create(nil);
try
// LiteConnection is either TLiteConnection already set up
LiteQuery.Connection := LiteConnection;
// set SQL query for delete record
LiteQuery.SQL.Clear;
LiteQuery.SQL.Add('DELETE FROM dept WHERE deptno = :deptno;');
// set parameters
LiteQuery.ParamByName('deptno').AsInteger := 10;
// execute query
LiteQuery.Execute;
finally
LiteQuery.Free;
end;
end;
[C++Builder]
{
TLiteQuery* LiteQuery = new TLiteQuery(NULL);
try
{
// LiteConnection is either TLiteConnection already set up
LiteQuery->Connection = LiteConnection;
// set SQL query for delete record
LiteQuery->SQL->Clear();
LiteQuery->SQL->Add("DELETE FROM dept WHERE deptno = :deptno;");
// set parameters
LiteQuery->ParamByName("deptno")->AsInteger = 10;
// execute query
LiteQuery->Execute();
}
__finally
{
LiteQuery->Free();
}
}
[Delphi]
var
LiteQuery: TLiteQuery;
begin
LiteQuery := TLiteQuery.Create(nil);
try
// LiteConnection is either TLiteConnection already set up
LiteQuery.Connection := LiteConnection;
// set SQL query for delete record
LiteQuery.SQL.Clear;
LiteQuery.SQL.Add('DELETE FROM dept WHERE deptno = 10;');
// execute query
LiteQuery.Execute;
finally
LiteQuery.Free;
end;
end;
[C++Builder]
{
TLiteQuery* LiteQuery = new TLiteQuery(NULL);
try
{
// con is either TLiteConnection already set up
LiteQuery->Connection = con;
// set SQL query for delete record
LiteQuery->SQL->Clear();
LiteQuery->SQL->Add("DELETE FROM dept WHERE deptno = 10;");
// execute query
LiteQuery->Execute();
}
__finally
{
LiteQuery->Free();
}
}