StarQuest Technical Documents

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

Last Update: 05 February 2009
Product: StarSQL
Version: 5.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

cn = New OdbcConnection("DSN=STARSQL_DSN;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

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

DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. 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 instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.