Using an SQL/PL stored procedure in your application
Now that youve quickly built a DB2
SQL/PL stored procedure, Ill show you how to quickly incorporate it into your
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.
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
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.
Add New Data Source in the Data Sources view, select Database,
and click Next:
the SAMPLE database from the Which data connection should your application
use to connect to the database? drop-down list.
the Yes, include sensitive data in the connection string radio button
and click Next. (Im assuming that youre working on your own test
machine here and notice that Visual Studio 2005 collapses the Connection
String field automatically).
the defaults on the next page of this wizard. Click Next.
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 Ive 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
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.
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
The toolStripLabel1 (or
whatever it is named in your IDE) should now appear in the Fill tool
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
Dim DEPTNUMB As Object = New Object
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
You can use any valid parameter
for department number in the STAFF table. If you pass the application a
department number that doesnt 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
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: firstname.lastname@example.org.
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.
product, and service names may be trademarks or service marks of others.
International Business Machines Corporation, 2007. All rights reserved.
solutions, and advice in this article are from the authors 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 authors knowledge at the time of writing.