Friday, September 12, 2008

Working with DB2 stored procedures in Delphi 2005

Original Source click here

Level: Intermediate

Bob Swart (b.swart@chello.nl), Developer, Bob Swart Training & Consultancy

01 Dec 2005

This article -- a follow-up on the SQL DDL article entitled "Using Delphi Code to Create/Drop DB2 UDB Database Tables" (developerWorks, September 2004) and the article "Work with IBM DB2 UDB databases and SQL in Delphi for .NET" (developerWorks, September 2004) -- focuses on DB2® stored procedures, shows what they can do and how they work, and demonstrates the stored procedures in the IBM® DB2 Universal Database™ (DB2 UDB) SAMPLE database in a Delphi 2005 client application.
Show developerWorks content related to my search: DelphiShow developerWorks content related to my search: Delphi


Using ADO.NET / BDP and SAMPLE database

In this article, we will use the Borland Data Provider for ADO.NET, which is part of Borland's C#Builder, Delphi 8 for .NET, Delphi 2005, and the newly released Delphi 2006. The latter two have both Win32 and .NET personalities, but in this article, I want to focus specifically on the .NET capabilities.

For the examples in this article, we'll also use the IBM DB2 UDB SAMPLE database again. This time we won't be working with the TABLES directly or through SQL, but we'll go for another way by utilizing stored procedures written using SQL or Java™.

A closer look at stored procedures

Before we actually start, I'll explain what a stored procedure is, and why you would want to use them in the first place. A stored procedure can be seen as one or more SQL commands that already reside at the database server, prepared and ready to be executed. They often contain parameters, and calling a stored procedure consists of filling the parameters with the required values and then executing a CALL to the stored procedure. Apart from being written in SQL, a stored procedure can also be written in other languages, like C/C++ or Java. In order to write a SQL stored procedure, you need to have a C compiler on your development machine. For a Java stored procedure, you need the JDK (which is already installed with DB2 itself, so that's generally easier).

There are several advantages of using stored procedures, compared to regular SQL code. One of them is the fact that you only need to pass the parameter to the database server, and you get the result back. You don't need to pass the entire SQL statement itself. This is not only faster because of the reduced bandwidth usage, but also faster because the stored procedure can be precompiled. It's also more secure. And, finally, you can access functionality that is only available on the database server itself.



Back to top


Connecting to the SAMPLE database

The IBM DB2 SAMPLE database includes a number of sample Tables, Views, and stored procedures. For readers who haven't worked with Delphi 2005 in combination with the IBM DB2 UDB SAMPLE database before, here are the steps to connect to the IBM DB2 UDB SAMPLE database:

Start Delphi 2005 Enterprise (or higher), and go to the Data Explorer. Here, you'll see the list of available connections to databases, like DB2, SQL Server, InterBase, Sybase, and so. Select the DB2 connection, and right-click on it to modify the connection in order to connect to the IBM DB2 UDB SAMPLE database. (See Figure 1 for my settings.)

Figure 1. Data explorer: Connections editor
Data explorer: Connections editor

Note that you need to specify the name of the Database (SAMPLE) as well as the UserName and Password (obviously ******** isn't my real password). If you want the enduser of the application to specify the UserName and Password information, then you can set the LoginPrompt value to True.

Before you close this dialog, make sure to click on the Test button to verify that you've entered the correct information to connect to and use the IBM DB2 UDB SAMPLE database.

Once you can connect to the IBM DB2 UDB SAMPLE database, you can use the Data Explorer to open up the tables, views, or procedures node. The latter will list the stored procedures that are included with the SAMPLE database. However, instead of using an existing one, it may be more illustrative to create a custom stored procedure of our own and demonstrate how to call that one from Delphi 2005.



Back to top


Creating a new stored procedure

As example, I want to build a stored procedure that can be used to retrieve certain fields (like First Name, Middle Initial, Last Name, and Work Department) from all employees that have a certain job.

In order to create a new stored procedure for the IBM DB2 UDB SAMPLE database, you need to move away from Delphi for a moment, and start the DB2 Development Center. Here, you need to start a new project, which can be used to create a new stored procedure that can be deployed inside the SAMPLE database. Using the Wizard, the stored procedure will also be deployed in the database itself. The process can be seen in Figure 2 (copied from the Development Center Launchpad dialog).


Figure 2. Development Center stored procedure Workflow
Development Center stored procedure Workflow

For this new project, you need to specify the connection to the SAMPLE database, as shown in Figure 3.


Figure 3. Database connection properties
Database connection properties

You can then create a new stored procedure, which can be either using SQL or using Java, as shown in Figure 4.


Figure 4. New stored procedure - SQL or Java
New stored procedure - SQL or Java

It doesn't matter which one you select first -- we'll create one of each here. For the first one, I'll use the name DELPHI2005SAMPLE, and the second one will be called D2005SAMPLE. Both will implement the same functionality, but one will be implemented in SQL and the other in Java.

Start with a new stored procedure that will be called DELPHI2005SAMPLE, implemented in SQL.

Adding parameters

For each of the stored procedures, you need to add one parameter called JOBCODE of type CHARACTER with a length of 8 (as shown in Figure 5). This parameter will be the input for your stored procedure, which will return the FIRSTNME, MIDNIIT, LASTNAME, and WORKDEPT fields from the EMPLOYEE table, where the JOB field is equal to the the specified JOBCODE parameter value.


Figure 5. Parameter JOBCODE
Parameter JOBCODE

Note that the type and length of the JOBCODE field was just copied from the original JOB field, which is also CHARACTER(8).

Creating the stored procedure

Once you're done with specifying the stored procedure properties, the empty stored procedure will be created, as shown in Figure 6.


Figure 6. Create stored procedure ADMINISTRATOR.DELPHI2005SAMPLE
Create stored procedure ADMINISTRATOR.DELPHI2005SAMPLE

This is still only the empty SQL stored procedure, without the actual contents to implement the required functionality. In the Development Center, right-click on the stored procedure in order to start the code editor for it.

This involves writing some SQL or Java code (based on the type of stored procedure).

Editing the stored procedure: SQL

You can now edit the contents of the stored procedure, which can be implemented in SQL for the DELPHI2005SAMPLE stored procedure (and in Java for the D2005SAMPLE stored procedure). The SQL code starts with declaring a new cursor and then performs the SELECT statement on the ADMINISTRATOR.EMPLOYEE table, using the JOBCODE parameter in the WHERE clause.

The actual SQL code of the ADMINISTRATOR.DELPHI2005SAMPLE stored procedure is as follows. (See also Figure 7 for a screenshot of the code editor.)


Listing 1. SQL stored procedure
				

CREATE PROCEDURE ADMINISTRATOR.DELPHI2005SAMPLE ( IN JOBCODE CHARACTER(8) )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- JOBCODE
------------------------------------------------------------------------
P1: BEGIN
-- Declare cursor
DECLARE cursor1 CURSOR WITH RETURN FOR
SELECT FIRSTNME, MIDINIT, LASTNAME, WORKDEPT FROM ADMINISTRATOR.EMPLOYEE
WHERE JOB = JOBCODE

-- Cursor left open for client application
OPEN cursor1;
END P1


Figure 7. SQL stored procedure
SQL stored procedure

In order to build and deploy the SQL stored procedure, you need a C compiler on your system, as well as the nmake and cl command-line tools. These may not be available, in which case it might be easier to create a Java stored procedure (the required JDK is installed with the DB2 tools themselves, so these should always work).

Editing the stored procedure: Java

Create a new stored procedure, call it D2005SAMPLE, and also give it a JOBCODE string parameter. The implementation of the Java edition is as follows:


Listing 2. Java stored procedure
				

/**
* JDBC Stored Procedure ADMINISTRATOR.D2005SAMPLE
* @param JOBCODE
*/
import java.sql.*; // JDBC classes

public class D2005SAMPLE
{
public static void d2005SAMPLE ( String JOBCODE,
ResultSet[] rs1 ) throws SQLException, Exception
{
// Get connection to the database
Connection con = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement stmt = null;
String sql;
sql = "SELECT"
+ " EMPLOYEE.FIRSTNME,"
+ " EMPLOYEE.MIDINIT,"
+ " EMPLOYEE.LASTNAME,"
+ " EMPLOYEE.WORKDEPT"
+ " FROM"
+ " EMPLOYEE"
+ " WHERE"
+ " (EMPLOYEE.JOB = ?)";
stmt = con.prepareStatement(sql);
stmt.setString(1, JOBCODE);
rs1[0] = stmt.executeQuery();
if (con != null) con.close();
}
}

The screenshot of the code editor showing the Java stored procedure can be seen in Figure 8.


Figure 8. Java stored procedure
Java stored procedure

Note that both will basically perform the same functionality, but it might be easier to deploy the Java version of the stored procedure (since the Java JDK will be available already once you've installed DB2, and you don't need a C compiler as with the SQL version).

You can build and run the stored procedure from the IBM DB2 Development Center. Once you're satisfied, you can deploy the stored procedure, which will add it to the IBM DB2 UDB SAMPLE database for actual use by client applications.



Back to top


Using the stored procedure in Delphi 2005

Once the new stored procedure(s) have been added to the IBM DB2 UDB SAMPLE database, you can close the IBM DB2 Development Center and start Delphi 2005 again. Go to the Data Explorer, and connect to the IBM DB2 UDB SAMPLE database (as shown in Figure 1), to verify that the new stored procedure(s) will show up in the list of procedures.

Using Delphi 2005, you can view and test stored procedures at design-time, even directly from the Data Explorer. Make sure the Data Explorer is connected to the IBM DB2 UDB SAMPLE database again, and double-click on the ADMINISTRATOR.D2005SAMPLE stored procedure, which will display a new page with the list of the stored procedure parameters. For each parameter, you can see the BdpType and so on and enter a value. As example value, we can take a look at the possible values for the JOB field in the EMPLOYEE table, which includes ANALYST, CLERK, DESIGNER, FIELDREP, MANAGER, OPERATOR, PRES, and SALESREP. We can enter DESIGNER as sample value for the JOBCODE parameter here and can even execute the stored procedure at design-time with the little button in the uppoer left corner of the window. If you've checked the option to mark the fact that the stored procedure returns one or more cursors (resultsets), then the resulting records will be displayed, for example as shown in Figure 9.


Figure 9. Delphi 2005 stored procedure execution test
Delphi 2005 stored procedure Execution Test

As you can see in Figure 9, I've specified the value DESIGNER for the JOBCODE parameter of the ADMINISTRATOR.D2005SAMPLE stored procedure, which result - when executed - in the FIRSTNME, MIDINIT, LASTNAME, and WORKDEPT fields from the EMPLOYEE table, where the JOBCODE is equal to the specified JOBCODE parameter value DESIGNER.

Once you've tested the stored procedure(s) at design time, it's time to actually build a new Delphi for .NET application to actually use the stored procedure(s) at runtime.



Back to top


Building applications with stored procedures

Start a new WinForms Application for Delphi for .NET. Open the Data Explorer, connect to the IBM DB2 UDB SAMPLE database, and drag the ADMINISTRATOR.D2005SAMPLE stored procedure from the list of pProcedures to the WinForm Designer. The result of this action is that two new components are created in the non-visual components area of the WinForms Designer: a BdpConnection component (connected to the IBM DB3 UDB SAMPLE database) and a BdpCommand component (pointing to the ADMINISTRATOR.D2005SAMPLE stored procedure), as can be seen in Figure 10.


Figure 10. WinForms application with DB2 stored procedure
WinForms application with DB2 stored procedure

You can now test this stored procedure again at design time in the Delphi 2005 IDE.



Back to top


Testing stored procedure at design time

Click on the CommandText Editor link at the bottom of the Object Inspector to start the stored procedure dialog (see Figure 11).


Figure 11. Stored procedure design time test dialog
Stored procedure design time test dialog

As you can see in Figure 11, you can enter a sample value (like 'CLERK') for the JOBCODE parameter, and check the "Stored procedure has one or more resultset" option so you can see the result (all employees with jobcode "CLERK" as a dataset). The stored procedure dialog is a convenient way you can use to specify test values for the parameters of the stored procedure, execute the stored procedure, and view output parameter values, as well as the actual result.

Building the user interface

Once you have retested the stored procedure at design-time, it's time to build the user interface around the stored procedure. I'd like to use a ComboBox for offering the possible values for the JOBCODE, as well as a Button to call the stored procedure. The resulting records should then be displayed in a DataGrid control.

So, place a Button, ComboBox, and DataGrid control on the WinForm. Fill the Items property of the ComboBox with the possible values that the JOBCODE parameter can take (ANALYST, CLERK, DESIGNER, FIELDREP, MANAGER, OPERATOR, PRES, and SALESREP), and set the DropDownStyle property to DropDownList. We'll write some source code for the Button in a minute, but first need to add two more components.

From the Borland Data Provider category on the Tools Palette, place a BdpDataAdapter component, and from the Data Access category place a DataSet component on the WinForm. The BdpDataAdapter component has been demonstrated in the past by executing a SELECT statement associated with the SelectCommand property, filling the .NET DataSet associated with its DataSet property. This time, instead of a SELECT statement, we'll use the stored procedure references by the BdpCommand. Click on the BdpDataAdapter component to select it, and use the Object Inspector to point its SelectCommand property to the existing BdpCommand component (which contains the reference to the stored procedure). Also assign the DataSet property of the BdpDataAdapter component to the DataSet component you've just placed. There is one more thing left to fill the DataSet with data: Set the Active property of the BdpDataAdapter component to True. As a result, the DataSet will now be filled with a DataTable containing the result of the stored procedure call (with the default parameter value that you specified earlier in the stored procedure dialog).

In order to display the results, click on the DataGrid component, and point its DataSource property to the DataSet component and its DataMember property to the DataTable, which is available (called Table). This will produce the output at design-time, as shown in Figure 12.


Figure 12. Stored procedure output at design time
Stored procedure output at design time


Back to top


Re-executing the stored procedure

At runtime, we're not happy with just seeing the result of the stored procedure with the default parameter value. We may want to switch the value. This is done in the implementation of the Button's Click event.

Here, you first need to set the Active property of the BdpDataAdapter component to False, then pass the new value of the selected item from the ComboBox (called cbJob, so the value can be found in cbJob.Text), and pass it as value to the first parameter of the stored procedure named in BdpCommand. After the parameter value is assigned, you can set the Active property of the BdpDataAdapter component to True again to re-execute the stored procedure. This will re-populate the resultset in the DataSet, so the contents of the DataGrid will automatically be updated.


Listing 3. Executing stored procedure in Delphi
				

procedure TWinForm3.Button1_Click(sender: System.Object; e: System.EventArgs);
begin
BdpDataAdapter1.Active := False;
Text := cbJob.Text + 'S from IBM DB2 UDB SAMPLE database';
BdpCommand1.Parameters[0].Value := cbJob.Text;
BdpDataAdapter1.Active := True;
end;

At runtime, the application can be seen in action in Figure 13.


Figure 13. Stored procedure at runtime
Stored procedure at runtime

The use of this stored procedure required only the parameter value and the name of the stored procedure itself to be sent from the client application to the database server. The SQL statement itself (or the Java code in case of the Java stored procedure) are only available at the server side, which results in faster speed and better security.

Obviously, a stored procedure doesn't have to return a resultset. In fact, they are often used to update, delete, or insert records from a table, where again only the parameter values are passed, and not the actual SQL commands.



Back to top


Summary

In this article, we've used the IBM DB2 Development Center to build SQL and Java stored procedures and used Delphi 2005 and the Borland Data Provider to test the stored procedure at design-time, passing parameter values and viewing the result. We've also built a Delphi 2005 WinForms application calling the stored procedure at runtime, again passing parameters and viewing the result in a DataGrid.

Stored procedures are a convenient addition to the regular use of SQL, and can be beneficial in many cases.



Back to top


Next time

Next time we'll continue with an article about some of the new Delphi 2005 features for both ADO.NET and the Borland Data Provider, introducing better support for .NET remoting to turn your program into a multi-tier distributed application.




Back to top


0 Comments: