Since the middle of 2006, Ive been writing about all
the great integration features in IBM DB2 9 for the Microsoft Visual Studio 2005
integrated development environment (IDE) and the accompanying ADO.NET 2.0 API.
In an article called Building
Tables with the DB2 Table Designer and Visual Studio 2005 I showed you
how to build a table using a feature unique to DB2 9 called a designer.
As you may recall, I mentioned that there are designers to build and create all
sorts of schema objects such as views and stored procedures, as well as
non-schema objects such as scripts and more. In this article, I want to show
you how to create a stored procedure using the SQL/PL Stored Procedure designer
and show you how you can leverage that stored procedure in a simple WinForm application.
To perform the steps in this
article, ensure that youve created the SAMPLE database by entering the db2sampl
command from your operating systems command prompt. In addition, ensure that
youve added a connection to this database in the Server Explorer view.
Building an SQL/PL stored procedure using a designer
To build an SQL/PL stored procedure that selects the
employees of a specific department based on using the DB2 9 SQL/PL Stored
Procedure designer in Visual Studio 2005, perform the following steps:
the entry for the SAMPLE database in the Server Explorer, select the Procedures
folder, and then Add New SQL Procedure with Designer
Procedure designer opens. Call this procedure SELECTSTAFFBYDEPT in the Procedure
identification section of this designer. Optionally, specify the schema
name under which this routine will be created and a comment describing it.
Since I dont show you the rich
debug integration between Visual Studio 2005 and the IBM add-ins for Visual
Studio 2005, you can leave the Debug mode setting as is, or set it to Allow
if you want to use the integrated debugging capabilities of this IDE for
the Procedure Parameters window, add a parameter to this routine by clicking Add
the Name field and rename this parameter to DEPTNUMB.
Import Parameters ()
to discover the data type of the parameter you want to pass to the routine that
retrieves employees within the STAFF table based on the DEPT column.
Import window opens. Optionally, use Schema name to filter the tables in
this window, find the STAFF table, select the DEPT column, move
it to the Selected list by clicking Add column to selected list (),
and click OK.
The IBM add-ins for Visual Studio
2005 give you some unique features that enhance the rapid application
development (RAD) experience of the Visual Studio 2005 IDE. The ability to discover
parameter data types is one of them. This prevents you from choosing a wrong
data type for the input parameter to be used by the stored procedure you are
building, which in turn reduces potential coding errors.
There are a host of other unique DB2
9 RAD features for Visual Studio 2005, some of which I covered in previous
articles. These features include column discovery for table creation,
IntelliSense hookup to the underlying DB2 schema (which Ill show you later in
this article), a specific IBM Output Message Pane that details specific DB2 data
server activity and error messages, and more.
The Procedure Parameters portion
of this designer should now look similar to this:
the DEPTNUMB parameter by selecting it and clicking Delete Parameter
Rename the DEPT parameter to DEPTNUMB.
Obviously, you can choose to
specify or discover the correct parameter immediately; I took you through these
extra steps to illustrate this designers multiple features.
the Procedure Identification and Procedure Parameters sections of
this designer by clicking their associated toggle ()
designer should now look like this:
select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES;
select * from STAFF where dept = deptnumb;
Note: When you enter
this new statement, you can leverage the IntelliSense hookup to the data
servers schema by specifying the schema name before the STAFF table, and the
schema and table names before the DEPT column, as shown below:
10. Build this stored procedure
by clicking Ctrl+S, and then Yes.
The many other features of the SQL/PL
Stored Procedure designer are outside the scope of this article. I recommend that
you experiment by building other routines to learn all about the capabilities
of this wonderful and unique tool.
11. Refresh the Procedures folders
view in the Server Explorer by right-clicking this folder, and selecting Refresh
if you need to.
12. Test your stored procedure
by passing it a value of 20 to the DEPTNUMB parameter as follows:
the SELECTSTAFFBYDEPT stored procedure and select Run. The Run
Options window opens.
the Null check box.
Value and enter 20.
These steps are shown
Notice that when you run a
procedure using the IBM add-ins for Visual Studio 2005, you can leverage options
for pre- and post-run script processing. For example, you may want a pre-run
script to create and populate a table with data, while a post-run script may
delete the table. A Commit after run script may check the routines code into
Visual Source safe, and so on. In addition, the next time you run this routine,
the stored procedure will cache the entered parameters value to make it easier
Your results are returned in a DB2
data grid and should be similar to this:
The DB2 data grid in Visual Studio
2005 is feature-rich and capable of many operations such as import and export
of data (including XML), projections, restrictions, row-level filtering,
sorting, and more.