This tutorial describes how to insert data into tables using the TIBCQuery and TIBCTable components.
This tutorial assumes that you already know how to connect to the server (see Connecting to InterBase and Firebird) and that the necessary objects have already been created on the server (see Creating Database Objects). To insert data at runtime, add IBC unit to the uses
clause for Delphi or include the IBC.hpp
header file for C++ Builder.
You can insert data into a table using the Data Manipulation Language (DML), which is part of SQL. The user must have the appropriate privileges to execute DML statements on the server. There are two ways to manipulate data in a table: you can build DML statements manually and run them with a component like TIBCQuery
, or you can use the dataset functionality (the Insert
, Append
, and Post
methods) of the TIBCQuery
and TIBCTable
components. This tutorial shows you how to insert data into the dept and emp tables.
The dept
table definition:
deptno | dname | loc |
---|---|---|
10 | ACCOUNTING | NEW YORK |
20 | RESEARCH | DALLAS |
30 | SALES | CHICAGO |
40 | OPERATIONS | BOSTON |
The emp
table definition:
empno | ename | job | mgr | hiredate | sal | comm | deptno |
---|---|---|---|---|---|---|---|
7369 | SMITH | CLERK | 7902 | 17-12-1980 | 800 | NULL | 20 |
7499 | ALLEN | SALESMAN | 7698 | 20-02-1981 | 1600 | 300 | 30 |
7521 | WARD | SALESMAN | 7698 | 22-02-1981 | 1250 | 500 | 30 |
7566 | JONES | MANAGER | 7839 | 02-04-1981 | 2975 | NULL | 20 |
7654 | MARTIN | SALESMAN | 7698 | 28-09-1981 | 1250 | 1400 | 30 |
7698 | BLAKE | MANAGER | 7839 | 01-05-1981 | 2850 | NULL | 30 |
7782 | CLARK | MANAGER | 7839 | 09-06-1981 | 2450 | NULL | 10 |
7788 | SCOTT | ANALYST | 7566 | 13-07-1987 | 3000 | NULL | 20 |
7839 | KING | PRESIDENT | NULL | 17-11-1981 | 5000 | NULL | 10 |
7844 | TURNER | SALESMAN | 7698 | 08-09-1981 | 1500 | 0 | 30 |
7876 | ADAMS | CLERK | 7788 | 13-07-1987 | 1100 | NULL | 20 |
7900 | JAMES | CLERK | 7698 | 03-12-1981 | 950 | NULL | 30 |
7902 | FORD | ANALYST | 7566 | 03-12-1981 | 3000 | NULL | 20 |
7934 | MILLER | CLERK | 7782 | 23-01-1982 | 1300 | NULL | 10 |
TIBCQuery
component in the IBDAC category on the Tool Palette.TIBCQuery
object in this application, the object will be named IBCQuery1
. Note that the IBCQuery1.Connection
property is automatically set to an existing connection.IBCQuery1
object.INSERT INTO dept VALUES (10,'ACCOUNTING','NEW YORK');
Execute
button to add a new record to the dept
table.
The Insert
, Append
, and Post
methods of the TIBCQuery
and TIBCTable
components allow you to insert data without having to manually construct a DML statement — it is generated by IBDAC components internally. Insert
adds a new empty record in the current cursor position, while Append
adds a new empty record at the end of the dataset. The code below demonstrates the use of these methods:
Delphi
var
IBCQuery1: TIBCQuery;
begin
IBCQuery1 := TIBCQuery.Create(nil);
try
// IBCConnection1 was set up earlier
IBCQuery1.Connection := IBCConnection1;
// adds a statement to retrieve data
IBCQuery1.SQL.Text := 'SELECT * FROM dept';
// opens the dataset
IBCQuery1.Open;
// adds a new empty record at the end of the dataset
IBCQuery1.Append;
// searches fields by their names and assigns new values
IBCQuery1.FieldByName('deptno').AsInteger := 10;
IBCQuery1.FieldByName('dname').AsString := 'ACCOUNTING';
IBCQuery1.FieldByName('loc').AsString := 'NEW YORK';
// writes the modified record
IBCQuery1.Post;
finally
IBCQuery1.Free;
end;
end;
C++Builder
TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
// IBCConnection1 was set up earlier
IBCQuery1->Connection = IBCConnection1;
// adds a statement to retrieve data
IBCQuery1->SQL->Text = "SELECT * FROM dept";
// opens the dataset
IBCQuery1->Open();
// adds a new empty record at the end of the dataset
IBCQuery1->Append();
// searches fields by their names and assigns new values
IBCQuery1->FieldByName("deptno")->AsInteger = 10;
IBCQuery1->FieldByName("dname")->AsString = "ACCOUNTING";
IBCQuery1->FieldByName("loc")->AsString = "NEW YORK";
// writes the modified record
IBCQuery1->Post();
}
__finally {
IBCQuery1->Free();
}
DML statements can be constucted with or without parameters. The code below demonstrates both ways.
Delphi
var
IBCQuery1: TIBCQuery;
begin
IBCQuery1 := TIBCQuery.Create(nil);
try
// IBCConnection1 was set up earlier
IBCQuery1.Connection := IBCConnection1;
// adds a parameterized statement to insert data
IBCQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);');
// searches parameters by their names and assigns new values
IBCQuery1.ParamByName('deptno').AsInteger := 10;
IBCQuery1.ParamByName('dname').AsString := 'ACCOUNTING';
IBCQuery1.ParamByName('loc').AsString := 'NEW YORK';
// executes the statement
IBCQuery1.Execute;
finally
IBCQuery1.Free;
end;
end;
C++Builder
TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
// IBCConnection1 was set up earlier
IBCQuery1->Connection = IBCConnection1;
// adds a parameterized statement to insert data
IBCQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (:deptno, :dname, :loc);");
// searches parameters by their names and assigns new values
IBCQuery1->ParamByName("deptno")->AsInteger = 10;
IBCQuery1->ParamByName("dname")->AsString = "ACCOUNTING";
IBCQuery1->ParamByName("loc")->AsString = "NEW YORK";
// executes the statement
IBCQuery1->Execute();
}
__finally {
IBCQuery1->Free();
}
Delphi
var
IBCQuery1: TIBCQuery;
begin
IBCQuery1 := TIBCQuery.Create(nil);
try
// IBCConnection1 was set up earlier
IBCQuery1.Connection := IBCConnection1;
// adds a statement to insert a record
IBCQuery1.SQL.Add('INSERT INTO dept(deptno, dname, loc) VALUES (10,''ACCOUNTING'',''NEW YORK'');');
// executes the statement
IBCQuery1.Execute;
finally
IBCQuery1.Free;
end;
end;
C++Builder
TIBCQuery* IBCQuery1 = new TIBCQuery(NULL);
try {
// IBCConnection1 was set up earlier
IBCQuery1->Connection = IBCConnection1;
//adds the statement to insert a record
IBCQuery1->SQL->Add("INSERT INTO dept(deptno, dname, loc) VALUES (10,'ACCOUNTING','NEW YORK');");
// executes the statement
IBCQuery1->Execute();
}
__finally {
IBCQuery1->Free();
}
There are many ways to insert data into tables. Any tool or component that is capable of running an SQL query can be used to manage data. For example, TIBCSQL can be used to insert records one by one, while TIBCScript is designed to execute multiple DDL/DML statements as a single SQL script. TIBCLoader is the fastest way to insert data into InterBase/Firebird tables.
It is also possible to use stored procedures to insert data, in which case all data manipulation logic is defined on the server. See Using Stored Procedures for more information.