Teach Yourself Database Programming
with Visual C++ 6 in 21 days

Day 5
      Adding, Modifying, and Deleting Data

• Cursor Types in ADO Recordsets
• The ADO AddNew, Update,and Delete Functions
  • The AddNew Function
  • The Update Function
  • The Delete Function
• The SQL INSERT, UPDATE,and DELETE Statements
  • The SQL INSERT Statement
  • INSERT INTO Products (partnumber, productname) VALUES('xxx', 'yyy')
  • The SQL UPDATE Statement
  • The SQL DELETE Statement
• Summary
• Q&A
  
• Workshop
  • Quiz
  • Exercises

One reason for the existence of database applications is to modify data in databases. You will learn how to modify data in relational databases by using ADO functions, as well as SQL statements, in your C++ programs. When you complete today's work, you will know how to create applications that alter the data in relational databases.

Today you will

• Learn about cursor types in ADO Recordsets.
• Use the ADO AddNew, Update, and Delete functions.
• Use SQL INSERT, UPDATE, DELETE statements.

Cursor Types in ADO Recordsets

As you know, an ADO Recordset object encapsulates a resultset, which contains records from a query, and a cursor, which enables you to move through those records. There are four different types of cursor in ADO--dynamic, keyset, static, and forward only--each with unique capabilities and attributes.

A dynamic cursor enables you to view additions, changes, and deletions made by other users while the recordset is open. A dynamic cursor also enables all types of movement through the Recordset, including the ability to move n number of records forward or backward, move to the first record, and move to the last record.

A keyset cursor enables you to see changes made by other users. However, you can't see records that other users add, nor can you access records that other users delete. It enables all types of movement through the Recordset.

A static cursor provides a static copy (or snapshot) of a set of records and enables all types of movement through the Recordset. Additions, changes, or deletions by other users are not visible.

A forward-only cursor enables you to see changes made by other users and to scroll only forward through records. This improves performance when you need to make only a single pass through a Recordset.

The type of cursor you choose depends on your requirements. However, bear in mind that queries execute much faster if you use a forward-only cursor. In database applications, performance is always an issue. Therefore, you should typically use forward-only cursors and save dynamic, keyset, or static cursors for when you need the features they offer.

You specify the cursor type in ADO before opening the Recordset, or you pass a CursorType argument with the Recordset Open function. Some providers don't support all cursor types. Check the provider's documentation. If you don't specify a cursor type, ADO opens a forward-only cursor by default.

The ADO AddNew, Update, and Delete Functions

One way to add, change, and delete records in a database is to create a Recordset object and call its member functions to add, change, and delete the records in the Recordset. The changes made to the Recordset are applied to the database. In other words, if you change the records in the Recordset, you change records in the database.

The ADO Recordset class has three member functions that enable you to modify the records in the Recordset. These functions are AddNew, Update, and Delete.

Create a new Recordset menu with three items for AddNew, Update, and Delete, as shown in Figure 5.1.

Figure 5.1 : The new Recordset menu.

To view the changes you will make to the database today, it would be handy to add a database project to your current project. Select the Project Add to Project New… menu. Select the Project tab and specify a database project, as shown in Figure 5.2.

Figure 5.2 : Add a database project.

When you click the OK button, you are presented with a dialog window where you specify the ODBC DSN for the database project, as you did in Day 2, "Tools for Database Development in Visual C++ Developer Studio" (refer to Figures 2.11 and 2.12). After you specify the DSN, the database project will be added to your current project. You need to select the Project Set Active Project menu to specify that your current project should be the active one, not the database project. (Otherwise, you will not be able to build or debug your application.)

The AddNew Function

The process of adding records by using the AddNew function involves creating two arrays and passing them as parameters to the AddNew function. The first array is an array that contains the names of the fields that will contain the data in the new record. The second array is an array of values to assign to each field.

The tricky part of using AddNew is that the two arrays you pass as parameters must be VARIANT arrays. The code to produce and manipulate VARIANT arrays in C++ can be involved. Because you are using MFC for the sample applications in this book, your code can be simplified by using the MFC ColeSafeArray class.

Listing 5.1 shows the code for adding a new record by using the AddNew function. Use ClassWizard to add a handler function to the View class for the AddNew menu choice. In the AddNew handler function for that menu item, add the code in Listing 5.1.

Listing 5.1.  Using the ADO AddNew Function

 1:  void CADOMFC1View::OnRecordsetAddnew()
 2:  {
 3:    _RecordsetPtr pRecordSet;
 4:    CADOMFC1Doc * pDoc;
 5:    pDoc = GetDocument();
 6:
 7:    HRESULT hr;
 8:    _bstr_t bstrQuery("SELECT * FROM Products WHERE PartNumber IS 
           NULL");
 9:    _variant_t vNull;
10:    vNull.vt = VT_ERROR;
11:    vNull.scode = DISP_E_PARAMNOTFOUND;
12:
13:    try
14:    {
15:      hr = pRecordSet.CreateInstance(_uuidof(Recordset));
16:      if (SUCCEEDED(hr))
17:      {
18:        pRecordSet->PutRefActiveConnection(pDoc->m_pConnection);
19:        hr = pRecordSet->Open(_variant_t(bstrQuery), vNull,
20:          adOpenForwardOnly, adLockOptimistic, adCmdText);
21:        if (SUCCEEDED(hr))
22:        {
23:          // Create an array for the list of fields in
24:          // the Products table.
25:          COleSafeArray vaFieldlist;
26:          vaFieldlist.CreateOneDim(VT_VARIANT,3);
27:          // Fill in the field names now.
28:          long lArrayIndex[1];
29:          lArrayIndex[0] = 0;
30:          vaFieldlist.PutElement(lArrayIndex,
31:            &(_variant_t(ÒPartNumber")));
32:          lArrayIndex[0] = 1;
33:          vaFieldlist.PutElement(lArrayIndex,
34:            &(_variant_t("ProductName")));
35:          lArrayIndex[0] = 2;
36:          vaFieldlist.PutElement(lArrayIndex,
37:            &(_variant_t("Price")));
38:
39:          // Create an array for the list of values to go in
40:          // the Products table.
41:          COleSafeArray vaValuelist;
42:          vaValuelist.CreateOneDim(VT_VARIANT,3);
43:          // Fill in the values for each field.
44:          lArrayIndex[0] = 0;
45:          vaValuelist.PutElement(lArrayIndex,
46:            &(_variant_t("8TRACK-003")));
47:          lArrayIndex[0] = 1;
48:          vaValuelist.PutElement(lArrayIndex,
49:            &(_variant_t("Bell Bottom Hits")));
50:          lArrayIndex[0] = 2;
51:          vaValuelist.PutElement(lArrayIndex,
52:            &(_variant_t((float)19.95)));
53:
54:          pRecordSet->AddNew(vaFieldlist, vaValuelist);
55:          pRecordSet->Close();
56:        }
57:      }
58:    }
59:    catch( _com_error &e )
60:    {
61:    TRACE( "Error:%08lx.\n", e.Error());
62:    TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
63:    TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
64:    TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
65:    }
66:    catch(...)
67:    {
68:      TRACE( "\n*** Unhandled Exception ***\n" );
69:    }
70:  }

In Listing 5.1, line 3 defines a smart pointer to a Recordset. Lines 4 and 5 obtain a pointer to the MFC document, which will enable you to get to the ADO Connection object that was opened with the document.

Line 8 defines a _bstr_t object that contains a SQL SELECT statement. This SQL statement will be passed to the Recordset Open function and will open a recordset that contains no records. The WHERE clause specifies records where the PartNumber IS NULL. PartNumber is the primary key in the Products table and will never be NULL. You want to use this Recordset only for adding new records to the database, so you don't need the Recordset to contain any records. In other words, you want an empty Recordset.

Lines 9-11 define a _variant_t object named vNull and set two of its data members. vNull will be passed as the second parameter to the Recordset Open function. This parameter can contain a string for connecting to the database during the Open call. Rather than pass a connect string, you pass vNull and (in line 18) specify that the Recordset should use the connection already opened in the MFC document.

Line 15 calls the _RecordsetPtr CreateInstance function to instantiate the Recordset object. If that succeeds, lines 18-20 set the connection and open the Recordset. The Open function specifies a forward-only cursor (adOpenForwardOnly) with optimistic record locking (adLockOptimistic).

Optimistic locking means that just before the record is added, it is locked so that no other users can muck with it while you're trying to write it to the database. Another option for locking is pessimistic locking, which holds the lock for a longer time in the database. You will learn more about optimistic and pessimistic locking later today in the section on the Update function.

In line 20, the last parameter for the Open call is adCmdText, which tells the database that you are passing the text of a SQL statement, which it needs to interpret.

Lines 23-37 create an array of VARIANTs, called vaFieldlist, which contains the field names of the new record. Lines 39-52 create another array of VARIANTs, called vaValuelist, that contains the actual data values you will place in the fields of the new record. If you were adding multiple records, you would probably define these arrays once and simply change the values of the vaValuelist array before calling AddNew to add each record.

Line 54 passes these two arrays in the AddNew call to add the record to the database. Line 55 closes the Recordset object. The rest of the code does exception handling.

You should be able to build the application with no errors or warnings. When you run the application, set a breakpoint on line 5 or so and another breakpoint in the exception-handling code, such as line 61. When you run the application and take the AddNew menu choice, you will receive no exceptions, and it will add the new record to the Products table. If you try to add the same record twice, you will receive an exception from the database, telling you that the changes were not successful because they would create duplicate values in the index or primary key. This is an example of the database ensuring the integrity of the data.

This might seem like a large amount of code for adding merely one record to the database. It is. However, you must realize that the Insert performance (the speed with which you can add records to a database) is often a critical factor in database applications. The ADO AddNew function is highly optimized so that it executes very efficiently (when used with an empty, forward-only Recordset, as in Listing 5.1). To reduce the amount of code you must write, you could encapsulate the code in Listing 5.1 into a function that builds the VARIANT arrays and takes the values of the data for the new records as parameters.

The Update Function

The ADO Update function enables you to edit an existing record. The idea is to open a Recordset that contains the record you want to edit. Position the cursor at the appropriate record (if it is not there already). Change the data in the field(s) you want to edit. Then call Update to commit the changes to the database. (An alternative to calling Update is to move the cursor off the record. This has the effect of implicitly calling Update.)

Use ClassWizard to add a handler function to the View class for the Recordset Update menu; then add the code in Listing 5.2.

Listing 5.2.  Using the ADO Update Function

 1:  void CADOMFC1View::OnRecordsetUpdate()
 2:  {
 3:     RecordsetPtr pRecordSet;
 4:    CADOMFC1Doc * pDoc;
 5:    pDoc = GetDocument();
 6:
 7:    HRESULT hr;
 8:     bstr_t bstrQuery(
 9:      "SELECT * FROM Products WHERE PartNumber = '8TRACK-003'");
10:     variant_t vNull;
11:    vNull.vt = VT_ERROR;
12:    vNull.scode = DISP_E_PARAMNOTFOUND;
13:
14:    try
15:    {
16:      hr = pRecordSet.CreateInstance(_uuidof(Recordset));
17:      if (SUCCEEDED(hr))
18:      {
19:        pRecordSet->PutRefActiveConnection(pDoc->m_pConnection);
20:        hr = pRecordSet->Open(_variant_t(bstrQuery), vNull,
21:          adOpenForwardOnly, adLockOptimistic, adCmdText);
22:        if (!pRecordSet->GetadoEOF())
23:        {
24:          pRecordSet->PutCollect(L"ProductName",
25:            L"Bell Bottoms and Bass Guitars");
26:          pRecordSet->Update(vNull, vNull);
27:          pRecordSet->Close();
28:        }
29:      }
30:    }
31:    catch( _com_error &e )
32:    {
33:      TRACE( "Error:%08lx.\n", e.Error());
34:      TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
35:      TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
36:      TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
37:    }
38:    catch(...)
39:    {
40:      TRACE( "\n*** Unhandled Exception ***\n" );
41:    }
42:  }

Lines 3-7 are identical to the code in Listing 5.1 and merely initialize some variables you will need. Line 8 defines an instance of _bstr_t that contains a SQL SELECT statement that selects the record you added in Listing 5.1. Lines 10-19 are the same as Listing 5.1. Line 20 opens the Recordset. Line 22 tests whether the EOF file condition is true. If it isn't, that means you have the record you are looking for.

Lines 24 and 25 call the Recordset PutCollect function to change the product name for this record to Bell Bottoms and Bass Guitars, which is obviously a much groovier title than Bell Bottom Hits. As an alternative to calling PutCollect, you could pass an array of field names and an array of values to the Update function. Line 26 calls Update to commit the change, and line 27 closes the Recordset.

This code will build with no errors or warnings. It will run with no exceptions or errors and will update the product name in the database. To ensure that this code runs properly, set some breakpoints when you execute this function. After you run it, you can open the Products table in Developer Studio and see whether the ProductName field for that record changed, as it should.

I mentioned locking earlier. The difference between optimistic and pessimistic locking consists primarily in the length of time the lock is held in the database. With pessimistic locking, the lock is initiated as soon as you modify the contents of a field in the record (in this case, by calling PutCollect). The lock is held until after you call Update or move the cursor off the record to commit the change.

With optimistic locking, the lock is initiated and held only when the change is committed to the database during the Update call. This means the lock is held for a very short time. However, if another user modifies the record during the time between your PutCollect call and your Update call, your Update call will fail (the database will reject your change to the record). If that happens, you need to retry to make the change.

Optimistic locking generally supports larger numbers of concurrent users than pessimistic locking. However, with optimistic locking, the users might have to try to commit their changes more than once.

The Delete Function

The ADO Delete function enables you to delete an existing record from the database. The idea is to open a Recordset that contains the record you want to edit. Position the cursor at the appropriate record (if it isn't already there). Then call the Delete function to remove the records from the database.

Use ClassWizard to add a handler function to the View class for the Recordset Delete menu; then add the code in Listing 5.3.

Listing 5.3.  Using the ADO Delete Function

 1:  void CADOMFC1View::OnRecordsetDelete()
 2:  {
 3:    RecordsetPtr pRecordSet;
 4:    CADOMFC1Doc * pDoc;
 5:    pDoc = GetDocument();
 6:
 7:    HRESULT hr;
 8:     bstr_t bstrQuery(
 9:      "SELECT * FROM Products WHERE PartNumber = '8TRACK-003'");
10:     variant_t vNull;
11:    vNull.vt = VT_ERROR;
12:    vNull.scode = DISP_E_PARAMNOTFOUND;
13:
14:    try
15:    {
16:      hr = pRecordSet.CreateInstance(_uuidof(Recordset));
17:      if (SUCCEEDED(hr))
18:      {
19:        pRecordSet->PutRefActiveConnection(pDoc->m_pConnection);
20:        hr = pRecordSet->Open(_variant_t(bstrQuery), vNull,
21:          adOpenForwardOnly, adLockOptimistic, adCmdText);
22:        if (!pRecordSet->GetadoEOF())
23:        {
24:          pRecordSet->Delete(adAffectCurrent);
25:          pRecordSet->Close();
26:        }
27:      }
28:    }
29:    catch( _com_error &e )
30:    {
31:      TRACE( "Error:%08lx.\n", e.Error());
32:      TRACE( "ErrorMessage:%s.\n", e.ErrorMessage());
33:      TRACE( "Source:%s.\n", (LPCTSTR) _bstr_t(e.Source()));
34:      TRACE( "Description:%s.\n", (LPCTSTR) _bstr_t(e.Description()));
35:    }
36:    catch(...)
37:    {
38:      TRACE( "\n*** Unhandled Exception ***\n" );
39:    }
40:  }

In Listing 5.3, lines 3-23 are identical to the code in Listing 5.2. You are opening a Recordset that contains the record that you added to the database in Listing 5.1. If the record is there, line 24 calls Delete and passes adAffectCurrent as a parameter so that only the current record is deleted from the database.

The code will build with no errors or warnings and run with no exceptions. It will delete the record that you added in the AddNew section earlier today.

The SQL INSERT, UPDATE, and DELETE Statements

So far, you have used the ADO Recordset to make changes to records in the database. You can also use SQL statements to make changes to records.

You've already learned about the SQL SELECT statement for retrieving data from the database. Now you will learn about three SQL statements that enable you to modify the data in a database. You could use the Execute statement in the ADO Connection object to send these statements to the database, but the easiest way to learn these statements is to use Developer Studio. You will now learn how to send these statements to the database by using Developer Studio.

The SQL INSERT Statement

The SQL INSERT statement enables you to add records to the database. The basic syntax looks like this:

INSERT INTO which table( list of columns ) VALUES( list of values )

Now you will use the INSERT statement to add a record to your database. Switch to the Data View and double-click the Products table to open it. Click the SQL button on the Query toolbar so that you can view and edit the SQL query. Change the query so that it looks like the one in Figure 5.3.

Figure 5.3 : The INSERT statement.

Visual Studio can help you by building the basic structure of the INSERT statement. If you click the Change Type button on the Query toolbar and then select Insert Values from the drop-down list, Visual Studio will create a basic INSERT statement for you. All you need to do is fill in the field names and their values.

Press the SQL Check button on the Query toolbar to verify the SQL syntax. The syntax should verify okay. Press the Run (!) button on the Query toolbar to execute the statement and insert the record.

After you run the query, a message box will appear, telling you that one record was affected. Also, the area below the SQL INSERT statement in the query window will become empty. As you know, the query window displays the records that the database returned as a result of the SQL statement. The window is empty because the SQL INSERT statement doesn't return data. To view the contents of the table, you must change the query back to a SQL SELECT statement (which does return data).

Edit the SQL query so that it looks like this:

SELECT Products.* FROM Products

Visual Studio can help you easily create the SELECT statement. If you click the Change Type button on the Query toolbar and then select Select from the drop-down list, Visual Studio will create most of a SELECT statement for you. All you need to do is add the table name and an asterisk after SELECT.

When you run the query, you should see all the records, including the one you just added to the table with your INSERT statement.

You can use an incomplete field list in an INSERT statement to add data to only some of the fields in the new record. For example, in the Products table, you could use a statement such as the one below to add a record without specifying the price.

INSERT INTO Products (partnumber, productname) VALUES('xxx', 'yyy')

You can perform more advanced operations by using the SQL INSERT statement, such as inserting multiple records that were retrieved from other tables with a SELECT statement. You will learn more about advanced INSERT operations tomorrow on Day 6, "Harnessing the Power of Relational Database Servers."

The SQL UPDATE Statement

The SQL UPDATE statement enables you to modify the data in existing records in the database. The basic syntax looks like this:

UPDATE which table SET which field = new value, which field = new value, ... WHERE condition

Now you will use the UPDATE statement to modify the record you inserted into your database. Change the query in the query window so that it looks like the one in Figure 5.4.

Figure 5.4 : The UPDATE statement.

Visual Studio can help you by building the basic structure of the UPDATE statement. If you click the Change Type button on the Query toolbar and then select Update from the drop-down list, Visual Studio will create a partial UPDATE statement for you. All you need to do is fill in the field names and their values and add a WHERE clause.

Press the SQL Check button on the Query toolbar to verify the SQL syntax. The syntax should verify okay. Press the Run (!) button on the Query toolbar to execute the statement and update the record.

A message box will appear, telling you that one record was affected. The portion of the query window that displays the data will be empty because the UPDATE statement doesn't return any data. Change the SQL statement to a SELECT statement so that you can view the records, including the one you just modified. The record should reflect the changes you specified in your UPDATE statement.

As you can see from Figure 5.4, you can use an incomplete field list in an UPDATE statement to modify only some of the fields in the record. You can also do more advanced operations with the UPDATE statement, such as replacing numeric data with the results of a mathematical operation. Also, you can use a SELECT statement in the WHERE clause to modify records that match very complex criteria. Tomorrow you will learn more about advanced operations with the UPDATE statement.

The SQL DELETE Statement

The SQL DELETE statement enables you to delete existing records in the database. The basic syntax looks like this:

DELETE FROM which table WHERE condition

Because you are deleting an entire record, the DELETE statement doesn't require you to specify a list of fields. You tell it which table you want to delete a record from and the criteria for the record.

Now you will use the DELETE statement to remove the record you inserted into your database. Change the query so that it looks like the one in Figure 5.5.

Figure 5.5 : The DELETE statement.

Visual Studio can help you by building the basic structure of the DELETE statement. If you click the Change Type button on the Query toolbar and then select Delete from the drop-down list, Visual Studio will create a DELETE statement for you. In fact, Visual Studio will remember the WHERE clause from the UPDATE statement and use that for your DELETE statement.

Press the SQL Check button on the Query toolbar to verify the SQL syntax. The syntax should verify okay. Press the Run (!) button on the Query toolbar to execute the statement and delete the record.

A message box will appear, telling you that one record was affected. The portion of the query window that displays the data will be empty because the DELETE statement doesn't return any data. Change the SQL statement to a SELECT statement so that you can view the records. The record you added should no longer exist in the Products table.

Summary

Today you learned two methods for manipulating records in a database. You learned how to manipulate records from C++ code by using the ADO Recordset member functions to insert, update, and delete records. You also learned how to manipulate records from SQL, using the INSERT, UPDATE, and DELETE statements.

Q&A

Workshop

The Workshop quiz questions test your understanding of today's material. (The answers appear in Appendix F, "Answers.") The exercises encourage you to apply the information you learned today to real-life situations.

Quiz

1. What is a forward-only cursor?
2. What function do you use to place a value in a field of the current record in an ADO Recordset?
3. What is wrong with this SQL statement?

DELETE FROM customers

4. What are the two arguments that you must pass to the ADO Recordset AddNew function?
5. What happens if you specify only one field/value pair in the SET clause of the SQL UPDATE function?

Exercises

1. Discover what happens in the Price field when you specify only the PartNumber and ProductName fields in a SQL INSERT statement for the Products table, like this:

INSERT INTO Products(PartNumber, ProductName)
VALUES ('xxx', 'yyy')

2. Modify the code in Listing 5.1 so that it doesn't specify a price for the new record.

© Copyright, Sams Publishing. All rights reserved.