IBDAC

Deleting Data From Tables

This tutorial describes how to delete data from a table using the TIBCQuery and TIBCTable components.

  1. Requirements
  2. General Information
  3. Using the DataSet Functionality
  4. Building DML Statements Manually
  5. Additional Information

Requirements

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 delete data at runtime, add the IBC unit to the uses clause for Delphi or include the IBC.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 TIBCQuery, or you can use the dataset functionality (the Delete method) of the TIBCQuery and TIBCTable 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 TIBCQuery and TIBCTable components allows you to delete data without having to manually construct a DML statement — it is generated by IBDAC components internally. The code below demonstrates the use of this method:

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

Non-Parameterized DML Statements

Delphi

var
  IBCQuery1: TIBCQuery;
begin
  IBCQuery1 := TIBCQuery.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCQuery1.Connection := IBCConnection1;
    // adds a statement to delete a record
    IBCQuery1.SQL.Add('DELETE FROM dept 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 delete a record  
    IBCQuery1->SQL->Add("DELETE FROM dept WHERE deptno = 10;");
    // executes the statement 
    IBCQuery1->Execute();
  }
__finally {
    IBCQuery1->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.

© 1997-2024 Devart. All Rights Reserved. Request Support DAC Forum Provide Feedback