Deleting Data From Tables

This tutorial describes how to delete data from a table 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 delete 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 delete data from 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 TPgQuery, or you can use the dataset functionality (the Delete method) of the TPgQuery and TPgTable components. Both ways are covered in this tutorial. This tutorial shows you how to delete a record from the dept table.

Using the DataSet Functionality

The Delete method of the TPgQuery and TPgTable components allows you to delete data without having to manually construct a DML statement — it is generated by PgDAC components internally. The code below demonstrates the use of this method:

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;
    // deletes the active record
    PgQuery1.Delete; 
  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();
    // deletes the active record
    PgQuery1->Delete();
}
__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: TPgQuery;
begin
  PgQuery1 := TPgQuery.Create(nil);
  try
    // PgConnection1 was set up earlier
    PgQuery1.Connection := PgConnection1; 
    // adds a statement to delete a record
    PgQuery1.SQL.Add('DELETE FROM dept WHERE deptno = :deptno;');
    // searches parameters by their names and assigns new values
    PgQuery1.ParamByName('deptno').AsInteger := 10;
    // executes the statement
    PgQuery1.Execute;
  finally
    PgQuery1.Free;
  end;
end;

C++Builder

TIBCQuery* PgQuery1 = new TIBCQuery(NULL);
try {
	// PgConnection1 was set up earlier
    PgQuery1->Connection = PgConnection1;
    // adds a statement to delete a record
    PgQuery1->SQL->Add("DELETE FROM dept WHERE deptno = :deptno;");
    // searches parameters by their names and assigns new values
    PgQuery1->ParamByName("deptno")->AsInteger = 10;
    // 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 a statement to delete a record
    PgQuery1.SQL.Add('DELETE FROM dept 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 delete a record  
    PgQuery1->SQL->Add("DELETE FROM dept 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.