Putting Stored Procedures td Work

We will make use of the Local InterBase! Server that ships with some versions of Delphi to explore the functioning of the StoredProd component and stored procedures in general. The project that we will build is called the Project Manager, and it works with the sample InterBase files in the IBLocal darabase. If you have not installed the LIBS, this database will not appear in the Database Explorer. Remember that the stored procedures are a part of the metadata of the database, so all of the information pertaining to the structure of the procedures can be viewed through the Database Explorer.

The running application is shown in Figure 5.11.

Figures 5. I I

The Project Manager maim screen

Figures 5. I I

The Project Manager maim screen

Two different operations are being handled by stored procedures running on the InterBase server. When the Programmers datased is scrolled from row to row, a procedure is used to extract the programmer's current project assignments and display them in the Assignments grid. Secondly, a procedure is used to insert new rows into FA1PI OYEH j PROJECT To begin building this project, start with a new application.

Caption the form Project Manager and save the project. Name the unit file stprocu2.pas| and the project stproc2.dpr.

Add three DBGridl components to the form: DBGridl in the top left, DBGrid2 in the top right, and DBGrid3| in the lower left-hand corner. So that the following instructions will have some measure of clarity, add the labeling as shown. We will refer to the grids by their closest label in the instructions.

Chapter] S-Data Access with Delphi □ 135!

3. On the Programmers grid place a DataSourcd and a Query component. Set the DBGrid DataSourcd property to DataSourcelJ On DataSourcelJ set the DataSetl property to Queryl. Select the Query component and set the Database Name property to IBLocal. (Rem em-I ber, this will only show if LIBS has been installed.) Double-click on the SQL property to open the String List Editor and enter the following SQL statement:

SELECT erfip riO, first-name, last name FROM employee

Click OK to save the statement to the control.

4. Be sure that the LIBS is running and double-click the Active property on the Query 1| component to execute the SOL statement. Enter the password master key: If it is correctly form&ted, the statement will be processed and the grid will fill with the result set. Your project should be similar to Figure 5.12 at this point.

Figure 5.12

Modifyrng the Project Manager project iy Pi nie e I Ila

: PtOOrJtiWBfs-"

  • Curteni Projects iy Pi nie e I Ila
  • PtOOrJtiWBfs-"


jntitT HAUL

|'_'.CT_lt»J<i 1 - I


2 Rot»!

Nelson C=V _1


4 Biuce

Young JLi!


Lsmbei! JJlj


8 Letti

Jolvison ■


9 Phd

Modifyrng the Project Manager project

5. To the Current Projects grid, add both a Data Sou reel and Query component. The grid's DataSourcd property should be set to DataSource2. The DataSet property of DataSource should be pointed to <}uery2j The Quay csimnipsanoifs IDatabaseName pnoperty will be IBLocal and the following SQL statement will be entered for the SQL property:

SELECT projjdj proj-name FROM project

6. Activate this query to fill the dataser with the results from the SQL statement.

Part ll-The Delphi Database Tools

7. Finally, add the DataSource and Query components to the Assignments grid. Set the appropriate DataSource and DataSet properties to link these three components together. In the SQL property, the SQL statement that we are going to enter will execute a stored procedure. We are using a Query component because the GET-EMP-PROJ stored procedure returns a result set. This procedure has an input parameter through which we will pass the employee ID number for which to display the current projects. Enter the SQL statement as follows:

8. This query will not be activated at design time. The application is going to execute this query each time a new employee is selected. To do this we will utilize the OnDataChange event of the DataSourcel control. Add the following lines to this event handler procedure:

procedure TForm 1.DataSource l.DataChange (Sender: TObject; Field: begin


  1. Prepare;
  2. ParamByName('Emp_Num').Aslnteger := Query1. FieldByName('EMP_N01).As Integer;
  3. 0pen; end;
  4. Add a BitBtn control and set the Kind property to bkClose, Compile and save the application. Execute the application and scroll through the Programmers list. As the employee ID changes in the current row, the GET EMP_ PROJ procedure is fired and returns all of the projects assigned to that specific ID number.
  5. Add a Button component and caption it Add Project. The user will click on this button to assign new projects to a programmer. For this we are going to use another stored procedure from the IBLocal database, ADD_EMP_PROJ. This procedure does not return a value, so rather than using a Query control we are going to use the StoredProc component. Place a StoredProc control on the form and set the DatabaseName property to IBLocal. Since the procedures are a part of the metadata of the database, clicking on the StoredProcName property will display a list of all of the registered procedures. Select GET-EMP-PROJ. Double-click on the Params property. The parameters for the selected procedure are displayed and their properties surfaced for editing. By clicking on each parameter you can determine what you are working with. Don't make any changes. Close the editor to return to the Object Inspector for StoredProcl.

The code for adding a project record is going to be placed in the button's OnClick event handler. GET__EMP PROJ has two parameters that are going to be gathered from the EMPLOYEE and PROJECT tables. Enter the following code for the event handler:

procedure TForml .ButtonlClick (Sender: TObject); begin

  1. Prepare;
  2. ParamByName('PR0J_ID').AsString := Query2.Fi eldByName('PR0J_ID').AsString;
  3. ParamByName('EMP_N0').AsInteger := Query1.Fi eldByName(1EMPJJO1).As Integer;
  4. ExecProc; StoredProcl.Unprepare; end;

Compile the program and save it. Execute the application and select a programmer and a project. Click the Add Project button and the ADD_EMP_PROJ procedure will take care of inserting the new row. When you select the employee record again, the new project will have been added to the Assignments list.

Project Management Made Easy

Project Management Made Easy

What you need to know about… Project Management Made Easy! Project management consists of more than just a large building project and can encompass small projects as well. No matter what the size of your project, you need to have some sort of project management. How you manage your project has everything to do with its outcome.

Get My Free Ebook

Post a comment