StarQuest Technical Documents

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

Last Update: 29 November 2016
Product: StarSQL
Version: 6.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 .NET Framework Data Provider for ODBC.

Solution

The following instructions explain how to execute DB2 SQL parameterized stored procedures from a Visual Studio 2008 VB.NET application using the .NET Framework Data Provider for ODBC (supplied with the .NET Framework 3.5). If you are using Visual Basic .NET 2002/2003 or have the .NET Framework 1.x or 2.x, refer to the Microsoft Knowledge Base Article How To Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual Basic .NET (Article ID: 309486) for additional instructions.

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 previously referenced.

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

After the stored procedure has been built, create the VB.NET application.

2. Start Visual Studio 2008 and create a new Visual Basic Windows Forms Application.
3. On the Project menu, select Properties and verify that System.Data is in the list of references. Close the properties window.
4. Switch to Code view (press F7), and add the following code immediately before the Public Class Form1 code:

   Imports System.Data.Odbc

   The System.Data.Odbc namespace is the .NET Framework Data Provider for ODBC.

5. Switch back to Design View. Add a Button control to Form1 and label it StarSQL.
6. 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 user credentials.

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()

'Display the result set
While dr.Read

Console.WriteLine(dr.GetString(1))

'Or write to the debug output window
'Debug.WriteLine(dr.GetString(1))

End While

dr.Close()

'Display the output parameter
Console.WriteLine("Average Salary: " & CStr(cmd.Parameters(1).Value))

'Or write to the debug output window
'Debug.WriteLine("Average salary: " & CStr(cmd.Parameters(1).Value))

Catch o As OdbcException

MsgBox(o.Message.ToString)

Finally

cn.Close()

End Try

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

Hints for using StarSQL with .NET

• When using a connection string, specify which driver to use:

64-bit: Driver={StarSQL (64-bit)};
32-bit: Driver={StarSQL 32};

Failing to do any of the following may result in the error:

ERROR [HYC00] [StarSQL][StarSQL ODBC Driver]Driver not capable.

• Be sure that output parameters of a stored procedure are specified as such; the default (when not specified) is input.
• When passing null values to a stored procedure, use 'DBNull.Value' rather than 'null'.
• When passing a varchar parameter (System.Data.Odbc.OdbcType.VarChar) to a stored procedure, be sure to specify the length of the parameter; this is a requirement of .NET.