IBDAC

Modifying Data in Tables

This tutorial describes how to modify data in tables 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 modify 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 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

Using the DataSet Functionality

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

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

Non-Parameterized DML Statements

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

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