This tutorial describes how to modify data in tables using the TPgQuery and TPgTable components.
This tutorial assumes that you have already connected to the server (see Connecting to PostgreSQL), 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 PgAccess unit to the uses
clause for Delphi or include the PgAccess.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 TPgQuery
, or you can use the dataset functionality (the Edit
and Post
methods) of the TPgQuery
and TPgTable
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 TPgQuery
and TPgTable
components allows you to modify data without having to manually construct a DML statement — it is generated by PgDAC components internally. The code below demonstrates the use of these methods:
Delphi
var
PgQuery1: TPgQuery;
begin
PgQuery1 := TPgQuery.Create(nil);
try
// PgConnection1 was set up earlier
PgQuery1.Connection := PgConnection1;
// adds a statement to retrieve data
PgQuery1.SQL.Text := 'SELECT * FROM dept';
// opens the dataset
PgQuery1.Open;
// positions the cursor on the deptno=10 record
PgQuery1.FindKey([10]);
// enables editing of data in the dataset
PgQuery1.Edit;
// searches fields by their names and assigns new values
PgQuery1.FieldByName('dname').AsString := 'RESEARCH';
PgQuery1.FieldByName('loc').AsString := 'LOS ANGELES';
// writes the modified record
PgQuery1.Post;
finally
PgQuery1.Free;
end;
end;
C++Builder
TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
// PgConnection1 was set up earlier
PgQuery1->Connection = PgConnection1;
// adds a statement to retrieve data
PgQuery1->SQL->Text = "SELECT * FROM dept";
// opens the dataset
PgQuery1->Open();
// positions the cursor on the deptno=10 record
PgQuery1->FindKey(ARRAYOFCONST((10)));
// enables editing of data in the dataset
PgQuery1->Edit();
// searches fields by their names and assigns new values
PgQuery1->FieldByName("dname")->AsString = "RESEARCH";
PgQuery1->FieldByName("loc")->AsString = "LOS ANGELES";
// writes the modified record
PgQuery1->Post();
}
__finally {
PgQuery1->Free();
}
DML statements can be constucted with or without parameters. The code below demonstrates both ways.
Delphi
var
PgQuery1: TPQuery;
begin
PgQuery1 := TPgQuery.Create(nil);
try
// PgConnection1 was set up earlier
PgQuery1.Connection := PgConnection1;
// adds a statement to update a record
PgQuery1.SQL.Add('UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;');
// searches parameters by their names and assigns new values
PgQuery1.ParamByName('deptno').AsInteger := 10;
PgQuery1.ParamByName('dname').AsString := 'RESEARCH';
PgQuery1.ParamByName('loc').AsString := 'LOS ANGELES';
// executes the statement
PgQuery1.Execute;
finally
PgQuery1.Free;
end;
end;
C++Builder
TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
// PgConnection1 was set up earlier
PgQuery1->Connection = PgConnection1;
// adds a statement to update a record
PgQuery1->SQL->Add("UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = :deptno;");
// searches parameters by their names and assigns new values
PgQuery1->ParamByName("deptno")->AsInteger = 10;
PgQuery1->ParamByName("dname")->AsString = "RESEARCH";
PgQuery1->ParamByName("loc")->AsString = "LOS ANGELES";
// executes the statement
PgQuery1->Execute();
}
__finally {
PgQuery1->Free();
}
Delphi
var
PgQuery1: TPgQuery;
begin
PgQuery1 := TPgQuery.Create(nil);
try
// PgConnection1 was set up earlier
PgQuery1.Connection := PgConnection1;
// adds the statement to update a record
PgQuery1.SQL.Add('UPDATE dept SET dname = ''RESEARCH'', loc = ''LOS ANGELES'' WHERE deptno = 10;');
// executes the statement
PgQuery1.Execute;
finally
PgQuery1.Free;
end;
end;
C++Builder
TPgQuery* PgQuery1 = new TPgQuery(NULL);
try {
// PgConnection1 was set up earlier
PgQuery1->Connection = PgConnection1;
// adds a statement to update a record
PgQuery1->SQL->Add("UPDATE dept SET dname = 'RESEARCH', loc = 'LOS ANGELES' WHERE deptno = 10;");
// executes the statement
PgQuery1->Execute();
}
__finally {
PgQuery1->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.