StarQuest Technical Documents

How To Execute a DB2 Query from a Visual Basic .NET Application Using StarSQL

Last Update: 29 November 2016
Product: StarSQL
Version: 6.x
Article ID: SQV00SQ010

Abstract

This document describes how to execute a DB2 query 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 a DB2 query 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 use the ODBC .NET Managed Provider in Visual Basic .NET and connection strings (Article ID: 310985) 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. Start Visual Studio 2008 and create a new Visual Basic Windows Forms Application.
2. On the Project menu, select Properties and verify that System.Data is in the list of references. Close the properties window.
3. 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.

4. Switch back to Design View. Add a Button control to Form1 and label it StarSQL.
5. Double-click the StarSQL command button to switch to the code window for the button Click event.
6. 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.  Modify the SQL statement to provide the appropriate schema for the STAFF table, or set the mystring object to any simple SELECT statement that returns a result set and modify the WriteLine command accordingly.

Dim cn As OdbcConnection
Try

' To use an ODBC data source:

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

' OR to use a DSN-less connection string (StarSQL 64-bit example):

cn = New OdbcConnection("Driver={StarSQL (64-bit)}; Provider=MSDASQL;Server=MYRDB;NetLib=SWTCP32.DLL;
HostName=myhost;Port=7446;PkgColID=STARSQL53;UID=DB2USER;PWD=PASSWORD;")

Dim mystring As String = "SELECT NAME FROM <SCHEMA>.STAFF"
Dim cmd As OdbcCommand = New OdbcCommand(mystring, cn) cn.Open()
Dim dr As OdbcDataReader = cmd.ExecuteReader()

While dr.Read

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

End While

dr.Close()

Catch o As OdbcException

MsgBox(o.Message.ToString)

Finally

cn.Close()

End Try

7. Run the project.

The code executes the DB2 query and displays the result set. You should see the NAME column values of the STAFF table in the Output window.

Calling ODBC using Windows API's (not recommended)

We highly recommend the use the .NET Framework Data Provider for ODBC when using StarSQL with managed code (VB.NET or C#). If, however, you decide to use the ODBC interface directly (using Windows API's), be aware that the Garbage Collection functions of .NET Framework's memory management may cause problems unless you pin memory using either GCHandle.Alloc()/GCHandle.Free() or Marshal.AllocHGlobal()/Marshal.FreeHGlobal() methods.

 

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.