Modifying Data in Tables

This tutorial describes how to modify data in tables using the TPgQuery and TPgTable components.

1. Requirements
2. General Information
3. Using the DataSet Functionality
4. Building DML Statements Manually
   • 4.1 Parameterized DML Statements
   • 4.2 Non-Parameterized DML Statements
5. Additional Information

Requirements

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.

General Information

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:

to change it to:

Using the DataSet Functionality

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();
}

Building DML Statements Manually

DML statements can be constucted with or without parameters. The code below demonstrates both ways.

Parameterized DML Statements

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();
  }

Non-Parameterized DML Statements

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();
}

Additional Information

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.