Get Employee Project Assignments with a Stored Procedure (IBX General Tutorial)
Go Up to Tutorial: Using InterBase Express to Access a Database
In this section you build a form that uses a stored procedure to find an employee's projects. The process of creating this form is very similar to the procedure you followed in Audit Salary Changes, especially since you will use the "DmCSDemo" TDataModule again. You will add some database components, unlike the form in Audit Salary Changes.
After components are added, the project assignments form looks like this:
The top TDBGrid shows the EMPLOYEE table. The bottom TDBGrid shows the selected employee's projects.
Contents
Create the Form
Begin by adding another VCL Form to the project. In the Project Manager, right-click the project and click the menu item Add New > VCL Form.
Adjust the new form's properties:
- Set Caption to "Employee Project Assignments".
- Set Name to "FrmQueryProc".
- Change the form's Height to 370 and its Width component to 380.
- Set OldCreateOrder to true.
Save the form:
- For Delphi, save the file as Frmqrysp.pas.
To make use of the TDataModule in this form:
- For Delphi, add the following line at the beginning of the
implementation
section of Frmqrysp.pas:
uses DmCSDemo;
Add Database Components
- Add a TDataSource component to get information from the EMPLOYEE table. Set DataSet to "DmEmployee.EmployeeTable" from the drop-down menu. Note that this dataset is furnished by the "DmCSDemo" TDataModule. Change Name to "EmployeeSource".
- Add a TIBQuery to access a database table using SQL statements.
- Change Name to "EmployeeProjectsQuery".
- Set Database to "DmEmployee.EmployeeDatabase" from the drop-down menu.
- If necessary, set Transaction to "DmEmployee.IBTransaction1" from the drop-down menu.
- Set the SQL property by clicking on the ellipsis (...) button for this property, which displays the CommandText Editor dialog. Enter this line in the SQL: pane of the dialog:
Select * from Get_Emp_Proj( :EMP_NO )
This SQL code calls the stored procedure Get_Emp_Proj
, which returns a dataset containing all the projects for the given employee number.
- Add another TDataSource. Change Name to "EmployeeProjectsSource". Set DataSet to "EmployeeProjectsQuery". This TDataSource serves as an interface between the TIBQuery and the TDBGrid data-aware control you'll add in the next section.
Add Visual Components
- Add a TStatusBar to the bottom of the form. You use this to display information about the currently selected employee's projects. Check SimplePanel true.
- Add three TPanel components, positioning them as shown in the figure below. These TPanels provide a framework for positioning the visual components. Resize them to fill the form's area, except for the TStatusBar.
- Add a TDBNavigator to "Panel1" to navigate database records. Set DataSource to "DmEmployee.EmployeeSource" from the drop-down menu. This data source is furnished by the TDataModule. Under the VisibleButtons property, check all the buttons. Set ShowHint to true, so that hints for the TDBNavigator's tool buttons are displayed.
- Place a TBitBtn on the right side of "Panel1". Set the following properties:
- Place a TDBGrid on "Panel2". Resize it to fill the TPanel. Set DataSource to "EmployeeSource" from the drop-down menu.
- Place another TDBGrid on "Panel3". Resize it to fill the TPanel. Set DataSource to "EmployeeProjectsSource" from the drop-down menu. The SQL query embodied in the TIBQuery obtains the dataset containing all the projects for a given employee number. The "EmployeeProjectsSource" TDataSource makes TIBQuery's dataset available for the TDBGrid.
Add Event Handlers and Other Code
Complete the form by adding one routine and creating three event handlers. You don't need an event handler for the TBitBtn, because this button already has a command associated with clicking it.
Add a WriteMsg routine that displays a message in the TStatusBar.
Delphi
Add this procedure in the private
part of the type
section of Frmqrysp.pas:
procedure WriteMsg( strWrite : String );
You can use class completion by pressing CTRL-SHIFT-C
to create a stub for this function in the implementation
section.
Add this code for the new procedure:
procedure TFrmQueryProc.WriteMsg(StrWrite: String);
begin
StatusBar1.SimpleText := StrWrite;
end;
Next, create an event handler for the "EmployeeSource" TDataSource component. Select the "EmployeeSource" TDataSource in the Design tab, then double-click the OnDataChange event in the Object Inspector. This event handler keeps track of the currently selected employee in the top TDBGrid and updates the TStatusBar and the lower TDBGrid. Add this code for the event handler:
Delphi
procedure TFrmQueryProc.EmployeeSourceDataChange(Sender: TObject;
Field: TField);
begin
{ Execute the ProjectsQuery, which uses a query procedure }
EmployeeProjectsQuery.Close;
EmployeeProjectsQuery.Params[0].AsInteger :=
DmEmployee.EmployeeTable.FieldByName('EMP_NO').AsInteger;
EmployeeProjectsQuery.Open;
WriteMsg('Employee ' + DmEmployee.EmployeeTable.FieldByName('EMP_NO').AsInteger +
' is assigned to ' + IntToStr(EmployeeProjectsQuery.RecordCount) +
' project(s).');
end;
Finally, write two event handlers for the form itself. In the Design tab, select the form. In the Object Inspector, double-click the OnShow event to create the skeleton code for the event. Add the following code:
Delphi
procedure TFrmQueryProc.FormShow(Sender: TObject);
begin
DmEmployee.EmployeeTable.Open;
{ Allow data flow from the EmployeeTable to the local EmployeeSource. This
will allow DataChange events to execute the query procedure }
EmployeeSource.Enabled := True;
{ Explicit query preparation is not required, but gives the best possible
performance }
with EmployeeProjectsQuery do if not Active then Prepare;
end;
While the form is still selected, in the Object Inspector, double-click the OnHide event to create this event's code stub. Modify the stub as follows:
Delphi
procedure TFrmQueryProc.FormHide(Sender: TObject);
begin
{ Turn off the DataChange event for our form, since DmEmployee.EmployeeTable
is used elsewhere }
EmployeeSource.Enabled := False;
end;
This completes the form.
Display the Table Viewing Form
Modify the main form created in Create Main Form. Add a button on that form to display the employee's projects form you just created.
Delphi
Add an additional line to the uses
clause at the beginning of the implementation
section of Frmmain.pas, so the main form knows about the FrmQrySP unit you just added:
uses
FrmQrySP, { The Query Stored Procedure demo }
FrmTrigg, { The Trigger Demo }
FrmViews; { The View Demo }
Add another TButton to the Frmmain form, similar to the TButton that was added in Create Main Form. Set TButton.Caption "&Query Stored Procedure Demo". Set TButton.Name to "BtnQrySP". Then add an event handler for the new button by double-clicking that TButton in the Design tab of Frmmain form. Use the following code for the event handler:
Delphi
procedure TFrmLauncher.BtnQrySPClick(Sender: TObject);
begin
FrmQueryProc.ShowModal;
end;
Run the Application
Build and run the application. The main form displays:
Click the new "Query Stored Procedure Demo" TButton to display the new form:
The top TDBGrid displays the EMPLOYEE table from the database. The TStatusBar shows that the currently selected employee has no projects. The bottom TDBGrid displays no data other than the field's name PROJ_ID, since the employee has no projects. If you select an employee with projects, you see this:
The bottom TDBGrid shows the dataset returned by the TIBQuery's SQL command: the dataset containing all the projects for a given employee number. The TStatusBar shows the number of projects for the employee.
This concludes this project. You could expand it along the lines given in the CsDemos sample, installed with RAD Studio.