Using Stored Procedures with Result Sets

This tutorial describes how to retrieve and modify result sets obtained from stored procedures using the TIBCStoredProc component

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 insert data at runtime, add the IBC unit to the uses clause for Delphi or include the IBC.hpp header file for C++ Builder.

Besides scalar variables, a stored procedure can return a result set generated by the SELECT statement. You can insert or modify data in a result set using the dataset functionality of the TIBCStoredProc component.

This tutorial shows you how to retrieve and modify data in the dept table using the TIBCStoredProc component. The following stored procedure will be used to retrieve data:

CREATE PROCEDURE SelectDept()
BEGIN
  SELECT * FROM dept;
END;

Using the DataSet Functionality

The Insert, Append, Edit, and Post methods of the TIBCStoredProc component can be used to insert and 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
  IBCStoredProc1: TIBCStoredProc;
begin
  IBCStoredProc := TIBCStoredProc.Create(nil);
  try
    // IBCConnection1 was set up earlier
    IBCStoredProc.Connection := IBCConnection1;
    // indicates the name of the stored procedure to call 
    IBCStoredProc.StoredProcName := 'SelectDept';
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc.PrepareSQL;
    // opens the dataset
    IBCStoredProc.Open;
    // adds a new empty record at the end of the dataset
    IBCStoredProc.Append;
    // searches fields by their names and assigns new values
    IBCStoredProc.FieldByName('deptno').AsInteger := 50;
    IBCStoredProc.FieldByName('dname').AsString := 'SALES';
    IBCStoredProc.FieldByName('loc').AsString := 'NEW YORK';
    // writes the modified record
    IBCStoredProc.Post;
    // adds a new empty record in the current cursor position
    IBCStoredProc.Insert;
    IBCStoredProc.FieldByName('deptno').AsInteger := 60;
    IBCStoredProc.FieldByName('dname').AsString := 'ACCOUNTING';
    IBCStoredProc.FieldByName('loc').AsString := 'LOS ANGELES';
    IBCStoredProc.Post;
    // positions the cursor on the deptno=10 record
    IBCStoredProc.FindKey([10]);
    // enables editing of data in the dataset
    IBCStoredProc.Edit;
    IBCStoredProc.FieldByName('dname').AsString := 'RESEARCH';
    IBCStoredProc.FieldByName('loc').AsString := 'LOS ANGELES';
    IBCStoredProc.Post;
  finally
    IBCStoredProc.Free;
  end;
end;

C++Builder

TIBCStoredProc* IBCStoredProc = new TIBCStoredProc(NULL);
try {
    // IBCConnection1 was set up earlier
    IBCStoredProc->Connection = IBCConnection1;
    // indicates the name of the stored procedure to call 
    IBCStoredProc->StoredProcName = "SelectDept";
    // constructs a statement based on the Params and StoredProcName
    // properties, and assigns it to the SQL property
    IBCStoredProc->PrepareSQL();
    // opens the dataset
    IBCStoredProc->Open();
    // adds a new empty record at the end of the dataset
    IBCStoredProc->Append();
    // searches fields by their names and assigns new values
    IBCStoredProc->FieldByName("deptno")->AsInteger = 50;
    IBCStoredProc->FieldByName("dname")->AsString = "SALES";
    IBCStoredProc->FieldByName("loc")->AsString = "NEW YORK";
    // writes the modified record
    IBCStoredProc->Post();
    // adds a new empty record in the current cursor position
    IBCStoredProc->Insert();
    IBCStoredProc->FieldByName("deptno")->AsInteger = 60;
    IBCStoredProc->FieldByName("dname")->AsString = "ACCOUNTING";
    IBCStoredProc->FieldByName("loc")->AsString = "LOS ANGELES";
    IBCStoredProc->Post();
    // positions the cursor on the deptno=10 record
    IBCStoredProc->FindKey(ARRAYOFCONST((10)));
    // enables editing of data in the dataset
    IBCStoredProc->Edit();
    IBCStoredProc->FieldByName("dname")->AsString = "RESEARCH";
    IBCStoredProc->FieldByName("loc")->AsString = "LOS ANGELES";
    IBCStoredProc->Post();
}
__finally {
    IBCStoredProc->Free();
}