Building and Using an SQL/PL Stored Procedure with Visual Studio 2005

Thursday Mar 1st 2007 by Paul Zikopoulos
Share:

Learn 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.

Since the middle of 2006, I’ve 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.

Note:

To perform the steps in this article, ensure that you’ve created the SAMPLE database by entering the db2sampl command from your operating system’s command prompt. In addition, ensure that you’ve 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:

1.  Expand the entry for the SAMPLE database in the Server Explorer, select the Procedures folder, and then Add New SQL Procedure with Designer

2.  The 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 don’t 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 this routine.

3.  In the Procedure Parameters window, add a parameter to this routine by clicking Add parameter ().

4.  Click the Name field and rename this parameter to DEPTNUMB.

5.  Click 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.

6.  The 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 I’ll 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:

7.  Erase 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 designer’s multiple features.

8.  Collapse the Procedure Identification and Procedure Parameters sections of this designer by clicking their associated toggle () icons. The designer should now look like this:

9.  Replace

select ROUTINENAME, ROUTINESCHEMA from SYSIBM.SYSROUTINES;

with

select * from STAFF where dept = deptnumb;

Note: When you enter this new statement, you can leverage the IntelliSense hookup to the data server’s 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 folder’s 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:

a.  Right-click the SELECTSTAFFBYDEPT stored procedure and select Run. The Run Options window opens.

b.  Deselect the Null check box.

c. Click Value and enter 20.

d.  Click Run.

These steps are shown below:

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 routine’s code into Visual Source safe, and so on. In addition, the next time you run this routine, the stored procedure will cache the entered parameter’s value to make it easier to run.

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.

Using an SQL/PL stored procedure in your application

Now that you’ve quickly built a DB2 SQL/PL stored procedure, I’ll show you how to quickly incorporate it into your application.

Note: While this article showed you how to build a stored procedure that resides in a DB2 data server running Linux, UNIX, or Windows, you could easily extend this capability to DB2 for i5/OS or DB2 for z/OS using DB2 Connect. The process would be pretty much the same.

1.  Create a new Visual Basic application called CallMySPProject by selecting File>New Project>Windows Application, selecting Visual Basic (which might be located under Other Languages), entering the project name in the Name field, and clicking OK:

Note: Unlike other applications built in this series, this application will be built in Visual Basic.NET.

2.  Ensure that the Add Data Source view is displayed in the IDE by pressing Shift+Alt+Delete or selecting Data>Show Data Sources from the menu bar.

3.  Click Add New Data Source in the Data Sources view, select Database, and click Next:

4.  Select the SAMPLE database from the Which data connection should your application use to connect to the database? drop-down list.

5.  Select the Yes, include sensitive data in the connection string radio button and click Next. (I’m assuming that you’re working on your own test machine here and notice that Visual Studio 2005 collapses the Connection String field automatically).

6.  Accept the defaults on the next page of this wizard. Click Next.

7.  Expand the Stored Procedures toggle, select the SELECTSTAFFBYDEPT stored procedure, and then click Finish:

Note: This step is a little different in previous articles from what I’ve shown below since we are populating a data set from a stored procedure instead of directly from a table.

The designer palette should now look similar to the following one. (You may need to expand the SAMPLEDataSet manually.)

8.  Drag the SELECTSTAFFBYDEPT stored procedure from the Data Sources view and drop it on your WinForm; it should automatically add a data grid and tool strips and look like this:

You can see that two different tool strips were automatically created after you performed this function. (This is different from what I showed you in the Using the FILLBY Method to Filter DB2 9 Data Sets...with a Twist article. Now, this function will be handled by the stored procedure code that you built earlier in this article.

9.  Click on the pull-down menu to the right of the Fill tool strip, and select Label.

Note: If there is no drop-down menu beside the Fill tool strip, click Fill and it should appear.

The toolStripLabel1 (or whatever it is named in your IDE) should now appear in the Fill tool strip:

10.  Right-click the toolStripLabel1 object, select Properties, and then change its Text property to Department Number:

11.  Create a field where you can specify the input parameter for your stored procedure by clicking the Fill drop-down box and selecting Textbox.

12.  Double-click Fill to see all the code that was generated for you:

You can see that the DEPTNUMB variable was automatically dimensioned (DIMed) for you by Visual Studio 2005.

13.  Change the code as follows:

Dim DEPTNUMB As Object = New Object

to

Dim DEPTNUMB As Object = ToolstripTextBox1.Text

14.  Press F5 to build the application. The application opens:

15.  Enter a valid department number for the STAFF table in the Department Number field, and press Fill. The WinForm passes the parameter value in the Department Number field to the SELECTSTAFFBYDEPT stored procedure and populates the data grid with the results.

You can use any valid parameter for department number in the STAFF table. If you pass the application a department number that doesn’t exist in the STAFF table, it will simply return an empty result set.

The following figure shows the passing of department number 20 to the SELECTSTAFFBYDEPT stored procedure:

Wrapping it up...

In this article, I showed you how to use the SQL/PL designer to create a stored procedure that takes as input a parameter used to represent the DEPT column in the STAFF table. I showed you some of the great design-time enhancement features of the SQL/PL Procedure designer with its IntelliSense hookup to the database schema and the ability to import the input parameter from the table itself (both of which reduce design time and help prevent costly errors). I then showed you how to include your stored procedure in an application.

» See All Articles by Columnist Paul C. Zikopoulos

About the Author

Paul C. Zikopoulos, BA, MBA, is an award-winning writer and speaker with the IBM Database Competitive Technologies team. He has more than ten years of experience with DB2 UDB and has written over one hundred magazine articles and several books about it. Paul has co-authored the books: DB2 9 New Features (available soon), DB2 Version 8: The Official Guide, DB2: The Complete Reference, DB2 Fundamentals Certification for Dummies, DB2 for Dummies, and A DBA's Guide to Databases on Linux. Paul is a DB2 Certified Advanced Technical Expert (DRDA and Cluster/EEE) and a DB2 Certified Solutions Expert (Business Intelligence and Database Administration). In his spare time, he enjoys all sorts of sporting activities, running with his dog Chachi, and trying to figure out the world according to Chloë – his new daughter. You can reach him at: paulz_ibm@msn.com.

Trademarks

IBM, DB2, DB2 Connect, DB2 Universal Database, i5/OS, and z/OS are trademarks or registered trademarks of International Business Machines Corporation in the United States, other countries, or both.

Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.

UNIX is a registered trademark of The Open Group in the United States and other countries.

Linux is a registered trademark of Linus Torvalds in the United States, other countries, or both.

Other company, product, and service names may be trademarks or service marks of others.

Copyright International Business Machines Corporation, 2007. All rights reserved.

Disclaimer

The opinions, solutions, and advice in this article are from the author’s experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the author’s knowledge at the time of writing.

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved