Create DB2 Stored Procedures in VS .NET: Part III

Build and test your own DB2 stored procedures with the DB2 .NET Enablement tool.

DataStream

LANGUAGES: VB

ASP.NET VERSIONS: 1.1

 

Create DB2 Stored Procedures in VS .NET: Part III

Build and test your own DB2 stored procedures with the DB2 .NET Enablement tool.

 

By Wayne S. Freeze

 

The DB2 .NET Enablement product gives you the tools you need to perform many common database programming tasks without leaving Visual Studio .NET. One of these tools lets you create and test stored procedures. Let's take a look at how it's done.

 

Create a DB2 Stored Procedure

Included with the DB2 .NET Enablement product is an add-in to Visual Studio .NET that introduces a new type of project, named a DB2 Database Project. The DB2 Database Project has collection files containing SQL scripts, which perform various tasks. In addition to creating scripts that build a stored procedure, you also can create scripts that create tables or views, insert information into the database, or manage security.

 

When you create a DB2 Database Project, you are prompted to enter connection information to the DB2 database. This information lets you view database structures and browse database contents, as well as create and run stored procedures.

 

Once you have created a DB2 Database Project, you can add a new stored procedure by right-clicking on Procedures and choosing Add | Add New Item from the pop-up menu. IBM supplies two templates: One gives you a template containing the statements needed to build a stored procedure, the other launches a wizard that helps you create the statements for your stored procedure.

 

Use the Stored Procedure Wizard

Using the wizard is a good idea if you are learning DB2. All you need to know is how to code the SQL statement you want to run. Figure 1 shows the information collected by the Stored Procedure Wizard for each statement in the stored procedure.

 


Figure 1. The Stored Procedure Wizard prompts you to enter each SQL statement in the stored procedure.

 

Each statement in the stored procedure has a name listed on the left side of the window. Details about the selected statement are shown in the Statement Detail section on the right side of the window. In this example, a simple Select statement was entered that retrieves selected columns from the Customers table.

 

Pressing the Finish button creates the script shown in Figure 2. The script begins by dropping the stored procedure if it exists already. Then it uses the Create Procedure statement to create the new stored procedure. Next it adds a comment to the stored procedure collected by the wizard. Finally, a Grant statement is included to let the public execute the stored procedure.

 

--

@

-- Drop the stored procedure if one already exists

DROP SPECIFIC PROCEDURE ADMINISTRATOR.GETCUSTOMERS

@

--

 

-- Create stored procedure

CREATE PROCEDURE ADMINISTRATOR.GETCUSTOMERS()

   SPECIFIC ADMINISTRATOR.GETCUSTOMERS

   DYNAMIC RESULT SETS 1

--------------------------------------------------------------------------

-- This routine retrieves all of the customers from the Customers table.

--------------------------------------------------------------------------

 

P1: BEGIN

 

-- Declare variables

 

 

-- Declare cursors

DECLARE Step1 CURSOR WITH RETURN FOR

   Select CustomerId, Name, Street, City, State, Phone, EMailAddress

      From Customers;

 

--

 

-- Cursor left open for client application.

OPEN Step1;

 

--

 

END P1

@

@

COMMENT ON SPECIFIC PROCEDURE ADMINISTRATOR.GETCUSTOMERS IS

   'This routine retrieves all of the customers from the Customers table.'

@

 

@

-- Grant access priviliges to stored procedure

GRANT EXECUTE ON SPECIFIC PROCEDURE ADMINISTRATOR.GETCUSTOMERS TO PUBLIC

@

Figure 2. The wizard generates a complete SQL script that drops the existing stored procedure, then creates the replacement stored procedure.

 

Add Parameters

The Stored Procedure Wizard makes dealing with parameters easy. When you enter your SQL statement, you specify parameters formatted like this: .:, or as shown in this example:

 

Select CustomerId, Name, Street, City, State, Phone, EMailAddress

   From Customers

   Where CustomerId = GetCustomers.:CustomerId

 

Each parameter is detected by the Stored Procedure Wizard automatically, and you can edit the information associated with the parameter (see Figure 3). This information is included automatically with the procedure's definition when the script is generated.

 


Figure 3. You can use the Stored Procedure Wizard to edit the information associated with each parameter.

 

Compile and Test the Stored Procedure

Once the stored procedure is finished, you can compile it by right-clicking on the name of the stored procedure and choosing Compile from the pop-up menu in the Solution Explorer window. The results of the compile are displayed in the Output pane at the bottom of the Visual Studio .NET IDE window.

 

You can test the stored procedure though the IBM Explorer window (View | IBM Explorer). This window displays information about the tables, views, stored procedures, and functions stored in the database. You can run the stored procedure by expanding the Stored Procedure icon, right-clicking on the name of the stored procedure, and choosing Run Stored Procedure from the pop-up menu. The results are displayed in Visual Studio .NET (see Figure 4).

 


Figure 4. Running the GetCustomers stored procedure returns the results as a table in the Visual Studio .NET IDE.

 

Wayne S. Freeze is a full-time computer book author with more than a dozen titles to his credit, including Windows Game Programming with Visual Basic and DirectX (Que) and Unlocking OLAP with SQL Server and Excel 2000 (Hungry Minds). He has more than 25 years of experience using all types of computers, from small, embedded microprocessor control systems to large-scale IBM mainframes. Freeze has a master's degree in management information systems as well as degrees in computer science and engineering. You can visit his Web site at http://www.JustPC.com and send e-mail to mailto:[email protected]. He loves reading e-mail from his readers, whose ideas, questions, and insights often provide inspiration for future books and articles.

 

 

 

 

Hide comments

Comments

  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.
Publish