StarQuest Technical Documents

How To Execute DB2 SQL Parameterized Stored Procedures from a Visual Basic .NET Application Using StarSQL

Last Update: 26 June 2008
Product: StarSQL
Version: 5.x
Article ID: SQV00SQ009

Abstract

This document describes how to execute DB2 SQL parameterized stored procedures from a Visual Basic .NET application using the StarSQL software and the Microsoft ODBC.NET Data Provider.

Solution

This solution uses the STAFF table provided with the DB2 UDB Sample tables. Contact IBM to obtain a copy of this sample table. The sample code for adding a command button in step 7 is excerpted from Microsoft Knowledge Base Article 309486.

  1. Create the Stored Procedure.

The following stored procedure code may need to be modified to suit the host DB2 platform.  Additionally, provide a valid schema name for the TESTPROC procedure and the STAFF table before you build the procedure.

CREATE PROCEDURE <SCHEMA>.TESTPROC ( IN DEPT_IN INTEGER, OUT AVGSAL FLOAT )
DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
-- DEPT_IN
-- AVGSAL
------------------------------------------------------------------------
P1: BEGIN

-- Declare variable
DECLARE AVGSAL_TMP FLOAT DEFAULT 0.0;

-- Declare cursors for result set and output parameter data
DECLARE cursor1 CURSOR WITH HOLD WITH RETURN FOR
SELECT * FROM <SCHEMA>.STAFF WHERE DEPT = DEPT_IN;
DECLARE cursor2 CURSOR WITH RETURN FOR
SELECT AVG(SALARY) FROM <SCHEMA>.STAFF WHERE DEPT = DEPT_IN;

-- Cursor1 is left open for client application
OPEN cursor1;
OPEN cursor2;
FETCH cursor2 into AVGSAL_TMP;

CLOSE cursor2;
SET AVGSAL = AVGSAL_TMP;

END P1

  1. If you have not already done so, download and install the ODBC .NET Managed Provider from the Microsoft Download Center.
  2. Start Visual Studio .NET and create a new Visual Basic .NET Windows Application.
  3. On the Project menu, click Add Reference. On the .NET tab, double-click Microsoft.Data.ODBC.dll to add a reference to the Microsoft.Data.ODBC namespace. Click OK.
  4. Switch to Code view (press F7), and add the following code immediately before the Public Class Form1 code:

Imports System.Data
Imports Microsoft.Data.ODBC

  1. Switch back to Design View. Add a Button control to Form1 and label it StarSQL.
  2. Double-click the StarSQL command button to switch to the code window for the button Click event. Paste the following code in the Click event procedure, changing the connection string as appropriate to supply a valid StarSQL DSN and DB2 userid and password.

Dim cn As OdbcConnection

Try

cn = New OdbcConnection("DSN=STARSQL_DSN;UID=DB2USER;PWD=PASSWORD;")

Dim cmd As OdbcCommand = New OdbcCommand("CALL <SCHEMA>.TESTPROC (?, ?)", cn)

Dim prm As OdbcParameter = cmd.Parameters.Add("DEPT_IN", OdbcType.Int) prm.Value = 51

prm = cmd.Parameters.Add("AVGSAL", OdbcType.Double) prm.Direction = ParameterDirection.Output

cn.Open()

Dim dr As OdbcDataReader = cmd.ExecuteReader()

While dr.Read

Console.WriteLine(dr.GetString(0))

End While
dr.Close()

Console.WriteLine("Average Salary (output param): {0}", cmd.Parameters(1).Value)

Catch o As OdbcException

MsgBox(o.Message.ToString)

Finally

cn.Close()

End Try

  1. Run the project.

The code calls the "TESTPROC" stored procedure that was created in step 1, passing in the department number (DEPT) as a single input parameter, and returns a result set along with an output parameter. In the Output window you should see the list of employees in the specified department and the average salary for the employees in that department.

DISCLAIMER

The information in technical documents comes without any warranty. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The information in technical documents may be gathered from various sources, including IBM, Microsoft, and other organizations.