Modifying Data

This tutorial describes how to modify data in 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 on the server (tutorial "Creating Database Objects"), and how to insert data to created tables (tutorial "Inserting Data Into Tables").

Data on server can be modified using Data Manipulation Language (DML), which is a part of SQL. DML statements can be executed on server by an account that has necessary privileges. 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 Edit and Post methods) of the TLiteQuery and TLiteTable components. We will discuss both ways. The goal of this tutorial is to modify the following record of the table dept:

to make it look as follows:

The Edit and Post methods of the TLiteQuery and TLiteTable components allow deleting data without using DML statements. DML statements are generated by LiteDAC components internally. The code below demonstrates using these methods:

[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;
   
    // to make the record with deptno=10 the current record
    LiteQuery.FindKey([10]); 

    // modify record
    LiteQuery.Edit;
    LiteQuery.FieldByName('dname').AsString := 'RESEARCH';
    LiteQuery.FieldByName('loc').AsString := 'LOS ANGELES';
    LiteQuery.Post;
  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();

    // to make the record with deptno=10 the current record
    LiteQuery->FindKey(ARRAYOFCONST((10)));

    // modify record
    LiteQuery->Edit();
    LiteQuery->FieldByName("dname")->AsString = "RESEARCH";
    LiteQuery->FieldByName("loc")->AsString = "LOS ANGELES";
    LiteQuery->Post();
  }
  __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 update record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;');

    // set parameters
    LiteQuery.ParamByName('deptno').AsInteger := 10;
    LiteQuery.ParamByName('dname').AsString := 'RESEARCH';
    LiteQuery.ParamByName('loc').AsString := 'LOS ANGELES';
  
    // execute query
    q.Execute;
  finally
    q.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 update record
    LiteQuery->SQL->Clear();
    LiteQuery->SQL->Add("UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;");

    // set parameters
    LiteQuery->ParamByName("deptno")->AsInteger = 10;
    LiteQuery->ParamByName("dname")->AsString = "RESEARCH";
    LiteQuery->ParamByName("loc")->AsString = "LOS ANGELES";

    // execute query
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}

[Delphi]

var
  LiteQuery: TLiteQuery;
begin
  LiteQuery := TLiteQuery.Create(nil);
  try
    // LiteConnecton is either TLiteConnection already set up
    LiteQuery.Connection := LiteConnection; 

    // set SQL query for update record
    LiteQuery.SQL.Clear;
    LiteQuery.SQL.Add('UPDATE dept SET dname = ''RESEARCH'', loc = ''LOS ANGELES'' WHERE deptno = 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 update record
    LiteQuery->SQL->Clear();    
    LiteQuery->SQL->Add("UPDATE dept SET dname = 'RESEARCH', loc = 'LOS ANGELES' WHERE deptno = 10;");

    // execute query
    LiteQuery->Execute();
  }
  __finally
  {
    LiteQuery->Free();
  }
}