This tutorial describes how to insert data into tables using the TLiteQuery and TLiteTable components.
This walkthrough supposes that you know how to connect to server (tutorials "Connecting To SQLite Database") and that necessary objects are already created in the database (tutorial "Creating Database Objects").
Data on server can be inserted 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 Insert, Append, and Post methods) of the TLiteQuery and TLiteTable components. We will discuss both ways.
The goal of this tutorial is to insert the following data into tables dept and emp:
Table dept
deptno | dname | loc |
---|---|---|
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
Table emp
ename | job | mgr | hiredate | sal | comm | deptno |
---|---|---|---|---|---|---|
SMITH | CLERK | 7902 | 17.12.1980 | 800 | NULL | 20 |
ALLEN | SALESMAN | 7698 | 20.02.1981 | 1600 | 300 | 30 |
WARD | SALESMAN | 7698 | 22.02.1981 | 1250 | 500 | 30 |
JONES | MANAGER | 7839 | 02.04.1981 | 2975 | NULL | 20 |
MARTIN | SALESMAN | 7698 | 28.09.1981 | 1250 | 1400 | 30 |
BLAKE | MANAGER | 7839 | 01.05.1981 | 2850 | NULL | 30 |
CLARK | MANAGER | 7839 | 09.06.1981 | 2450 | NULL | 10 |
SCOTT | ANALYST | 7566 | 13.07.1987 | 3000 | NULL | 20 |
KING | PRESIDENT | NULL | 17.11.1981 | 5000 | NULL | 10 |
TURNER | SALESMAN | 7698 | 08.09.1981 | 1500 | 0 | 30 |
ADAMS | CLERK | 7788 | 13.07.1987 | 1100 | NULL | 20 |
JAMES | CLERK | 7698 | 03.12.1981 | 950 | NULL | 30 |
FORD | ANALYST | 7566 | 03.12.1981 | 3000 | NULL | 20 |
MILLER | CLERK | 7782 | 23.01.1982 | 1300 | NULL | 10 |
Note: The empno field of the emp table is an IDENTITY(1,1) (i.e. autoincrement) field, so its value is filled automatically by the server.
INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
Performing these steps adds a new record to the dept table.
The Insert, Append, and Post methods of the TLiteQuery and TLiteTable components allow inserting data not using DML statements. DML statements are generated by LiteDAC components internally. The difference between the Append and Insert methods is that Append creates a new empty record in the end of a dataset, when Insert creates it in the position of the current record of a dataset. 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;
// append record
LiteQuery.Append;
LiteQuery.FieldByName('deptno').AsInteger := 10;
LiteQuery.FieldByName('dname').AsString := 'ACCOUNTING';
LiteQuery.FieldByName('loc').AsString := 'NEW YORK';
LiteQuery.Post;
// insert record
LiteQuery.Insert;
LiteQuery.FieldByName('deptno').AsInteger := 20;
LiteQuery.FieldByName('dname').AsString := 'RESEARCH';
LiteQuery.FieldByName('loc').AsString := 'DALLAS';
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();
// append record
LiteQuery->Append();
LiteQuery->FieldByName("deptno")->AsInteger = 10;
LiteQuery->FieldByName("dname")->AsString = "ACCOUNTING";
LiteQuery->FieldByName("loc")->AsString = "NEW YORK";
LiteQuery->Post();
// insert record
LiteQuery->Insert();
LiteQuery->FieldByName("deptno")->AsInteger = 20;
LiteQuery->FieldByName("dname")->AsString = "RESEARCH";
LiteQuery->FieldByName("loc")->AsString = "DALLAS";
LiteQuery->Post();
}
__finally
{
q->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 insert record
LiteQuery.SQL.Clear;
LiteQuery.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
// set parameters
LiteQuery.ParamByName('deptno').AsInteger := 10;
LiteQuery.ParamByName('dname').AsString := 'ACCOUNTING';
LiteQuery.ParamByName('loc').AsString := 'NEW YORK';
// 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 insert record
LiteQuery->SQL->Clear();
LiteQuery->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
// set parameters
LiteQuery->ParamByName("deptno")->AsInteger = 10;
LiteQuery->ParamByName("dname")->AsString = "ACCOUNTING";
LiteQuery->ParamByName("loc")->AsString = "NEW YORK";
// execute query
LiteQuery->Execute();
}
__finally
{
LiteQuery->Free();
}
}
[Delphi]
vvar
LiteQuery: TLiteQuery;
begin
LiteQuery := TLiteQuery.Create(nil);
try
// LiteConnection is either TLiteConnection already set up
LiteQuery.Connection := LiteConnection;
// set SQL query for insert record
LiteQuery.SQL.Clear;
LiteQuery.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
// 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; // con is either TLiteConnection already set up
// set SQL query for insert record
LiteQuery->SQL->Clear();
LiteQuery->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
// execute query
LiteQuery->Execute();
}
__finally
{
LiteQuery->Free();
}
}
Actually, there are lots of ways to insert data into tables. Any tool or component that is capable of running a SQL query, can be used to manage data. Some components are best for performing certain tasks. For example, TLiteLoader is the fastest way to insert data, TLiteScript is designed for executing series of statements one by one.