This tutorial describes how to retrieve and modify result sets obtained from stored procedures using the TIBCStoredProc component
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;
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();
}