StarQuest Technical Documents

How To Execute a DB2 Query from a Visual Studio C# .NET Application Using StarSQL

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

Abstract

This document describes how to execute a DB2 query from a Visual Studio C# 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 C# application using the .NET Framework Data Provider for ODBC (supplied with the .NET Framework 3.5). If you are using Visual Studio .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 C# and Connection Strings (Article ID: 310988) for additional instructions.

  1. Start Visual Studio 2008 and create a new C# Windows Forms Application.
  2. In the Solution Explorer , select References and verify that System.Data is in the list of references.
  3. Open the Toolbar, select Button, and place it on the form. Change the text to "Run Query".
  4. Select Listbox in the Toolbar and place it on the form.
  5. Double-click on the button to open the code window.
  6. Add the following code immediately after the existing using statements:

    using System.Data.Odbc;

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

  7. Paste the following code in the Click event procedure. Change the connection string as appropriate to supply a valid StarSQL data source and DB2 userid and password. Modify the SQL statement, changing MYTABLE to the name of an existing table.
OdbcConnection conn = new OdbcConnection();  conn.ConnectionString = "DSN=MYDSN;UID=MYUSER;PWD=MYPWD"; try {     conn.Open();        using (OdbcCommand com = new OdbcCommand("SELECT * FROM MYTABLE", conn))     {         using (OdbcDataReader reader = com.ExecuteReader())         { 			while (reader.Read()) 			{     			string word = reader.GetString(0);     			listBox1.Items.Add(word);     			}         }     } } catch (Exception ex) {     MessageBox.Show(ex.message); } finally {     conn.Close(); }
  1. Select Build Solution from the Build menu.
  2. Run the application by selecting Start Debugging from the Debug menu, or run the EXE file created during the build.

The code executes the DB2 query and displays the first column of the result set.

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.

 


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.