This tutorial describes how to modify data in tables using the TIBCQuery and TIBCTable components.
This tutorial assumes that you have already connected to the server (see Connecting to InterBase and Firebird), created the necessary objects on the server (see Creating Database Objects), and inserted data into tables (see Inserting Data Into Tables). To modify data at runtime, add the IBC unit to the uses
clause for Delphi or include the IBC.hpp
header file for C++ Builder.
You can modify data in a table using the Data Manipulation Language (DML), which is part of SQL. DML statements can be executed on the server by a user with respective privileges. 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 Edit
and Post
methods) of the TIBCQuery
and TIBCTable
components. This tutorial shows you how to modify data in the dept table:
10 | ACCOUNTING | NEW YORK |
to change it to:
10 | RESEARCH | LOS ANGELES |
The Edit
and Post
methods of the TIBCQuery
and TIBCTable
components allows you to modify data without having to manually construct a DML statement — it is generated by IBDAC components internally. 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;
// positions the cursor on the deptno=10 record
IBCQuery1.FindKey([10]);
// enables editing of data in the dataset
IBCQuery1.Edit;
// searches fields by their names and assigns new values
IBCQuery1.FieldByName('dname').AsString := 'RESEARCH';
IBCQuery1.FieldByName('loc').AsString := 'LOS ANGELES';
// 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();
// positions the cursor on the deptno=10 record
IBCQuery1->FindKey(ARRAYOFCONST((10)));
// enables editing of data in the dataset
IBCQuery1->Edit();
// searches fields by their names and assigns new values
IBCQuery1->FieldByName("dname")->AsString = "RESEARCH";
IBCQuery1->FieldByName("loc")->AsString = "LOS ANGELES";
// writes the modified record
IBCQuery1->Post();
}
__finally {
q->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 statement to update a record
IBCQuery1.SQL.Add('UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;');
// searches parameters by their names and assigns new values
IBCQuery1.ParamByName('deptno').AsInteger := 10;
IBCQuery1.ParamByName('dname').AsString := 'RESEARCH';
IBCQuery1.ParamByName('loc').AsString := 'LOS ANGELES';
// 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 statement to update a record
IBCQuery1->SQL->Add("UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;");
// searches parameters by their names and assigns new values
IBCQuery1->ParamByName("deptno")->AsInteger = 10;
IBCQuery1->ParamByName("dname")->AsString = "RESEARCH";
IBCQuery1->ParamByName("loc")->AsString = "LOS ANGELES";
// 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 the statement to update a record
IBCQuery1.SQL.Add('UPDATE dept SET dname = ''RESEARCH'', loc = ''LOS ANGELES'' WHERE deptno = 10;');
// 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 statement to update a record
IBCQuery1->SQL->Add("UPDATE dept SET dname = 'RESEARCH', loc = 'LOS ANGELES' WHERE deptno = 10;");
// executes the statement
IBCQuery1->Execute();
}
__finally {
IBCQuery1->Free();
}
It is also possible to use stored procedures to delete data, in which case all data manipulation logic is defined on the server. See Using Stored Procedures for more information.