Getting Started with DB2 V8 and ADO using Visual Basic 6

You asked for it, you got it! Author Essma Hasin walks you through basic ADO database operations using DB2 Version 8, including how you can do many of the same functions with the Development Center Visual Studio Add-in.

Essma Hasin, Quality Assurance Engineer, Neneva Technologies

Essma Hasin, a certified Visual Basic and C programmer, is a quality assurance engineer at Neneva Technologies, a consulting firm that specializes in building custom solutions for small and mid-size companies utilizing IBM DB2 Universal Database and the Microsoft .Net framework. Essma can be reached at essma@neneva.com.



13 February 2003

Also available in Japanese

© 2003 International Business Machines Corporation. All rights reserved.

Introduction

IBM® DB2® V8.1 is a great new release with numerous new enhancements in terms of engine capabilities, such as MDC (multi-dimensional clustering) and online reorg to name a few, as well as great new tools such as the Health Center, the Development Center, and the Development Add-Ins for Microsoft® Visual Studio 6.

Although the DB2 Development Add-In for Visual Basic 6 is a great tool that greatly simplifies n-tier application development, especially those applications that rely on database procedures and functions, it is critical for Visual Basic developers to understand the basics of DB2 development using the ADO programming APIs.

In this article, I will cover using the ADO object model to perform four basic database operations; namely, to select, insert, update, and delete records from a DB2 Universal DatabaseTM table using a Visual Basic client application. Where applicable, I will cover two different methods to accomplish these operations:

  • Using direct SQL statements
  • Using ADO recordset method calls

The BasicADO client-server application

Throughout this article, I show code snippets from a sample Visual Basic application, BasicADO, to demonstrate DB2 application development using ADO.

Figure 1. BasicADO sample application
BasicADO sample application

The BasicADO application selects, inserts, updates, and deletes records from a single table that has six base DB2 data types: IDENTITY, INTEGER, VARCHAR, DATE, TIME, and DECIMAL.

Refer to the section titled Sample application download for instructions on downloading, installation, and running the application.


Selecting DB2 records

You can select records from DB2 using ADO using a SQL SELECT statement that is executed against an ADO Connection object, using either an ADO Command object or an ADO Recordset object.

The typical steps required to query data records are:

  1. Create and set up an ADO Connection object.
  2. Connect to the DB2 database using the connection's Open method.
  3. Create and set up an ADO Command object with the appropriate SQL SELECT statement as the CommandText.
  4. Create the ADO Recordset object and assign it to the return value resulting from invoking the command's Execute method.
  5. Close the ADO Connection object using the connection's Close method.

ADO Connection object using IBMDADB2

The first step required for accessing a DB2 database from a Visual Basic application is to create and set up the required ADO Connection object. There are two properties that must be set to connect to DB2:

  • The connection string
  • The cursor location

Recommendation: Use client-side cursors when connecting to DB2 databases. This can be accomplished by assigning the value adUseClient to the CursorLocation property.

Because DB2 ships with its own OLE DB provider, IBMDADB2, the connection string required to connect to DB2 is of the form:

Provider=IBMDADB2; DSN=DatabaseAlias;

You can also include the optional user name and password to the connection string as follows:

User ID=UserName; Password=UserPassword;

The code snippet in Listing 1 shows the GetConnection function that creates and initializes the ADO Connection object.

Listing 1. BasicADO - Function GetConnection
<font size="-1"> 
' Create and return ADO Connection Object 
Public Function GetConnection( _ 
        strDBName As String, _ 
        Optional strUserName As String = "", _ 
        Optional strPassword As String = "") As ADODB.Connection 
    On Error GoTo GetConnection_ErrHandler 
 
    Dim strConnectionString As String 
    strConnectionString = "Provider=IBMDADB2; DSN=" & strDBName 
    If strUserName <> "" 
    And strPassword <> "" Then 
        strConnectionString = strConnectionString & "; 
        User ID=" & strUserName _ 
                 & "; Password=" & strPassword 
    End If 
     
    ' Create new ADO connection object 
    Dim adoConnection As New ADODB.Connection 
    With adoConnection 
        .CursorLocation = adUseClient 
        .ConnectionString = strConnectionString 
    End With 
 
    ' Return new ADO connection object 
    Set GetConnection = adoConnection 
    Set adoConnection = Nothing 
    Exit Function 
 
GetConnection_ErrHandler: 
    MsgBox "Error Code: " & Err.Number & vbNewLine 
    &; _ 
            "Description: " & Err.Description 
            & vbNewLine & _ 
            "Source: " & Err.Source, _ 
            vbOKOnly + vbCritical 
    Err.Clear 
    Set GetConnection = Nothing 
End Function 
</font>

Note that you must first open the connection before executing any SQL against it, and you must close the connection when you're done. This can be accomplished using the Open and Close methods of the ADO Connection object.

Using the IBM DB2 V8 Development Add-In
Using the DB2 Development Add-Ins for Visual Basic 6, you can automatically generate the ADO code required to create and initialize the ADO Connection object for your project database. The sample code provided in Listing 1 above was based on the generated code from the Add-In itself. I strongly recommend using the DB2 Development Add-In when building DB2 applications using Visual Studio 6.0.

Creating an ADO Command object

To execute any SQL against a DB2 database server, you must first create and initialize an ADO Command object. You can then use the Open method of an ADO Recordset object to execute the desired command SQL.

The code snippet in Listing 2 shows the QueryRecords function that creates and initializes the ADO Command object and executes the Open method of the ADO Recordset object.

Listing 2. BasicADO - Function QueryRecords
<font size="-1"> 
Function QueryRecords(ByRef adoConnection As ADODB.Connection) As  
	ADODB.Recordset 
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
    Dim adoRecordSet As ADODB.Recordset 
 
    On Error GoTo QueryRecords_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        .CommandText = "SELECT tINT, tVARCHAR, tDATE, tTIME, tDECIMAL FROM BASICADO" 
    End With 
 
    'Create record set 
    Set adoRecordSet = New ADODB.Recordset 
    adoRecordSet.Open adoCommand, , adOpenStatic, adLockOptimistic 
     
    Set QueryRecords = adoRecordSet 
     
    Set adoRecordSet = Nothing 
    Set adoCommand = Nothing 
    Exit Function 
 
QueryRecords_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
    Set QueryRecords = Nothing 
End Function 
</font>

Creating and initializing the ADO Command object

The CommandType property must be set to adCmdText to execute an SQL statement. If you'd rather invoke a database stored procedure, set the property to adCmdStoredProc.

The CommandText property must be set to the actual SQL to be executed, or to the stored procedure name. For the BasicADO application, we specified the SELECT statement required to query all of the relevant columns for all of the table records.

The ActiveConnection property must be set to the desired ADO Connection object that has been opened earlier.

Creating and initializing the ADO Recordset object

The CursorType parameter can be set to adOpenStatic. Consult the online ADO documentation for all possible values. The LockingType parameter can be set to adLocOptimistic. Consult the online ADO documentation for all possible values.

Using the IBM DB2 V8 Development Add-In
Using the DB2 Development Add-Ins for Visual Basic 6, you can automatically generate the ADO code required to invoke your server-side stored procedures and functions. It creates the ADO Command and Recordset objects as well as any required ADO parameter objects for those procedures and functions having parameters.

You can also invoke the SQL Assist query builder to graphically build the required SQL for your ADO Commands.

One of the features I found very useful is the ability to sample the content of server-side tables while testing my application.

Accessing ADO Recordset data

After you execute ADO commands that return result sets, you can access those result sets using the ADO Recordset object. For those ADO commands that return multiple result sets, you can use the NextRecordSet method of an ADO Recordset object to move to the next multi-record result set.

There are three ways to access the various columns of the current row in the current result set:

Regardless of the method used, you can get or set the various column values using this access mechanism:

SomeVar = Recordset!FiledName	' To lookup the field value 
Recordset!FieldName = SomeValue	' To set the field value

Using Recordset!FieldName
Using the Recordset!FieldName syntax, you can very easily access the various columns of the current row, where the FieldName refers to the column name. You should avoid this notation if your column names are delimited. The field name is case-insensitive. This is the syntax I use most often.

Using Recordset.Fields("FieldName")
Using the Recordset.Fields("FieldName") syntax is very similar to the Recordset!FieldName with the exception that you can now use delimited column names.

Using Recordset.Fields(FieldIndex)
For those result sets that do not include column names, or for faster access to the various result set columns, you can use the Recordset.Fields(FieldIndex) syntax. This syntax dictates that you must know the order of your result set columns. If you modify your query to include additional columns, then you may have to go back and adjust your field indexes.

Populating ListView using an ADO Recordset

You can programmatically populate a Visual Basic ListView object with the data rows from a given ADO Recordset object by iterating through the rows of the result set and using the Add method of a ListView.

You can move back and forth between the rows of an ADO Recordset using the following methods: MoveFirst, MoveLast, Move Next, and MovePrevious. You can also check for the end of the result set using the EOF boolean method.

The code snippet in Listing 3 shows the DisplayRecords function, which populates the ListView object of the BasicADO application with the records resulting from QueryRecords function shown in Listing 2.

Listing 3. BasicADO - Subroutine DisplayRecords
 <font size="-1"> 
Private Sub DisplayRecords() 
    Dim adoRecordSet As ADODB.Recordset 
    Set adoRecordSet = modDBUtil.QueryRecords(adoCon) 
     
    'Populate list view with records manually 
    lstRecords.ListItems.Clear 
     
    If Not adoRecordSet Is Nothing Then 
        Dim lstItem As ListItem 
        While Not adoRecordSet.EOF() 
            Set lstItem = lstRecords.ListItems.Add(, , adoRecordSet!tINT) 
            lstItem.SubItems(1) = adoRecordSet!tVARCHAR & "" 
            lstItem.SubItems(2) = GetDateValue(adoRecordSet!tDATE & "") 
            lstItem.SubItems(3) = GetTimeValue(adoRecordSet!tTIME & "") 
            lstItem.SubItems(4) = FormatCurrency
            (adoRecordSet!tDECIMAL & "") 
            adoRecordSet.MoveNext 
        Wend 
    End If 
 
    Set adoRecordSet = Nothing 
    Set lstItem = Nothing 
End Sub 
</font>

Hint - Handling null values gracefully
You can easily handle null column values by including an & "" as shown above. The code fragment means that the null string is concatenated to the field value. This will avoid the runtime error that is generated when you assign a null field value to a Visual Basic variable.

Alternately, you could check for null values before making an assignment by using the following code:

If adoRecordSet.Fields("SomeField").ActualSize > 0 Then 
	SomeVar = adoRecordSet.Fields("SomeField") 
End If

Hint - Formatting date columns
You can use the Format or the FormateDateTime Visual Basic functions to properly format the date column of your recordset. Using the Format function gives you greater flexibility, because it lets you specify your own custom formats. Using FormateDateTime lets you use the standard, locale-specific, date formatting options, such as vbShortDate, vbLongDate, and vbGeneralDate.

Before using these formatting functions, convert the value to a proper date data type using the DateValue Visual Basic function.

The code snippet in Listing 4 shows the GetDateValue function, which can be used to properly format a DATE column type.

Listing 4. BasicADO - Function GetDateValue
<font size="-1"> 
Public Function GetDateValue(ByRef strDate As String) As String 
    If strDate <> "" Then 
        GetDateValue = Format(DateValue(strDate), "mm/dd/yyyy") 
        'GetDateValue = FormatDateTime(DateValue(strDate), vbGeneralDate) 
    Else 
        GetDateValue = "" 
    End If 
End Function 
</font>

Hint - Formatting time columns
Unlike the DATE data type, when you display the value of TIME data type column you get some bogus date followed by the actual time value. You can use the Format or the FormateDateTime Visual Basic functions to properly format the date column of your recordset. Using the Format function gives you greater flexibility, as it allows you to specify your own custom formats. Using the FormateDateTime allows you to use the standard, locale specific, time formatting options, such as vbShortTime and vbLongTime.

Before using these formatting functions, convert the value to a proper time data type using the TimeValue Visual Basic function.

The code snippet in Listing 5 shows the GetTimeValue function, which can be used to properly format a TIME column type.

Listing 5. BasicADO - Function GetTimeValue
<font size="-1"> 
Public Function GetTimeValue(ByRef strTime As String) As String 
    If strTime <> "" Then 
        GetTimeValue = Format(TimeValue(strTime), "hh:nn:ss") 
        'GetTimeValue = FormatDateTime(TimeValue(strTime), vbLongTime) 
    Else 
        GetTimeValue = "" 
    End If 
End Function 
</font>

Hint - Formatting decimal columns
You can format a decimal type in a verity of ways. For instance:

  • FormatCurrency function converts the decimal to a locale-specific currency value.
  • FormatPercent function converts the decimal to a percent value.
  • FormatNumber function converts the decimal to a specific numeric value that has a specified number of leading zeros and/or decimal places.

Inserting new DB2 records

It is often necessary to insert new records into existing database tables based on user input or some generated data. You can accomplish this operation in a variety of ways. Two such methods are:

  • Using a SQL INSERT statement
  • Using the Add method of an ADO Recordset object

One key difference between the two methods is the fact that you require an INSERT statement for each record to be inserted; whereas by using the ADO Recordset method, you can batch multiple inserts and then execute them as one unit using the Update method of the ADO Recordset object.

Insert using an INSERT statement

To insert records using a SQL INSERT statement, you must:

  1. Create and initialize the ADO Command object with the proper SQL INSERT statement as the command text. The actual column values may be passed as positional parameters using the '?' parameter marker. This allows for using the same ADO command to insert multiple records.
  2. Set the values of the positional parameters to the actual column values for the new row.
  3. Execute the command using the Execute method of the ADO command object.
  4. Reset the parameter values and re-execute for each additional row to be inserted.

The code snippet in Listing 6 shows the InsertUsingInsert subroutine, which executes a multi-row insertion using an SQL INSERT statement. The same ADO command is used to insert multiple rows.

Listing 6. BasicADO - Subroutine InsertUsingInsert
<font size="-1"> 
Sub InsertUsingInsert(ByRef adoConnection As ADODB.Connection) 
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
 
    On Error GoTo InsertUsingInsert_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        .CommandText = "INSERT INTO BASICADO (tINT, tVARCHAR, tDATE,  
tTIME, tDECIMAL) VALUES(?,?,?,?,?)" 
    End With 
 
    'Add Parameters to the Command object 
    Dim adoParm_tINT As Parameter 
    Set adoParm_tINT = adoCommand.CreateParameter("tINT", _ 
                    adInteger, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tINT) 
     
    Dim adoParm_tVARCHAR As Parameter 
    Set adoParm_tVARCHAR = adoCommand.CreateParameter("tVARCHAR", _ 
                    adVarChar, _ 
                    adParamInput, _ 
                    256) 
    Call adoCommand.Parameters.Append(adoParm_tVARCHAR) 
     
    Dim adoParm_tDATE As Parameter 
    Set adoParm_tDATE = adoCommand.CreateParameter("tDATE", _ 
                    adDBDate, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tDATE) 
     
    Dim adoParm_tTIME As Parameter 
    Set adoParm_tTIME = adoCommand.CreateParameter("tTIME", _ 
                    adDBTime, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tTIME) 
     
    Dim adoParm_tDECIMAL As Parameter 
    Set adoParm_tDECIMAL = adoCommand.CreateParameter("tDECIMAL", _ 
                    adDecimal, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tDECIMAL) 
     
    'Set the values and execute the 1st insert 
    adoParm_tINT.Value = 1 
    adoParm_tVARCHAR.Value = "Hello world at 1!" 
    adoParm_tDATE.Value = "21/02/1997" 
    adoParm_tTIME.Value = "12:00:01" 
    adoParm_tDECIMAL.Value = "1111.11" 
    Call adoCommand.Execute 
     
    'Set the values and execute the 2nd insert 
    adoParm_tINT.Value = 2 
    adoParm_tVARCHAR.Value = "Hello world at 2!" 
    adoParm_tDATE.Value = "01/03/1999" 
    adoParm_tTIME.Value = "12:00:02" 
    adoParm_tDECIMAL.Value = "2222.22" 
    Call adoCommand.Execute 
 
    Set adoCommand = Nothing 
    Exit Sub 
 
InsertUsingInsert_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub 
</font>

Creating and initializing ADO parameter objects
When using ADO Commands that require positional or stored procedure parameters, you can use the ADO Parameters collection of the ADO Command to set and get values for these parameters.

Each parameter object must be created, initialized, and then appended to the parameters collection of the ADO Command object. The input parameter values must be set before executing the command. The output parameter values must be read after executing the command.

As shown in Listing 6, use command parameters as follows:

  1. Create a new ADO Parameter object using the command's CreateParameter method. The parameter name, type, size, and optional value should be specified.
  2. Append the parameter to the command's Parameters collection using the collection's Append method.
  3. Specify the value for any input parameters by assigning an input value to the Value property of the parameter.
  4. Execute the command.
  5. Read the value of any output parameters using the Value property of the parameter.

Important: Use an ADO data type that maps correctly to the DB2 SQL type for each parameter. If you use the DB2 V8.1 Development Add-In, the stored procedure parameters are created and set up automatically for you, which makes it that much easier for you to develop DB2-based applications.

Insert using an ADO Recordset

To insert records using an ADO Recordset, you must:

  1. Create the ADO Command and resulting ADO record set to hold the new list of records. The ADO record set need not have any records in it. What is critical is a proper column list for the rows to be inserted. To this end, your SELECT statement for the ADO Command may have a WHERE clause with a false condition.
  2. Open the ADO connection.
  3. Execute the ADO command and retrieve the potentially empty ADO record set.
  4. Append new records to the ADO record set using the AddNew method.
  5. Set the various column values for each of the newly added records using any of the three methods for accessing record set data.
  6. Invoke the Update method of the ADO record set to insert the new records into the DB2 table.
  7. Close the ADO connection.

The code snippet in Listing 7 shows the InsertUsingRecordset subroutine, which executes a multi-row insertion using an ADO recordset object.

Listing 7. BasicADO - Subroutine InsertUsingRecordset
<font size="-1"> 
Sub InsertUsingRecordset(ByRef adoConnection As ADODB.Connection) 
     
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
    Dim adoRecordSet As ADODB.Recordset 
 
    On Error GoTo InsertUsingRecordset_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        .CommandText = "SELECT tINT, tVARCHAR, tDATE, tTIME, tDECIMAL FROM  
BASICADO WHERE tINT = 0" 
    End With 
 
    'Create record set 
    Set adoRecordSet = New ADODB.Recordset 
    adoRecordSet.Open adoCommand, , adOpenStatic, adLockOptimistic 
     
    'Update values for row 1 
    adoRecordSet.AddNew 
    adoRecordSet!tINT = 1 
    adoRecordSet!tVARCHAR = "Hello world at 1!" 
    adoRecordSet!tDATE = "21/02/1997" 
    adoRecordSet!tTIME = "12:00:01" 
    adoRecordSet!tDECIMAL = "1111.11" 
     
    'Update values for row 2 
    adoRecordSet.AddNew 
    adoRecordSet!tINT = 2 
    adoRecordSet!tVARCHAR.Value = "Hello world at 2!" 
    adoRecordSet!tDATE = "01/03/1999" 
    adoRecordSet!tTIME = "12:00:02" 
    adoRecordSet!tDECIMAL = "2222.22" 
     
    'Execute all inserts 
    Call adoRecordSet.Update 
     
    Set adoRecordSet = Nothing 
    Set adoCommand = Nothing 
    Exit Sub 
 
InsertUsingRecordset_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub 
</font>

Note that proper DATE and TIME value strings must be used to set the corresponding column values for the newly inserted record.


Updating existing DB2 records

Records are often updated as part of any database application. This update may be the result of user modifying fields in a property dialogue for a given object record or indirectly due to some user actions or transaction. You can accomplish this operation in a variety of ways. Two such methods are:

  • Using a SQL UPDATE statement
  • Using direct field value manipulation via an ADO Recordset object

One key advantage of using the Recordset update mechanism is that you typically have already queried those records for display purposes; thus, updating the records is a simple matter of modifying them on the client machine before sending them back to the server. In contrast, the UPDATE statement must take place on the server using the WHERE clause. This means that you need to re-identify those records to be updated.

Update using an UPDATE statement

To update records using an SQL UPDATE statement, you must:

  1. Create and initialize the ADO Command object with the proper SQL UPDATE statement as the command text. The actual column update values as well as the WHERE clause values for these columns may be passed as positional parameters using the '?' parameter marker. This allows for using the same ADO command to update dynamically, update of multiple records, or update of multiple groups of records.
  2. Set the values of the positional parameters to the actual column values.
  3. Execute the command using the Execute method of the ADO command object.
  4. Reset the parameter values and re-execute for each additional row or groups of rows to be updated.

The code snippet in Listing 8 shows the UpdateUsingUpdate subroutine, which executes a multi-row update using an SQL UPDATE statement. The same ADO command may be used to update multiple rows or groups of rows.

Listing 8. BasicADO - Subroutine UpdateUsingUpdate
<font size="-1"> 
Sub UpdateUsingUpdate(ByRef adoConnection As ADODB.Connection) 
     
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
 
    On Error GoTo UpdateUsingUpdate_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        .CommandText = "UPDATE BASICADO " & _ 
                       "SET tVARCHAR = 'Bye world!' " & _ 
                       "WHERE tINT = ?" 
    End With 
 
    'Add Parameters to the Command object 
    Dim adoParm_tINT As Parameter 
    Set adoParm_tINT = adoCommand.CreateParameter("tINT", _ 
                    adInteger, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tINT) 
     
    'Update all records having tINT = 1 
    adoParm_tINT.Value = 1 
    Call adoCommand.Execute 
     
    Set adoCommand = Nothing 
    Exit Sub 
 
UpdateUsingUpdate_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub 
</font>

Update using an ADO Recordset

To update records using an ADO Recordset, you must:

  1. Create the ADO Command and resulting ADO record set to hold the list of records to be displayed and later updated. The ADO record set need not have only those records that will be updated.
  2. Open the ADO connection.
  3. Execute the ADO command and retrieve the initial list of applicable rows as an ADO record set. You must include primary key columns in order for Recordset update to work. See hint below.
  4. Iterate and locate each record in the ADO record set using the Find method.
  5. Set the various column values for each of the records to be updated with the new value using any of the three methods for accessing record set data.
  6. Invoke the Update method of the ADO record set to affect your multi-row updates in the DB2 table.
  7. Close the ADO connection.

The code snippet in Listing 9 shows the UpdateUsingRecordset subroutine, which executes a multi-row update using an ADO recordset object.

Listing 9. BasicADO - Subroutine UpdateUsingRecordset
<font size="-1"> 
Sub UpdateUsingRecordset(ByRef adoConnection As ADODB.Connection) 
     
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
    Dim adoRecordSet As ADODB.Recordset 
 
    On Error GoTo UpdateUsingRecordset_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        'Update requires primary key, so need to select tKEY 
        .CommandText = "SELECT tKEY, tINT, tVARCHAR, tDATE, tTIME, tDECIMAL  
FROM BASICADO" 
    End With 
 
    'Create record set 
    Set adoRecordSet = New ADODB.Recordset 
    adoRecordSet.Open adoCommand, , adOpenStatic, adLockOptimistic 
     
    'Update all records having tINT = 2 (requires primary key) 
    Call adoRecordSet.Find("tINT = 2") 
    While Not adoRecordSet.EOF() 
        adoRecordSet!tVARCHAR = "Bye world!" 
        adoRecordSet.MoveNext 
        Call adoRecordSet.Find("tINT = 2") 
    Wend 
     
    'Workaround for BOF ADO bug! 
    If adoRecordSet.RecordCount > 0 Then 
        adoRecordSet.MoveFirst 
    End If 
     
    'Execute the update 
    Call adoRecordSet.Update 
 
    Set adoRecordSet = Nothing 
    Set adoCommand = Nothing 
    Exit Sub 
 
UpdateUsingRecordset_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub 
</font>

Hint - Include primary keys
Although primary keys in record objects are seldom updated, you must include the primary key columns in your query. This is required to allow ADO to generate the proper underlying UPDATE SQL using those primary keys to identify the records that must be updated.

Failure to include primary key will result in a run time error being generated, as shown in Figure 2.

Figure 2. BasicADO - Error generated on update without primary keys
BasicADO - Error generated on update without primary keys

Hint - Avoiding EOF ADO error
While working with the BasicADO application, I noticed a bug having to do with invoking the Update method while your ADO record set row index is past the last record. As you can see in the UpdateUsingUpdate code snippet in Listing 8, we used a while loop to iterate through all the records. If we invoke the Update method directly after the loop, we end up with the error message shown in Figure 3.

Figure 3. BasicADO - Error generated on invoking Update while at EOF
BasicADO - Error generated on invoking Update while at EOF

To avoid this error, move your record pointer to the first record in the ADO record set and then invoke the Update method. You can do this by inserting the following code fragment before calling Update:

    'Workaround for EOF ADO bug! 
    If adoRecordSet.RecordCount > 0 Then 
        adoRecordSet.MoveFirst 
    End If

Deleting old DB2 records

Deleting records from tables is similar to updating records, with the exception that instead of modifying row column values, you actually delete the rows. You can delete rows in a variety of ways, including:

  • Using a SQL DELETE statement
  • Invoking the Delete method of an ADO Recordset object

Like the update operation discussed earlier, one key advantage of using the Recordset delete mechanism is that you typically have already queried those records for display purposes; thus deleting them is a simple matter of removing them on the client machine before sending the updated record set back to the server. On the other hand, the DELETE statement must take place on the server using the WHERE clause. This means that you need to re-identify those records to be deleted.

Delete using a DELETE statement

To delete records using a SQL DELETE statement, you must:

  1. Create and initialize the ADO Command object with the proper SQL DELETE statement as the command text. The WHERE clause values for those columns required to identify the rows to be deleted may be passed as positional parameters using the '?' parameter marker. This allows for using the same ADO command to delete multiple records dynamically.
  2. Set the values of the positional parameters to the actual column values required to identify the records to be deleted.
  3. Execute the command using the Execute method of the ADO command object.
  4. Reset the parameter values and re-execute for each additional row or groups of rows to be deleted.

The code snippet in Listing 10 shows the DeleteUsingDelete subroutine, which executes a multi-row delete using an SQL DELETE statement. The same ADO command may be used to delete multiple rows or groups of rows.

Listing 10. BasicADO - Subroutine DeleteUsingDelete
<font size="-1"> 
Sub DeleteUsingDelete(ByRef adoConnection As ADODB.Connection) 
     
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
 
    On Error GoTo DeleteUsingDelete_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        .CommandText = "DELETE FROM BASICADO WHERE tINT = ?" 
    End With 
 
    'Add Parameters to the Command object 
    Dim adoParm_tINT As Parameter 
    Set adoParm_tINT = adoCommand.CreateParameter("tINT", _ 
                    adInteger, _ 
                    adParamInput) 
    Call adoCommand.Parameters.Append(adoParm_tINT) 
     
    'Delete all records having tINT = 1 
    adoParm_tINT.Value = 1 
    Call adoCommand.Execute 
     
    Set adoCommand = Nothing 
    Exit Sub 
 
DeleteUsingDelete_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub 
</font>

Delete using an ADO Recordset

To delete records using an ADO Recordset, you must:

  1. Create the ADO Command and resulting ADO record set to hold the list of records to be displayed and a subset later deleted. The ADO record set need not have only those records that will be deleted.
  2. Open the ADO connection.
  3. Execute the ADO command and retrieve the initial list of applicable rows as an ADO record set. You must include primary key columns in order for Recordset update to work.
  4. Iterate and locate each record to be deleted in the ADO record set using the Find method.
  5. Invoke the Delete method on the ADO record set to delete the record.
  6. Continue with the record set iteration until you're done.
  7. Invoke the Update method of the ADO record set to propagate your multi-row deletes to the DB2 table.
  8. Close the ADO connection.

The code snippet in Listing 11 shows the DeleteUsingRecordset subroutine, which executes a multi-row delete using an ADO recordset object.

Listing 11. BasicADO - Subroutine DeleteUsingRecordset
<font size="-1"> 
Sub DeleteUsingRecordset(ByRef adoConnection As ADODB.Connection) 
     
    'Create ADO objects 
    Dim adoCommand As ADODB.Command 
    Dim adoRecordSet As ADODB.Recordset 
 
    On Error GoTo DeleteUsingRecordset_ErrHandler 
 
    Set adoCommand = New ADODB.Command 
 
    With adoCommand 
        .CommandType = adCmdText 
        .ActiveConnection = adoConnection 
        'Delete requires primary key, so need to select tKEY 
        .CommandText = "SELECT tKEY, tINT, tVARCHAR, tDATE, tTIME, tDECIMAL  
FROM BASICADO" 
    End With 
 
    'Create record set 
    Set adoRecordSet = New ADODB.Recordset 
    adoRecordSet.Open adoCommand, , adOpenStatic, adLockOptimistic 
     
    'Delete all records having tINT = 2 (requires primary key) 
    Call adoRecordSet.Find("tINT = 2") 
    While Not adoRecordSet.EOF() 
        adoRecordSet.Delete 
        adoRecordSet.MoveNext 
        Call adoRecordSet.Find("tINT = 2") 
    Wend 
     
    'Workaround for EOF ADO bug! 
    If adoRecordSet.RecordCount > 0 Then 
        adoRecordSet.MoveFirst 
    End If 
     
    'Execute the delete 
    Call adoRecordSet.Update 
 
    Set adoRecordSet = Nothing 
    Set adoCommand = Nothing 
    Exit Sub 
 
DeleteUsingRecordset_ErrHandler: 
    Call ShowAllErrors(adoConnection) 
End Sub   
</font>

Hint - Include primary keys
You must include the primary key columns in your query in order for you to perform deletion using a record set. This is required to allow ADO to generate the proper underlying DELETE SQL using those primary keys to identify the records that must be deleted.


Conclusion

The IBM DB2 V8.1 Development Add-In for Visual Basic 6 is a great tool that greatly simplifies n-tier application development. This article describes in detail how to perform the four basic database operations, SELECT, INSERT, DELETE, and UPDATE using the ADO programming APIs. This article also described some useful hints and tips that should help you develop high-quality DB2 applications using ADO.


Sample application download

Throughout this article, I have shown code snippets from a sample Visual Basic application, BasicADO, to demonstrate DB2 application development using ADO. This section provides you with download, setup, and running instructions for this application.

This application is provided as is, without any promise of warranty or support. Feel free to use and redistribute all or part of the application code in your own projects.

Downloading the BasicADO application

The BasicADO application is a Visual Basic 6.0 project with a DB2 script file to create the required table. The basicado.zip file contains the following files:

  • BasicADO.vbp - Project file
  • frmMain.frm - Main application form
  • modDBUtil.bas - Module containing the DB2 ADO functions
  • createtb.bat - Batch file to run the createtb.ddl DB2 script file
  • createtb.ddl - DB2 script file required to connect to SAMPLE and create the required table

The system requirements for the BasicADO application are:

  • IBM DB2 Universal Database V8.1 or later
  • IBM DB2 OLE DB provider (IBMDADB2)
  • Microsoft Windows 2000®, XP®, or NT®
  • Microsoft Visual Studio V6.0
  • MDAC 2.6 or higher

Setting up the BasicADO application

Although the BasicADO application is configured to run with the SAMPLE database, you can change all instances of SAMPLE with your own database name. The files affected include createtb.ddl and frmMain.frm.

Before launching Visual Basic, run the script required to create the BasicADO application table. To do this, simply open a DB2 command window and run the batch file, createtb.bat.

Now that you have created the required table, simply start Visual Basic and open the BasicADO.vbp project file.

Running the BasicADO application

To run the BasicADO application, you will need to either compile the project into an executable, or run it directly from Visual Basic.

After running the application, you will be presented with the form shown earlier in Figure 1. The application expects you to go through a pre-defined sequence of actions; hence, the action buttons are enabled and disabled according to this sequence:

  1. Connect to the database
  2. Insert using INSERT
  3. Insert using record set
  4. Update using UPDATE
  5. Update using record set
  6. Delete using DELETE
  7. Delete using recordset
  8. Disconnect

Download

DescriptionNameSize
Code samplebasicado.zip  ( HTTP | FTP )7KB

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=13849
ArticleTitle=Getting Started with DB2 V8 and ADO using Visual Basic 6
publish-date=02132003