公告

Recordset: Declaring a Class for a Table (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

The most common recordset class opens a single table. To declare a recordset class for a single table, use ClassWizard. In ClassWizard, choose each column you want by naming a corresponding recordset field data member. See the article ClassWizard: Creating a Recordset Class.

Other uses for recordsets include:

Joining two or more tables.
Containing the results of a predefined query.

Recordset: Creating and Closing Recordsets (ODBC)

项目
2006/09/15
3 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

To use a recordset, construct a recordset object, then call its Open member function to run the recordset’s query and select records. When you finish with the recordset, close and destroy the object.

This article explains:

When and how to create a recordset object .
When and how you can qualify the recordset’s behavior by parameterizing, filtering, sorting, or locking it.
When and how to close a recordset object.

Creating Recordsets at Run Time

Before you can create recordset objects in your program, you typically write application-specific recordset classes. For more information on this preliminary step, see the article ClassWizard: Creating a Recordset Class.

Open a dynaset or snapshot object when you need to select records from a data source. The type of object to create depends on what you need to do with the data in your application and on what your ODBC driver supports. For more information, see the articles Dynaset and Snapshot.

To open a recordset

Construct an object of your CRecordset-derived class.

You can construct the object on the heap or on the stack frame of a function.
Optionally modify the default recordset behavior. For the available options, see Setting Recordset Options.
Call the object’s member function.

In the constructor, pass a pointer to a CDatabase object, or pass NULL to use a temporary database object that the framework will construct and open based on the connection string returned by the member function. The CDatabase object may or may not already be connected to a data source.

The call to Open uses SQL to select records from the data source. The first record selected (if any) is the “current record”. The values of this record’s fields are stored in the recordset object’s field data members. If any records were selected, both the IsBOF and IsEOF member functions return 0.

In your call, you can:

Specify whether the recordset is a dynaset or snapshot. Recordsets open as snapshots by default. Or you can specify a forward-only recordset, which allows only forward scrolling, one record at a time.

By default, a recordset uses the default type stored in the CRecordset data member m_nDefaultType. Wizards write code to initialize m_nDefaultType to the recordset type you choose in the wizard. Rather than accepting this default, you can substitute another recordset type.
Specify a string to replace the default SQL SELECT statement that the recordset constructs.
Specify whether the recordset is read-only or append-only. Recordsets allow full updating by default, but you can limit that to adding new records only or you can disallow all updates.

The following example shows how to open a read-only snapshot object of class CStudentSet, an application-specific class:

// Construct the snapshot object
CStudentSet rsStudent( NULL );
// Set options if desired, then open the recordset
if(!rsStudent.Open(CRecordset::snapshot, NULL, CRecordset::readOnly))
    return FALSE;
// Use the snapshot to operate on its records...

After you call Open, use the member functions and data members of the object to work with the records. In some cases, you may want to requery or refresh the recordset to include changes that have occurred on the data source. See the article Recordset: Requerying a Recordset (ODBC).

****Tip ****The connect string you use during development might not be the same connect string that your eventual users need. For ideas about generalizing your application in this regard, see the article Data Source: Managing Connections (ODBC).

Setting Recordset Options

After you construct your recordset object but before you call Open to select records, you may want to set some options to control the recordset’s behavior. For all recordsets, you can:

Specify a filter to constrain record selection.
Specify a sort order for the records.
Specify parameters so you can select records using information obtained or calculated at run time.

You can also set the following option if conditions are right:

If the recordset is updatable and supports locking options, specify the locking method used for updates.

Important To affect record selection, you must set these options before you call the Open member function.

Closing a Recordset

When you finish with your recordset, you must dispose of it and deallocate its memory.

To close a recordset

Call its member function.
Destroy the recordset object.

If you declared it on the stack frame of a function, the object is destroyed automatically when the object goes out of scope. Otherwise, use the delete operator.

Close frees the recordset’s HSTMT handle. It doesn’t destroy the C++ object.

Recordset: Scrolling (ODBC)

项目
2006/09/15
3 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

After you open a recordset, you need to access the records to display values, do calculations, generate reports, and so on. Scrolling lets you move from record to record within your recordset.

This article explains:

How to scroll from one record to another in a recordset
Under what circumstances scrolling is and is not supported

Scrolling from One Record to Another

Class CRecordset provides the Move member functions for scrolling within a recordset. These functions move the current record by rowsets. If you have implemented bulk row fetching, a Move operation repositions the recordset by the size of the rowset. If you have not implemented bulk row fetching, a call to a Move function repositions the recordset by one record each time. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

****Note ****When moving through a recordset, deleted records may not be skipped. See the member function for details.

In addition to the Move functions, CRecordset provides member functions for checking whether you have scrolled past the end or ahead of the beginning of your recordset.

To determine whether scrolling is possible in your recordset, call the CanScroll member function.

To scroll

Forward one record or one rowset: call the member function.
Backward one record or one rowset: call the member function.
To the first record in the recordset: call the member function.
To the last record in the recordset or to the last rowset: call the member function.
N records relative to the current position: call the member function.

To test for the end or the beginning of the recordset

Have you scrolled past the last record? Call the member function.
Have you scrolled ahead of the first record (moving backward)? Call the member function.

The following code example uses IsBOF and IsEOF to detect the limits of a recordset when scrolling in either direction.

// Open a recordset; first record is current
CCustSet rsCustSet( NULL );
rsCustSet.Open( );

if( rsCustSet.IsBOF( ) )
    return;
    // The recordset is empty

// Scroll to the end of the recordset, past
// the last record, so no record is current
while ( !rsCustSet.IsEOF( ) )
    rsCustSet.MoveNext( );

// Move to the last record
rsCustSet.MoveLast( );

// Scroll to beginning of the recordset, before
// the first record, so no record is current
while( !rsCustSet.IsBOF( ) )
    rsCustSet.MovePrev( );

// First record is current again
rsCustSet.MoveFirst( );

IsEOF returns a nonzero value if the recordset is positioned past the last record. IsBOF returns a nonzero value if the recordset is positioned ahead of the first record (before all records). In either case, there is no current record to operate on. If you call MovePrev when IsBOF is already TRUE, or call MoveNext when IsEOF is already TRUE, the framework throws a CDBException. You can also use IsBOF and IsEOF to check for an empty recordset.

For more information about recordset navigation, see the article Recordset: Bookmarks and Absolute Positions (ODBC).

When Scrolling Is Supported

As originally designed, SQL provided only forward scrolling, but ODBC extends scrolling capabilities. The available level of support for scrolling depends on the ODBC driver(s) your application will work with, your driver’s ODBC API conformance level, and whether the ODBC Cursor Library is loaded into memory. For more information, see the articles ODBC and ODBC: The ODBC Cursor Library.

****Tip ****You can control whether the cursor library is used. See the bUseCursorLib and dwOptions parameters to .

****Note ****Unlike the MFC DAO classes, the MFC ODBC classes do not provide a set of Find functions for locating the next (or previous) record that meets specified criteria.

Recordset: Bookmarks and Absolute Positions (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

When navigating through a recordset, you often need a way of returning to a particular record. A record's bookmark and absolute position provide two such methods.

This article explains:

How to use bookmarks.
How to set the current record using absolute positions.

Bookmarks in MFC ODBC

A bookmark uniquely identifies a record. When you navigate through a recordset, you can't always rely on the absolute position of a record since records can be deleted from the recordset. The reliable way to keep track of the position of a record is to use its bookmark. Class CRecordset supplies member functions for:

Getting the bookmark of the current record, so you can save it in a variable ().
Moving quickly to a given record by specifying its bookmark, which you saved earlier in a variable ().

The following example illustrates how to use these member functions to mark the current record and later return to it:

// rs is a CRecordset or
// CRecordset-derived object

CDBVariant varRecordToReturnTo;
rs.GetBookmark( varRecordToReturnTo );

// More code in which you
// move to other records

rs.SetBookmark( varRecordToReturnTo );

You do not need to extract the underlying data type from the object. Simply assign the value with GetBookmark and return to that bookmark with SetBookmark.

****Note ****Depending on your ODBC driver and recordset type, bookmarks may not be supported. You can easily determine whether bookmarks are supported by calling . Furthermore, if bookmarks are supported, you must explicitly choose to implement them by specifying the CRecordset::useBookmarks option in the member function. You should also check the persistence of bookmarks after certain recordset operations. For example, if you Requery a recordset, bookmarks may no longer be valid. Call to check whether you can safely call SetBookmark.

Absolute Positions in MFC ODBC

Besides bookmarks, class CRecordset allows you to set the current record by specifying an ordinal position. This is called absolute positioning.

****Note ****Absolute positioning is not available on forward-only recordsets. For more information about forward-only recordsets, see the article Recordset (ODBC).

To move the current record pointer using absolute position, call . When you pass a value to SetAbsolutePosition, the record corresponding to that ordinal position becomes the current record.

****Important ****The absolute position of a record is potentially unreliable. If the user deletes records from the recordset, the ordinal position of any subsequent record will change. Bookmarks are the recommended method for moving the current record. See Bookmarks in MFC ODBC.

For more information about recordset navigation, see the article Recordset: Scrolling (ODBC).

Recordset: Parameterizing a Recordset (ODBC)

项目
2006/09/15
6 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

Sometimes you’d like to be able to select records at run time, using information you’ve calculated or obtained from your end-user. Recordset parameters let you accomplish that goal.

This article explains:

The purpose of a parameterized recordset.
When and why you might want to parameterize a recordset.
How to declare parameter data members in your recordset class.
How to pass parameter information to a recordset object at run time.

Parameterized Recordsets

A parameterized recordset lets you pass parameter information at run time. This has two valuable effects:

It may result in better execution speed.
It lets you build a query at run time, based on information not available to you at design time, such as information obtained from your user or calculated at run time.

When you call Open to run the query, the recordset uses the parameter information to complete its SQL SELECT statement. You can parameterize any recordset.

When to Use Parameters

Typical uses for parameters include:

Passing run-time arguments to a predefined query.

To pass parameters to a stored procedure, you must specify a complete custom ODBC CALL statement — with parameter placeholders — when you call Open, overriding the recordset’s default SQL statement. See in the Class Library Reference and the articles SQL: Customizing Your Recordset’s SQL Statement (ODBC) and Recordset: Declaring a Class for a Predefined Query (ODBC).
Efficiently performing numerous requeries with different parameter information.

For example, each time your end-user looks up information for a particular student in the student registration database, you can specify the student’s name or ID as a parameter obtained from the user. Then, when you call your recordset’s Requery member function, the query selects only that student’s record.

Your recordset’s filter string, stored in m_strFilter, might look like this:
```
"StudentID = ?"
```
Suppose you obtain the student ID in the variable strInputID. When you set a parameter to strInputID (for example, the student ID 100) the value of the variable is bound to the parameter placeholder represented by the “?” in the filter string.

Assign the parameter value as follows:
```
strInputID = "100";
...
m_strParam = strInputID;
```
Note that you would not want to set up a filter string this way:
```
m_strFilter = "StudentID = 100";   // 100 is incorrectly quoted
                                   // for some drivers
```
For a discussion of how to use quotes correctly for filter strings, see the article Recordset: Filtering Records (ODBC).

The parameter value is different each time you requery the recordset for a new student ID.

****Tip ****Using a parameter is more efficient than simply a filter. For a parameterized recordset, the database must process an SQL SELECT statement only once. For a filtered recordset without parameters, the SELECT statement must be processed each time you Requery with a new filter value.

For more information about filters, see the article Recordset: Filtering Records (ODBC).

Parameterizing Your Recordset Class

****Note ****This section applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, implementing parameters is a similar process. For more information, see the article Recordset: Fetching Records in Bulk (ODBC).

Before you create your recordset class, determine what parameters you need, what their data types are, and how the recordset will use them.

To parameterize a recordset class

Run ClassWizard and create the class. See the article ClassWizard: Creating a Recordset Class.
Specify field data members for the recordset’s columns.
After ClassWizard writes the class to a file in your project, go to the .H file and manually add one or more parameter data members to the class declaration. The addition might look something like the following example, part of a snapshot class designed to answer the query “Which students are in the senior class?”
```
class CStudentSet : public CRecordset
{
// Field/Param Data
    //{{AFX_FIELD(CStudentSet, CRecordset)
    CString m_strFirstName;
    CString m_strLastName;
    CString m_strStudentID;
    CString m_strGradYear;
    //}}AFX_FIELD

    CString m_strGradYrParam;
};
```
ClassWizard writes field data members inside the “//{{AFX_FIELD” comment brackets. You add your parameter data members outside the comment brackets. The convention is to append the word “Param” to each name.
Modify the member function definition in the .CPP file. Add an RFX function call for each parameter data member you added to the class. For information on writing your RFX functions, see the article Record Field Exchange: How RFX Works. Precede the RFX calls for the parameters with a single call to
```
pFX->SetFieldType( CFieldExchange::param );
// RFX calls for parameter data members
```
In the constructor of your recordset class, increment the count of parameters, m_nParams.

For information, see The Recordset Constructor in the article Record Field Exchange: Working with the Wizard Code.
When you write the code that creates a recordset object of this class, place a “?” (question mark) symbol in each place in your SQL statement string(s) where a parameter is to be replaced.

At run time, “?” placeholders are filled, in order, by the parameter values you pass. The first parameter data member set after the call replaces the first “?” in the SQL string, the second parameter data member replaces the second “?”, and so on.

****Important ****Parameter order is important: the order of RFX calls for parameters in your DoFieldExchange function must match the order of the parameter placeholders in your SQL string.

****Tip ****The most likely string to work with is the string you specify (if any) for the class’s data member, but some ODBC drivers may allow parameters in other SQL clauses.

Passing Parameter Values at Run Time

You must specify parameter values before you call Open (for a new recordset object) or Requery (for an existing one).

To pass parameter values to a recordset object at run time

Construct the recordset object.
Prepare a string or strings, such as the m_strFilter string, containing the SQL statement, or part(s) of it. Put “?” placeholders where the parameter information is to go.
Assign a run-time parameter value to each parameter data member of the object.
Call the Open member function (or Requery, for an existing recordset).

For example, suppose you want to specify a filter string for your recordset using information obtained at run time. Assume you have constructed a recordset of class CStudentSet earlier — called rsStudents — and now want to requery it for a particular kind of student information.

// Set up a filter string with
// parameter placeholders
rsStudents.m_strFilter = "GradYear <= ?";

// Obtain or calculate parameter values
// to pass--simply assigned here
CString strGradYear = GetCurrentAcademicYear( );

// Assign the values to parameter data members
rsStudents.m_strGradYrParam = strGradYear;

// Run the query
if( !rsStudents.Requery( ) )
    return FALSE;

The recordset contains records for those students whose records meet the conditions specified by the filter, which was constructed from run-time parameters. In this case, the recordset contains records for all senior students.

****Note ****If needed, you can set the value of a parameter data member to Null, using . You can likewise check whether a parameter data member is Null, using .

Recordset: Adding, Updating, and Deleting Records (ODBC)

项目
2006/09/15
6 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

****Note ****You can now add records in bulk more efficiently. For information, see the article Recordset: Adding Records in Bulk (ODBC).

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

Updatable snapshots and dynasets allow you to add, edit (update), and delete records. This article explains:

How to determine whether your recordset is updatable.
How to add a new record.
How to edit an existing record.
How to delete a record.

For more information about how updates are carried out and how your updates appear to other users, see the article Recordset: How Recordsets Update Records (ODBC). Normally, when you add, edit, or delete a record, the recordset changes the data source immediately. You can instead batch groups of related updates into transactions. If a transaction is in progress, the update doesn’t become final until you commit the transaction. This allows you to take back or roll back the changes. For information about transactions, see the article Transaction (ODBC).

The following table summarizes the options available for recordsets with different update characteristics.

Recordset Read/Update Options

Type	Read	Edit records	Delete records	Add new (append)
Read-only	Y	N	N	N
Append-only	Y	N	N	Y
Fully updatable	Y	Y	Y	Y

Determining Whether Your Recordset is Updatable

A recordset object is updatable if the data source is updatable, and you opened the recordset as updatable. Its updatability also depends on the SQL statement you use, the capabilities of your ODBC driver, and whether the ODBC Cursor Library is in memory or not. You can’t update a read-only recordset or data source.

To determine whether your recordset is updatable

Call the recordset object’s member function.

CanUpdate returns a nonzero value if the recordset is updatable.

By default, recordsets are fully updatable (you can perform AddNew, Edit, and Delete operations). But you can also use the option to open updatable recordsets. A recordset opened this way allows only the addition of new records with AddNew. You can’t edit or delete existing records. You can test whether a recordset is open only for appending by calling the member function. CanAppend returns a nonzero value if the recordset is either fully updatable or open only for appending.

The following code shows how you might use CanUpdate for a recordset object called rsStudentSet:

if( !rsStudentSet.Open( ) )
    return FALSE;
if( !rsStudentSet.CanUpdate( ) )
{
    AfxMessageBox( "Unable to update the Student recordset." );
    return;
}

****Caution ****When you prepare to update a recordset by calling Update, take care that your recordset includes all columns making up the primary key of the table (or all of the columns of any unique index on the table). In some cases, the framework can use only the columns selected in your recordset to identify which record in your table to update. Without all the necessary columns, multiple records may be updated in the table, possibly damaging the referential integrity of the table. In this case, the framework will throw exceptions when you call Update.

Adding a Record to a Recordset

You can add new records to a recordset if its member function returns a nonzero value.

To add a new record to a recordset

Make sure the recordset is appendable.
Call the recordset object’s member function.

AddNew prepares the recordset to act as an edit buffer. All field data members are set to the special value Null and marked as unchanged so only changed (“dirty”) values will be written to the data source when you call .
Set the values of the new record’s field data members.

Assign values to the field data members. Those you don’t assign will not be written to the data source.
Call the recordset object’s Update member function.

Update completes the addition by writing the new record to the data source. For what happens if you fail to call Update, see the article Recordset: How Recordsets Update Records (ODBC).

For information about how adding records works and about when added records are visible in your recordset, see the article Recordset: How AddNew, Edit, and Delete Work (ODBC).

The following example shows how to add a new record:

if( !rsStudent.Open( ) )
    return FALSE;
if( !rsStudent.CanAppend( ) )
    return FALSE;                      // no field values were set
rsStudent.AddNew( );
rsStudent.m_strName = strName;
rsStudent.m_strCity = strCity;
rsStudent.m_strStreet = strStreet;
if( !rsStudent.Update( ) )
{
    AfxMessageBox( "Record not added; no field values were set." );
    return FALSE;
}

For additional information, see Adding a Record in the article Recordset: How AddNew, Edit, and Delete Work.

****Tip ****To cancel an AddNew or Edit call, simply make another call to AddNew or Edit or call Move with the AFX_MOVE_REFRESH parameter. Data members will be reset to their previous values and you will still be in Edit or Add mode.

Editing a Record in a Recordset

You can edit existing records if your recordset’s member function returns a nonzero value.

To edit an existing record in a recordset

Make sure the recordset is updatable.
Scroll to the record you want to update.
Call the recordset object’s member function.

Edit prepares the recordset to act as an edit buffer. All field data members are marked so that the recordset can tell later whether they were changed. The new values for changed field data members are written to the data source when you call .
Set the values of the new record’s field data members.

Assign values to the field data members. Those you don’t assign values will remain unchanged.
Call the recordset object’s Update member function.

Update completes the edit by writing the changed record to the data source. For what happens if you fail to call Update, see the article Recordset: How Recordsets Update Records (ODBC).

After you edit a record, the edited record remains the current record.

The following example shows an Edit operation. It assumes the user has moved to a record he or she wants to edit.

rsStudent.Edit( );
rsStudent.m_strStreet = strNewStreet;
rsStudent.m_strCity = strNewCity;
rsStudent.m_strState = strNewState;
rsStudent.m_strPostalCode = strNewPostalCode;
if( !rsStudent.Update( ) )
{
    AfxMessageBox( "Record not updated; no field values were set." );
    return FALSE;
}

For more information, see Editing an Existing Record in the article Recordset: How AddNew, Edit, and Delete Work.

Deleting a Record from a Recordset

You can delete records if your recordset’s member function returns a nonzero value.

To delete a record

Make sure the recordset is updatable.
Scroll to the record you want to update.
Call the recordset object’s member function.

Delete immediately marks the record as deleted, both in the recordset and on the data source.

Unlike AddNew and Edit, Delete has no corresponding Update call.
Scroll to another record.

****Important ****When moving through the recordset, deleted records may not be skipped. See the member function for details.

The following example shows a Delete operation. It assumes the user has moved to a record he or she wants to delete. After Delete is called, it’s important to move to a new record.

rsStudent.Delete( );
rsStudent.MoveNext( );

For more information about the effects of the AddNew, Edit, and Delete member functions, see the article Recordset: How Recordsets Update Records (ODBC).

Recordset: Locking Records (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains:

The kinds of record locking available.
How to lock records in your recordset during updates.

When you use a recordset to update a record on the data source, your application can lock the record so no other user can update the record at the same time. The state of a record updated by two users at “the same time” is undefined unless the system can guarantee that two users can’t update a record simultaneously.

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you have implemented bulk row fetching, some of the information does not apply. For example, you cannot call the Edit and Update member functions. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

Record-Locking Modes

The database classes provide two :

Optimistic locking (the default)
Pessimistic locking

Updating a record occurs in three steps:

You begin the operation by calling the member function.
You change the appropriate fields of the current record.
You end the operation — and normally commit the update — by calling the member function.

Optimistic locking locks the record on the data source only during the Update call. If you use optimistic locking in a multiuser environment, the application should handle an Update failure condition. Pessimistic locking locks the record as soon as you call Edit and doesn’t release it until you call Update (failures are indicated via the CDBException mechanism, not by a value of FALSE returned by Update). Pessimistic locking has a potential performance penalty for other users, since concurrent access to the same record may have to wait until completion of your application’s Update process.

Locking Records in Your Recordset

If you want to change a recordset object’s locking mode from the default, you must change the mode before you call Edit.

To change the current locking mode for your recordset

Call the member function, specifying either CRecordset::pessimistic or CRecordset::optimistic.

The new locking mode remains in effect until you change it again or the recordset is closed.

****Note ****Relatively few ODBC drivers currently support pessimistic locking.

Recordset: Performing a Join (ODBC)

项目
2006/09/15
5 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains:

What a join is.
How to perform a join of multiple tables.

What a Join Is

The join operation — a common data-access task — lets you work with data from more than one table using a single recordset object. Joining two or more tables yields a recordset that can contain columns from each table, but appears as a single table to your application. Sometimes the join uses all columns from all tables, but sometimes the SQL SELECT clause in a join uses only some of the columns from each table. The database classes support read-only joins but not updatable joins.

The key to a join operation is one or more columns that the tables have in common. For example, suppose there is a “CourseID” column in both the Course table and the Section table for an application such as the ENROLL tutorial. In the Course table, the CourseID column contains a unique ID value for each possible course. In the Section table, the CourseID column probably doesn’t contain unique values, since each course usually has more than one section.

To select records containing columns from joined tables, you need the following items:

A table list containing the names of all tables being joined.
A column list containing the names of all participating columns. Columns with the same name but from different tables are qualified by the table name.
A filter (SQL WHERE clause) that specifies the column(s) on which the tables are joined. This filter takes the form “Table1.KeyCol = Table2.KeyCol” and actually accomplishes the join. For the ENROLL example above, the filter is:
```
Course.CourseID = Section.CourseID
```

Performing the Join

The following procedure shows a join of two tables but can apply to joins of any number of tables (all on the same data source). The procedure involves first binding columns from multiple tables with ClassWizard, then directly modifying source code to complete the join.

Binding the Table Columns

To bind columns from both tables to a single recordset

Use ClassWizard to create a recordset class for the join. In ClassWizard choose Data Sources to open the Data Sources dialog box and bind columns from the first table to recordset field data members.

See the article ClassWizard: Creating a Recordset Class.
Choose ClassWizard’s Update Columns button to open the Data Sources dialog box a second time.
Select a data source and choose OK to close the Data Sources dialog box.
In the Tables dialog box, select the name of the second table and choose OK to close the dialog box.
Bind columns from the second table to additional recordset field data members.

If any column names from the second table duplicate column names from the first table, be sure to give the corresponding recordset field data members unique names. For example, if you’re joining Instructor and Section tables, each table might contain a column named RoomNo; you might bind one column to m_strInstrOffice and the other to m_strClassRoom.
Close ClassWizard.

****Note ****When creating CRecordset-derived or CDaoRecordset-derived classes with ClassWizard, be careful when selecting multiple tables or queries. Selecting multiple tables or queries will result in the construction of a join query without any restriction on how to perform the join (called a cross-product or cartesian product join). You may want to specify a filter using or (resulting in MFC building an SQL WHERE clause) before the recordset is opened. This will constrain the number of records in the result set. This is especially necessary when using the ODBC Cursor Library, since the Cursor Library may create a large temporary file for result sets with many records.

Modifying the Source Files

Once you create the recordset class with ClassWizard, you must customize two parts of the class code. First, edit the class’s table list, then qualify any columns with the same name but from different tables. You’ll need to edit the calls in your DoFieldExchange override to insert table names.

For example, the student registration database for the MFC Tutorial sample contains Instructor and Section tables. The Instructor table contains the following columns:

InstructorID
Name
RoomNo (the instructor’s office)

The Section table contains the following columns:

InstructorID
Schedule
RoomNo (where the class is held)
SectionNo
CourseID
Capacity (maximum size of the section)

To modify the recordset’s table list

Rewrite the recordset’s GetDefaultSQL member function to return a string containing a comma-delimited list of table names.

For example, if your CJoinSet recordset joins a Course table to a Section table, you should rewrite your GetDefaultSQL function to look something like this:

CString CJoinSet::GetDefaultSQL()
{
    return "SECTION, INSTRUCTOR";
}

****Tip ****As an alternative, you can pass a string containing a comma-delimited list of table names in the lpszSQL parameter when you call the recordset’s Open member function. The string has the same form as the string returned in the example above.

To qualify columns with the same name from different tables

Edit the RFX or Bulk RFX function calls in the recordset’s or member function.

For each duplicate column name, edit the second parameter in the RFX or Bulk RFX call to prefix a table name to the column name already there. Separate the table name and the column name with a period.

For example, because CJoinSet binds a RoomNo column from each table, you must modify the two RFX calls for these columns as shown in the following code:

void CJoinSet::DoFieldExchange(CFieldExchange* pFX)
{
    //{{AFX_FIELD_MAP(CJoinSet)
    SetFieldType(pFX, CFieldExchange::outputColumn);
    RFX_Text(pFX, "Section.RoomNo", m_strClassRoom);
    RFX_Text(pFX, "Instructor.RoomNo", m_strInstructorOffice);
    // ...
    //}}AFX_FIELD_MAP
}

In the second parameter of each RFX function call above, the name RoomNo is prefixed by the table name. The two items are separated by a period.

Setting the Join Conditions with a Filter

When you construct a CJoinSet object in your program, set its filter to specify which columns constitute the join. Then call the recordset’s Open member function as shown in the following example, which joins the Instructor and Section tables on their common InstructorID column:

CJoinSet ssJoin( NULL );
ssJoin.m_strFilter = "Instructor.InstructorID = Section.InstructorID";
if( !ssJoin.Open( ) )
    return FALSE;        // recordset could not be opened

The filter supplies the connection between two columns that makes it possible to view two tables as if they were one.

You can join more than two tables in the same way by equating multiple pairs of columns, each pair joined by the SQL keyword AND.

Recordset: Declaring a Class for a Predefined Query (ODBC)

项目
2006/09/15
5 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains how to create a recordset class for a predefined query (sometimes called a “stored procedure,” as in Microsoft SQL Server).

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If bulk row fetching is implemented, the process is very similar. To understand the differences between recordsets that implement bulk row fetching and those that do not, see the article Recordset: Fetching Records in Bulk (ODBC).

Some database management systems (DBMSs) allow you to create a predefined query and call it from your programs like a function. The query has a name, may or may not take parameters, and may or may not return records. The procedure in this article describes how to call a predefined query that returns records (and perhaps takes parameters).

The database classes don’t support updating predefined queries. The difference between a snapshot predefined query and a dynaset predefined query is not updatability but whether changes made by other users (or other recordsets in your program) are visible in your recordset.

****Tip ****You don’t need a recordset to call a predefined query that doesn’t return records. Prepare the SQL statement as described below, but execute it by calling the CDatabase member function .

You can create a single recordset class to manage calling a predefined query, but you must do some of the work yourself. ClassWizard doesn’t support creating a class specifically for this purpose.

To create a class for calling a predefined query (stored procedure)

Use ClassWizard to create a recordset class for the table that contributes the most columns returned by the query. This gives you a head start.
Manually add field data members for any columns of any tables that the query returns but that ClassWizard didn’t create for you. Add them outside the “//{{AFX_FIELD” comments.

For example, if the query returns three columns each from two additional tables, add six field data members (of the appropriate data types) to the class.
Manually add RFX function calls in the DoFieldExchange member function of the class, one corresponding to the data type of each added field data member.
```
Add these function calls outside the “//{{AFX_FIELD_MAP” comments. Immediately before these RFX calls, call , as shown here:
pFX->SetFieldType( CFieldExchange::outputColumn );
```
****Note ****You must know the data types and the order of columns returned in the result set. The order of RFX function calls in DoFieldExchange must match the order of result set columns.
Manually add initializations for the new field data members in the recordset class constructor.

You must also increment the initialization value for the data member. ClassWizard writes the initialization, but it only covers the field data members it adds for you. Put the increment statement outside the comment brackets. For example:
```
m_nFields += 6;
//{{AFX_FIELD(CDelinquents, CRecordset)
...
//}}AFX_FIELD
```
Some data types shouldn’t be initialized here, for example, CLongBinary or byte arrays.
If the query takes parameters, add a parameter data member for each parameter, an RFX function call for each, and an initialization for each.
You must increment m_nParams for each added parameter, as you did m_nFields for added fields in step 4 above. See the article Recordset: Parameterizing a Recordset (ODBC) for details.
Manually write an SQL statement string with the following form:
```
{CALL proc-name [(? [, ?]...)]}
```
where CALL is an ODBC keyword, proc-name is the name of the query as it is known on the data source, and the “?” items are placeholders for the parameter values you supply to the recordset at run time (if any). The following example prepares a placeholder for one parameter:
```
CString mySQL = "{CALL Delinquent_Accts (?)}";
```
In the code that opens the recordset, first set the values of the recordset’s parameter data members, then call the Open member function, passing your SQL string for the lpszSQL parameter. Or instead, replace the string returned by the GetDefaultSQL member function in your class.

The following examples illustrate the procedure for calling a predefined query, named Delinquent_Accts, which takes one parameter for a sales district number. This query returns three columns: Acct_No, L_Name, Phone. All columns are from the Customers table.

The recordset below specifies field data members for the columns the query returns and a parameter for the sales district number requested at run time.

class CDelinquents : public CRecordset
{
// Field/Param Data
    //{{AFX_FIELD(CDelinquents, CRecordset)
    LONG m_lAcct_No;
    CString m_strL_Name;
    CString m_strPhone;
    //}}AFX_FIELD
    LONG m_lDistParam;
    // ...
};

This class declaration is as ClassWizard writes it, except for the m_lDistParam member added manually outside the “//{{AFX_FIELD” comment. Other members below the comments aren’t shown here.

The next example shows the initializations for the data members in the CDelinquents constructor. You add the two lines outside the comment brackets.

CDelinquents::CDelinquents(CDatabase* pdb)
   : CRecordset(pdb)
{
    //{{AFX_FIELD_INIT(CDelinquents)
    m_lAcct_No = 0;
    m_strL_Name = "";
    m_strPhone = "";
    m_nFields = 3;
    //}}AFX_FIELD_INIT
    m_nParams = 1;
    m_lDistParam = 0;
}

Note the initializations for and . ClassWizard initializes m_nFields; you initialize m_nParams.

The next example shows the RFX functions in CDelinquents::DoFieldExchange:

void CDelinquents::DoFieldExchange(CFieldExchange* pFX)
{
//{{AFX_FIELD_MAP(CDelinquents)
    pFX->SetFieldType(CFieldExchange::outputColumn);
    RFX_Long(pFX, "Acct_No", m_lAcct_No);
    RFX_Text(pFX, "L_Name", m_strL_Name);
    RFX_Text(pFX, "Phone", m_strPhone);
//}}AFX_FIELD_MAP
    pFX->SetFieldType(CFieldExchange::param);
    RFX_Long(pFX, "Dist_No", m_lDistParam);
}

Besides making the RFX calls for the three returned columns, this code manages binding the parameter you pass at run time. The parameter is keyed to the Dist_No (district number) column.

The next example shows how to set up the SQL string and how to use it to open the recordset.

// Construct a CDelinquents recordset object
CDelinquents rsDel( NULL );
CString strSQL = "{CALL Delinquent_Accts (?)}"
// Specify a parameter value (obtained earlier from the user)
rsDel.m_lDistParam = lDistrict;
// Open the recordset and run the query
if( rsDel.Open( CRecordset::snapshot, strSQL ) )
    // Use the recordset ...

This code constructs a snapshot, passes it a parameter obtained earlier from the user, and calls the predefined query. When the query runs, it returns records for the specified sales district. Each record contains columns for the account number, customer’s last name, and customer’s phone number.

****Tip ****You might want to handle a return value (output parameter) from a stored procedure. For more information and an example, see .

Recordset: Requerying a Recordset (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains how you can use a recordset object to “requery” — refresh — itself from the database, and when you might want to do that with the member function.

The principal reasons for requerying a recordset are to:

Bring the recordset up to date with respect to records added by you or by other users and records deleted by other users (those you delete are already reflected in the recordset).
Refresh the recordset based on changing parameter values.

Bringing the Recordset Up to Date

Frequently you will want to requery your recordset object to bring it up to date. In a multiuser database environment, other users can make changes to the data during the life of your recordset. For more information about when your recordset reflects changes made by other users and when other users’ recordsets reflect your changes, see the articles Recordset: How Recordsets Update Records (ODBC) and Dynaset.

Requerying Based on New Parameters

Another frequent — and equally important — use of is to select a new set of records based on changing parameter values. For example, Step 2 in the ENROLL tutorial application illustrates using a combo box in a record view to select from a list of all available college courses. When the user selects a different course from the combo box, ENROLL requeries a Section table to select only those class sections for the course the user chose in the combo box. See the CSectionForm::OnSelendokCourseList member function in in Tutorials.

****Tip ****Query speed is probably significantly faster if you call Requery with changing parameter values than if you call Open again.

Requerying Dynasets vs. Snapshots

Because dynasets are meant to present a set of records with dynamic, up-to-date data, you’ll want to requery dynasets often if you want to reflect other users’ additions. Snapshots, on the other hand, are useful because you can safely rely on their static contents while you prepare reports, calculate totals, and so on. Still, you may sometimes want to requery a snapshot as well. In a multiuser environment, snapshot data may lose synchronization with the data source as other users change the database.

To requery a recordset object

Call the member function of the object.

Alternatively, you can simply close and reopen the original recordset. In either case, the new recordset represents the current state of the data source.

For an example, see the article Record Views: Filling a List Box from a Second Recordset.

****Tip ****To optimize Requery performance, avoid changing the recordset’s filter or sort. Change only the parameter value before calling Requery.

If the Requery call fails, you can retry the call, otherwise, your application should terminate gracefully. A call to Requery or Open might fail for any of a number of reasons. Perhaps a network error occurs; or, during the call, after the existing data is released but before the new data is obtained, another user might get exclusive access; or the table on which your recordset depends could be deleted.

Recordset: Dynamically Binding Data Columns (ODBC)

项目
2006/09/15
11 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

Recordsets manage binding table columns that you specify at design time, but there are cases when you may want to bind columns that were unknown to you at design time. This article explains:

When you might want to bind columns dynamically to a recordset.
How to bind columns dynamically at run time.

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. The techniques described generally are not recommended if you are using bulk row fetching. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

When You Might Bind Columns Dynamically

At design time, ClassWizard creates recordset classes based on the known tables and columns on your data source. Databases can change between when you design them and later when your application uses those tables and columns at run time. You or another user might add or drop a table or add or drop columns from a table that your application’s recordset relies upon. This probably isn’t a concern for all data-access applications, but if it is for yours, how can you cope with changes in the database schema, other than by redesigning and recompiling? The purpose of this article is to answer that question.

This article describes the most common case in which you might bind columns dynamically — having begun with a recordset based on a known database schema, you want to handle additional columns at run time. The article further assumes that the additional columns map to CString field data members, the most common case, although suggestions are supplied to help you manage other data types.

With a small amount of extra code, you can:

Determine what columns are available at run time.
Bind additional columns to your recordset dynamically, at run time.

Your recordset still contains data members for the columns you knew about at design time. It also contains a small amount of extra code that dynamically determines whether any new columns have been added to your target table and, if so, binds these new columns to dynamically allocated storage (rather than to recordset data members).

This article doesn’t cover other dynamic binding cases, such as dropped tables or columns. For those, you’ll need to use ODBC API calls more directly. See the ODBC SDK Programmer’s Reference on the MSDN Library CD.

Example code for this article comes from the MFC Database samples and .

How to Bind Columns Dynamically

To bind columns dynamically in a case like that of the DYNABIND example, you must know (or be able to determine) the names of the additional columns. You must also allocate storage for the additional field data members, specify their names and their types, and specify the number of columns you’re adding.

The following discussion mentions two different recordsets. The first is the main recordset that selects records from the target table. The second is a special column recordset used to get information about the columns in your target table.

The General Process

At the most general level, you follow these steps:

Construct your main recordset object.

Optionally, pass a pointer to an open CDatabase object, or be able to supply connection information to the column recordset in some other way.
Take steps to add columns dynamically.

See the process described in Adding the Columns below.
Open your main recordset.

The recordset selects records and uses record field exchange (RFX) to bind both the static columns (those mapped to recordset field data members) and the dynamic columns (mapped to extra storage that you allocate).

Adding the Columns

Dynamically binding added columns at run time requires the following steps:

Determine at run time what columns are in the target table. Extract from that information a list of the columns that have been added to the table since your recordset class was designed.

A good approach is to use a column recordset class designed to query the data source for column information for the target table — such as column name, data type, and so on. The MFC Database sample provides a recordset class called CColumns that you can use to build a list of the new columns’ names.
Provide storage for the new field data members. Your main recordset class doesn’t have field data members for unknown columns, so you must provide a place to store the names, result values, and possibly data type information (if the columns are of different data types).

One approach is to build one or more dynamic lists, one for the new columns’ names, another for their result values, and a third for their data types (if necessary). These lists, particularly the value list, provide the information and the necessary storage for binding. The following figure illustrates building the lists.

Building Lists of Columns to Bind Dynamically
Add an RFX function call in your main recordset’s DoFieldExchange function for each added column. These RFX calls do the work of fetching a record, including the additional columns, and binding the columns to recordset data members or to your dynamically supplied storage for them.

One approach is to add a loop to your main recordset’s DoFieldExchange function that loops through your list of new columns, calling the appropriate RFX function for each column in the list. On each RFX call, pass a column name from the column name list and a storage location in the corresponding member of the result value list.

Lists of Columns

The four lists you need to work with are:

Current-Table-Columns (List 1 in the illustration)

A list of the columns currently in the table on the data source. This list may or may not match the list of columns currently bound in your recordset.

Bound-Recordset-Columns (List 2 in the illustration)

A list of the columns bound in your recordset (usually set up with ClassWizard). These columns already have RFX statements in your DoFieldExchange function.

Columns-To-Bind-Dynamically (List 3 in the illustration)

A list of columns in the table but not in your recordset. These are the columns you want to bind dynamically.

Dynamic-Column-Values (List 4 in the illustration) A list containing storage for the values retrieved from the columns you bind dynamically. Elements of this list correspond to those in Columns-to-Bind-Dynamically, one to one.

Building Your Lists

With a general strategy in mind, you can turn to the details. The procedures in the rest of this article show you how to build the lists shown in Lists of Columns. The procedures guide you through:

Determining the columns in your table at run time
Determining the names of columns not in your recordset
Providing dynamic storage for columns newly added to the table
Dynamically adding RFX calls for new columns

Determining the Columns in Your Table at Run Time

First, build Current-Table-Columns (as in the illustration): a list of the columns in the table on the data source.

To determine the columns in a table at run time (Current-Table-Columns)

Borrow the files COLUMNST.H/.CPP from the MFC Database sample . Add the .CPP file to your project and include the .H file as needed.
At run time, construct a “column recordset” object of class CColumn, passing a pointer to an open CDatabase object.

Before you call Open, set one or more of the column recordset’s parameters. The following table describes what these parameters specify.

Parameter	Description
`m_strQualifierParam`	Identifies the database containing the table for ODBC. You usually don’t need to specify this value.
`m_strOwnerParam`	Identifies the person who created the target table.
`m_strTableNameParam`	Identifies the target table by name.
`m_strColumnNameParam`	Identifies a specific column by name.

In most cases, you need only the table name, although some data sources might require the owner name as well, and others might require even more information. In addition to table name, use the column name parameter if you need information for only a single column in the table. For information about these parameters, see ::SQLColumns in the ODBC SDK Programmer’s Reference on the MSDN Library CD.

Call Open for the column recordset.

The recordset returns a record for each column in the specified table (unless you specify m_strColumnNameParam).
Construct Current-Table-Columns, a collection object that can hold CString objects.

For example, you might use a CStringList.
Scroll through the object’s records, loading column names into Current-Table-Columns as you go.

This procedure results in a collection object that contains the names of all columns in a specified table. For example, the illustration shows Current-Table-Columns (List 1) with four elements. The last element is “Phone.” For descriptions of the lists, see Lists of Columns.

Determining Which Table Columns Are Not in Your Recordset

Next, build a list (Bound-Recordset-Columns, as in List 2 in the illustration) that contains a list of the columns already bound in your main recordset. Then build a list (Columns-to-Bind-Dynamically, derived from Current-Table-Columns and Bound-Recordset-Columns) that contains column names that are in the table on the data source but not in your main recordset.

To determine the names of columns not in the recordset (Columns-to-Bind-Dynamically)

Build a list (Bound-Recordset-Columns) of the columns already bound in your main recordset.

One approach is to create Bound-Recordset-Columns at design time. You can visually examine the RFX function calls in the recordset’s DoFieldExchange function to get these names. Then set up your list as an array initialized with the names.

For example, the illustration shows Bound-Recordset-Columns (List 2) with three elements. Bound-Recordset-Columns is missing the Phone column shown in Current-Table-Columns (List 1).
Compare Current-Table-Columns and Bound-Recordset-Columns to build a list (Columns-to-Bind-Dynamically) of the columns not already bound in your main recordset.

One approach is to loop through your list of columns in the table at run time (Current-Table-Columns) and your list of columns already bound in your recordset (Bound-Recordset-Columns) in parallel. Into Columns-to-Bind-Dynamically put any names in Current-Table-Columns that don’t appear in Bound-Recordset-Columns.

For example, the illustration shows Columns-to-Bind-Dynamically (List 3) with one element: the Phone column found in Current-Table-Columns (List 1) but not in Bound-Recordset-Columns (List 2).
Build a list of Dynamic-Column-Values (as in List 4 in the illustration) in which to store the data values corresponding to each column name stored in your list of columns to bind dynamically (Columns-to-Bind-Dynamically).

The elements of this list play the role of new recordset field data members. They are the storage locations to which the dynamic columns are bound. For descriptions of the lists, see Lists of Columns.

Providing Storage for the New Columns

Next, set up storage locations for the columns to be bound dynamically. The idea is to provide a list element in which to store each column’s value. These storage locations parallel the recordset member variables, which store the normally bound columns.

To provide dynamic storage for new columns (Dynamic-Column-Values)

Build Dynamic-Column-Values, parallel to Columns-to-Bind-Dynamically, to contain the value of the data in each column.

For example, the illustration shows Dynamic-Column-Values (List 4) with one element: a CString object containing the actual phone number for the current record: “555-1212”.

In the most common case, Dynamic-Column-Values has elements of type CString. If you’re dealing with columns of varying data types, you’ll need a list that can contain elements of a variety of types.

The result of the preceding procedures is two main lists: Columns-to-Bind-Dynamically containing the names of columns and Dynamic-Column-Values containing the values in the columns for the current record.

****Tip ****If the new columns aren’t all of the same data type, you might want an extra parallel list containing items that somehow define the type of each corresponding element in the column list. (You can use the values AFX_RFX_BOOL, AFX_RFX_BYTE, and so on, for this if you wish. These constants are defined in AFXDB.H.) Choose a list type based on how you represent the column data types.

Adding RFX Calls to Bind the Columns

Finally, arrange for the dynamic binding to occur by placing RFX calls for the new columns in your DoFieldExchange function.

To dynamically add RFX calls for new columns

In your main recordset’s DoFieldExchange member function, add code that loops through your list of new columns (Columns-to-Bind-Dynamically). In each loop, extract a column name from Columns-to-Bind-Dynamically and a result value for the column from Dynamic-Column-Values. Pass these items to an RFX function call appropriate to the data type of the column. For descriptions of the lists, see Lists of Columns.

In the common case, in your RFX_Text function calls you extract CString objects from the lists, as in the following lines of code, where Columns-to-Bind-Dynamically is a CStringList called m_listName and Dynamic-Column-Values is a CStringList called m_listValue:

RFX_Text( pFX,
            m_listName.GetNext( posName ),
            m_listValue.GetNext( posValue ));

For an example of such a loop added to DoFieldExchange, see CSections::DoFieldExchange in the file SECTIONS.CPP in the MFC Database sample . For more information about RFX functions, see in the Class Library Reference.

****Tip ****If the new columns are of different data types, use a switch statement in your loop to call the appropriate RFX function for each type.

When the framework calls DoFieldExchange during the Open process to bind columns to the recordset, the RFX calls for the static columns bind those columns. Then your loop repeatedly calls RFX functions for the dynamic columns.

See the complete source code in the MFC Database sample .

Recordset: Working with Large Data Items (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to both the MFC ODBC classes and the MFC DAO classes.

****Note ****If you’re using the MFC DAO classes, manage your large data items with class rather than class . If you're using the MFC ODBC classes with bulk row fetching, use CLongBinary rather than CByteArray. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

Suppose your database can store large pieces of data, such as bitmaps (employee photographs, maps, pictures of products, OLE objects, and so on). This kind of data is often referred to as a Binary Large Object (or BLOB) because:

Each field value is large.
Unlike numbers and other simple data types, it has no predictable size.
The data is formless from the perspective of your program.

This article explains what support the database classes provide for working with such objects.

Managing Large Objects

Recordsets have two ways to solve the special difficulty of managing binary large objects. You can use class , or you can use class . In general, CByteArray is the preferred way to manage large binary data.

CByteArray requires more overhead than CLongBinary but is more capable, as described in The CByteArray Class. CLongBinary is described briefly in The CLongBinary Class.

For detailed information about using CByteArray to work with large data items, see .

The CByteArray Class

CByteArray is one of the MFC collection classes. A CByteArray object stores a dynamic array of bytes — the array can grow if needed. The class provides fast access by index, as with built-in C++ arrays. CByteArray objects can be serialized and dumped for diagnostic purposes. The class supplies member functions for getting and setting specified bytes, inserting and appending bytes, and removing one byte or all bytes. These facilities make parsing the binary data easier. For example, if the binary object is an OLE object, you might have to work through some header bytes to reach the actual object.

Using CByteArray in Recordsets

By giving a field data member of your recordset the type CByteArray, you provide a fixed base from which RFX can manage the transfer of such an object between your recordset and the data source and through which you can manipulate the data inside the object. RFX needs a specific site for retrieved data, and you need a way to access the underlying data.

For detailed information about using CByteArray to work with large data items, see .

The CLongBinary Class

A object is a simple shell around an HGLOBAL handle to a block of storage allocated on the heap. When it binds a table column containing a binary large object, RFX allocates the HGLOBAL handle when it needs to transfer the data to the recordset and stores the handle in the CLongBinary field of the recordset.

In turn, you use the HGLOBAL handle, m_hData, to work with the data itself, operating on it as you would on any handle data. This is where adds capabilities.

****Caution CLongBinary objects can’t be used as parameters in function calls. In addition, their implementation, which calls ::SQLGetData, necessarily slows scrolling performance for a scrollable snapshot. This may also be true when you use an ::SQLGetData call yourself to retrieve dynamic schema columns.

Recordset: Obtaining SUMs and Other Aggregate Results (ODBC)

项目
2006/09/15
2 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains how to obtain aggregate results using the following SQL keywords:

SUM Calculates the total of the values in a column with a numeric data type.
MIN Extracts the smallest value in a column with a numeric data type.
MAX Extracts the largest value in a column with a numeric data type.
AVG Calculates an average value of all the values in a column with a numeric data type.
COUNT Counts the number of records in a column of any data type.

You use these SQL functions to obtain statistical information about the records in a data source rather than to extract records from the data source. The recordset that is created usually consists of a single record (if all columns are aggregates) that contains a value. (There might be more than one record if you used a GROUP BY clause.) This value is the result of the calculation or extraction performed by the SQL function.

****Tip ****To add an SQL GROUP BY clause (and possibly a HAVING clause) to your SQL statement, append it to the end of m_strFilter. For example:

m_strFilter = "sales > 10 GROUP BY SALESPERSON_ID";

You can limit the number of records you use to obtain aggregate results by filtering and sorting the columns.

****Caution ****Some aggregation operators return a different data type from the column(s) over which they are aggregating.

SUM and AVG may return the next larger data type (for example, calling with int returns LONG or double).
COUNT usually returns LONG regardless of target column type.
MAX and MIN return the same data type as the columns they calculate.

For example, ClassWizard creates long``m_lSales to accommodate a Sales column, but you’ll need to replace this with a double m_dblSumSales data member to accommodate the aggregate result. See the example that follows.

To obtain an aggregate result for a recordset

Create a recordset containing the column(s) from which you want to obtain aggregate results.
Modify the function for the recordset. Replace the string representing the column name (the second argument of the RFX function call(s)) with a string representing the aggregation function on the column. For example, replace
```
RFX_Long(pFX, "Sales", m_lSales);
```
with
```
RFX_Double(pFX, "Sum(Sales)", m_dblSumSales)
```
Open the recordset. The result of the aggregation operation will be left in m_dblSumSales.

****Note ****ClassWizard actually assigns data member names without Hungarian prefixes. For example, the wizard would produce m_Sales for a Sales column, rather than the m_lSales name used earlier for illustration.

If you’re using a class to view the data, you’ll have to change the DDX function call to display the new data member value; in this case, changing it from

DDX_FieldText(pDX, IDC_SUMSALES, m_pSet->m_lSales, m_pSet);

DDX_FieldText(pDX, IDC_SUMSALES, m_pSet->m_dblSumSales, m_pSet);

Recordset: How Recordsets Select Records (ODBC)

项目
2006/09/15
6 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains:

Your role and your options in selecting records.
How a recordset constructs its SQL statement and selects records.
What you can do to customize the selection.

Recordsets select records from a data source through an ODBC driver by sending SQL statements to the driver. The SQL sent depends on how you design and open your recordset class.

Your Options in Selecting Records

The following table shows your options in selecting records.

How and When You Can Affect a Recordset

When you ...	You can ...
Declare your recordset class with ClassWizard...	Specify which table to select from. Specify which columns to include. See ClassWizard: Creating a Recordset Class.
Complete your recordset class implementation...	Override member functions such as OnSetOptions (advanced) to set application-specific options or to change defaults. Specify parameter data members if you want a parameterized recordset.
Construct a recordset object (before you call Open) and then...	Specify a search condition (possibly compound) for use in a WHERE clause that filters the records. See Recordset: Filtering Records (ODBC). Specify a sort order for use in an ORDER BY clause that sorts the records. See Recordset: Sorting Records (ODBC). Specify parameter values for any parameters you added to the class. See Recordset: Parameterizing a Recordset (ODBC).
Run the recordset’s query by calling Open...	Specify a custom SQL string to replace the default SQL string set up by ClassWizard. See in the Class Library Reference and SQL: Customizing Your Recordset’s SQL Statement (ODBC).
Call Requery to requery the recordset with the latest values on the data source...	Specify new parameters, filter, or sort. See Recordset: Requerying a Recordset (ODBC).

How a Recordset Constructs Its SQL Statement

When you call a recordset object’s member function, Open constructs an SQL statement using some or all of the following ingredients:

The lpszSQL parameter passed to Open. If not NULL, this parameter specifies a custom SQL string, or part of one. The framework parses the string. If the string is an SQL SELECT statement or an ODBC CALL statement, the framework uses the string as the recordset’s SQL statement. If the string does not begin with “SELECT” or “{CALL”, the framework uses what’s supplied to construct an SQL FROM clause.
The string returned by . By default, this is the name of the table you specified for the recordset in ClassWizard, but you can change what the function returns. The framework calls GetDefaultSQL — if the string doesn’t begin with “SELECT” or “{CALL”, it is assumed to be a table name, which is used to construct an SQL string.
The field data members of the recordset, which are to be bound to specific columns of the table. The framework binds record columns to the addresses of these members, using them as buffers. The framework determines the correlation of field data members to table columns from the RFX or Bulk RFX function calls in the recordset’s or member function.
The filter for the recordset, if any, contained in the data member. The framework uses this string to construct an SQL WHERE clause.
The sort order for the recordset, if any, contained in the data member. The framework uses this string to construct an SQL ORDER BY clause.

****Tip ****To use the SQL GROUP BY clause (and possibly the HAVING clause), append the clause(s) to the end of your filter string.
The values of any parameter data members you specify for the class. You set parameter values just before you call Open or Requery. The framework binds the parameter values to “?” placeholders in the SQL string. At compile time, you specify the string with placeholders. At run time, the framework fills in the details based on the parameter values you pass.

Open constructs an SQL SELECT statement from these ingredients. See Customizing the Selection for details about how the framework uses the ingredients.

After constructing the statement, Open sends the SQL to the ODBC Driver Manager (and the ODBC Cursor Library if it is in memory), which sends it on to the ODBC driver for the specific DBMS. The driver communicates with the DBMS to carry out the selection on the data source and fetches the first record. The framework loads the record into the field data members of the recordset.

You can use a combination of these techniques to open tables and to construct a query based on a join of multiple tables. With additional customization, you can call predefined queries (stored procedures), select table columns not known at design time and bind them to recordset fields, or perform most other data-access tasks. Tasks you can’t accomplish by customizing recordsets can still be accomplished by calling ODBC API functions or directly executing SQL statements with .

Customizing the Selection

Besides supplying a filter, a sort order, or parameters, you can take the following actions to customize your recordset’s selection:

Pass a custom SQL string in lpszSQL when you call for the recordset. Anything you pass in lpsqSQL takes precedence over what the member function returns.

See the article SQL: Customizing Your Recordset’s SQL Statement (ODBC). That article describes the kinds of SQL statements (or partial statements) you can pass to Open and what the framework does with them.

****Note ****If the custom string you pass does not begin with “SELECT” or “{CALL”, MFC assumes it contains a table name. This also applies to the next bulleted item below.
Alter the string that ClassWizard writes in your recordset’s GetDefaultSQL member function. Edit the function’s code to change what it returns. By default, ClassWizard writes a GetDefaultSQL function that returns a single table name.

You can have GetDefaultSQL return any of the items that you can pass in the lpszSQL parameter to Open. If you don’t pass a custom SQL string in lpszSQL, the framework uses the string that GetDefaultSQL returns. At a minimum, GetDefaultSQL must return a single table name. But you can have it return multiple table names, a full SELECT statement, an ODBC CALL statement, and so on. For a list of what you can pass to lpszSQL — or have GetDefaultSQL return — see the article SQL: Customizing Your Recordset’s SQL Statement (ODBC).

If you’re performing a join of two or more tables, rewrite GetDefaultSQL to customize the table list used in the SQL FROM clause. See the article Recordset: Performing a Join (ODBC).
Manually bind additional field data members, perhaps based on information you obtain about the schema of your data source at run time. You add field data members to the recordset class, RFX or Bulk RFX function calls for them to the or member function, and initializations of the data members in the class constructor.

See the article Recordset: Dynamically Binding Data Columns (ODBC).
Override recordset member functions, such as OnSetOptions, to set application-specific options or to override defaults.

If you want to base the recordset on a complex SQL statement, you’ll need to use some combination of these customization techniques. For example, perhaps you want to use SQL clauses and keywords not directly supported by recordsets, or perhaps you’re joining multiple tables.

Recordset: How Recordsets Update Records (ODBC)

项目
2006/09/15
3 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

Besides their ability to select records from a data source, recordsets can (optionally) update or delete the selected records or add new records. Three factors determine a recordset’s updatability: whether the connected data source is updatable, the options you specify when you create a recordset object, and the SQL that is created.

****Note ****The SQL upon which your CRecordset object is based can affect your recordset’s updatability. For example, if your SQL contains a join or a GROUP BY clause, MFC sets the updatability to FALSE.

Note This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you are using bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

This article explains:

Your role in recordset updating and what the framework does for you.
The recordset as an edit buffer and the differences between dynasets and snapshots.

The article Recordset: How AddNew, Edit, and Delete Work (ODBC) describes the actions of these functions from the point of view of the recordset.

The article Recordset: More About Updates (ODBC) completes the recordset update story by explaining how transactions affect updates, how closing a recordset or scrolling affects updates in progress, and how your updates interact with the updates of other users.

Your Role in Recordset Updating

The following table shows your role in using recordsets to add, edit, or delete records, along with what the framework does for you.

Recordset Updating: You and the Framework

You...	The framework...
Determine whether the data source is updatable (or appendable).	Supplies member functions for testing the data source’s updatability or appendability.
Open an updatable recordset (of any type).
Determine whether the recordset is updatable by calling CRecordset update functions such as CanUpdate or CanAppend.
Call recordset member functions to add, edit, and delete records.	Manages the mechanics of exchanging data between your recordset object and the data source.
Optionally, use transactions to control the update process.	Supplies CDatabase member functions to support transactions.

For more information about transactions, see the article Transaction (ODBC).

The Edit Buffer

Taken collectively, the field data members of a recordset serve as an edit buffer that contains one record — the current record. Update operations use this buffer to operate on the current record.

When you add a record, the edit buffer is used to build a new record. When you finish adding the record, the record that was previously current becomes current again.
When you update (edit) a record, the edit buffer is used to set the field data members of the recordset to new values. When you finish updating, the updated record is still current.

When you call or , the current record is stored so it can be restored later as needed. When you call , the current record is not stored but is marked as deleted, and you must scroll to another record.

****Note ****The edit buffer plays no role in record deletion. When you delete the current record, the record is marked as deleted, and the recordset is “not on a record” until you scroll to a different record.

Dynasets and Snapshots

Dynasets refresh a record’s contents as you scroll to the record. Snapshots are static representations of the records, so a record’s contents are not refreshed unless you call . To use all the functionality of dynasets, you must be working with an ODBC driver that conforms to the correct level of ODBC API support. For more information, see the articles ODBC and Dynaset.

Recordset: How AddNew, Edit, and Delete Work (ODBC)

项目
2006/09/15
8 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains how the AddNew, Edit, and Delete member functions of class CRecordset work. Topics covered include:

How adding records works
Visibility of added records
How editing records works
How deleting records works

As a supplement, you might want to read the article Record Field Exchange: How RFX Works, which describes the corresponding role of RFX in update operations.

Adding a Record

Adding a new record to a recordset involves calling the recordset’s member function, setting the values of the new record’s field data members, and calling the member function to write the record to the data source.

As a precondition for calling AddNew, the recordset must not have been opened as read-only. The CanUpdate and CanAppend member functions let you determine these conditions.

When you call AddNew:

The record in the edit buffer is stored, so its contents can be restored if the operation is canceled.
The field data members are flagged so it will be possible to detect changes in them later. The field data members are also marked “clean” (unchanged) and set to a Null.

After you call AddNew, the edit buffer represents a new, empty record, ready to be filled in with values. To do this, you manually set the values by assigning to them. Instead of specifying an actual data value for a field, you can call SetFieldNull to specify the value Null.

To commit your changes, you call Update.

When you call Update for the new record:

If your ODBC driver supports the ::SQLSetPos ODBC API function, MFC uses the function to add the record on the data source. With ::SQLSetPos, MFC can add a record more efficiently because it doesn’t have to construct and process an SQL statement.
If ::SQLSetPos can’t be used, MFC does the following:

If no changes are detected, Update does nothing and returns 0.
If there are changes, Update constructs an SQL INSERT statement. The columns represented by all dirty field data members are listed in the INSERT statement. To force a column to be included, call the member function:
```
SetFieldDirty( &m_dataMember, TRUE );
```
Update commits the new record — the INSERT statement is executed and the record is committed to the table on the data source (and the recordset, if not a snapshot) unless a transaction is in progress (see How Transactions Affect Updates in the article Recordset: More About Updates).
The stored record is restored to the edit buffer. The record that was current before the AddNew call is current again regardless of whether the INSERT statement was successfully executed.

****Tip ****For complete control of a new record, take the following approach: (a) set the values of any fields that will have values; (b) explicitly set any fields that will remain Null by calling SetFieldNull with a pointer to the field and the parameter TRUE (the default). If you want to ensure that a field is not written to the data source, call SetFieldDirty with a pointer to the field and the parameter FALSE, and do not modify the field’s value. To determine whether a field is allowed to be Null, call IsFieldNullable.

****Tip Advanced: To detect when recordset data members change value, MFC uses a PSEUDO_NULL value appropriate to each data type that you can store in a recordset. If you must explicitly set a field to the PSEUDO_NULL value and the field happens already to be marked Null, you must also call SetFieldNull, passing the address of the field in the first parameter and FALSE in the second parameter.

Visibility of Added Records

When is an added record visible to your recordset? Added records sometimes show up and sometimes aren’t visible, depending on two things:

What your driver is capable of.
What the framework can take advantage of.

If your ODBC driver supports the ::SQLSetPos ODBC API function, MFC uses the function to add records. With ::SQLSetPos, added records are visible to any updatable MFC recordset. Without support for the function, added records are not visible, and you must call Requery to see them. Using ::SQLSetPos is also more efficient.

Editing an Existing Record

Editing an existing record in a recordset involves scrolling to the record, calling the recordset’s member function, setting the values of the new record’s field data members, and calling the member function to write the changed record to the data source.

As a precondition for calling Edit, the recordset must be updatable and on a record. The CanUpdate and IsDeleted member functions let you determine these conditions. The current record also must not already have been deleted, and there must be records in the recordset (both IsBOF and IsEOF return 0).

When you call Edit, the record in the edit buffer (the current record) is stored. The stored record’s values are later used to detect whether any fields have changed.

After you call Edit, the edit buffer still represents the current record but is now ready to accept changes to the field data members. To change the record, you manually set the values of any field data members you want to edit. Instead of specifying an actual data value for a field, you can call SetFieldNull to specify the value Null. To commit your changes, call Update.

****Tip ****To get out of AddNew or Edit mode, call Move with the parameter AFX_MOVE_REFRESH.

As a precondition for calling Update, the recordset must not be empty and the current record must not have been deleted. IsBOF, IsEOF, and IsDeleted should all return 0.

When you call Update for the edited record:

If your ODBC driver supports the ::SQLSetPos ODBC API function, MFC uses the function to update the record on the data source. With ::SQLSetPos, the driver compares your edit buffer with the corresponding record on the server, updating the record on the server if the two are different. With ::SQLSetPos, MFC can update a record more efficiently because it doesn’t have to construct and process an SQL statement.

-or-
If ::SQLSetPos can’t be used, MFC does the following:

If there have been no changes, Update does nothing and returns 0.
If there are changes, Update constructs an SQL UPDATE statement. The columns listed in the UPDATE statement are based on the field data members that have changed.
Update commits the changes — executes the UPDATE statement — and the record is changed on the data source, but not committed if a transaction is in progress (see the article Transaction: Performing a Transaction in a Recordset (ODBC) for details about how the transaction affects the update). ODBC keeps a copy of the record, which also changes.
Unlike the process for AddNew, the Edit process does not restore the stored record. The edited record remains in place as the current record.

****Caution ****When you prepare to update a recordset by calling Update, take care that your recordset includes all columns making up the primary key of the table (or all of the columns of any unique index on the table, or enough columns to uniquely identify the row). In some cases, the framework can use only the columns selected in your recordset to identify which record in your table to update. Without all the necessary columns, multiple records may be updated in the table. In this case, the framework will throw exceptions when you call Update.

****Tip ****If you call AddNew or Edit after having called either function previously but before you call Update, the edit buffer is refreshed with the stored record, replacing the new or edited record in progress. This behavior gives you a way to abort an AddNew or Edit and begin a new one: if you determine that the record-in-progress is faulty, simply call Edit or AddNew again.

Deleting a Record

Deleting a record from a recordset involves scrolling to the record and calling the recordset’s member function. Unlike AddNew and Edit, Delete does not require a matching call to Update.

As a precondition for calling Delete, the recordset must be updatable and it must be on a record. The CanUpdate, IsBOF, IsEOF, and IsDeleted member functions let you determine these conditions.

When you call Delete:

If your ODBC driver supports the ::SQLSetPos ODBC API function, MFC uses the function to delete the record on the data source. Using ::SQLSetPos is usually more efficient than using SQL.

-or-
If ::SQLSetPos can’t be used, MFC does the following:

The current record in the edit buffer is not backed up as in AddNew and Edit.
Delete constructs an SQL DELETE statement that will remove the record.

The current record in the edit buffer is not stored as in AddNew and Edit.
Delete commits the deletion — executes the DELETE statement. The record is marked deleted on the data source and, if the record is a snapshot, in ODBC.
The deleted record’s values are still in the field data members of the recordset, but the field data members are marked Null and the recordset’s IsDeleted member function will return a nonzero value.

****Important ****After deleting a record, you should scroll to another record to refill the edit buffer with the new record’s data. It’s an error to call Delete again, or to call Edit.

For information about the SQL statements used in update operations, see the article SQL.

Recordset: More About Updates (ODBC)

项目
2006/09/15
5 分钟可看完

This article applies to the MFC ODBC classes. For DAO recordsets, see the article DAO Recordset.

This article explains:

How other operations, such as transactions, affect updates.
Your updates and those of other users.
More about the Update and Delete member functions.

****Note ****This article applies to objects derived from CRecordset in which bulk row fetching has not been implemented. If you have implemented bulk row fetching, some of the information does not apply. For example, you cannot call the AddNew, Edit, Delete, and Update member functions; however, you can perform transactions. For more information about bulk row fetching, see the article Recordset: Fetching Records in Bulk (ODBC).

How Other Operations Affect Updates

Your updates are affected by transactions in effect at the time of the update, by closing the recordset before completing a transaction, and by scrolling before completing a transaction.

How Transactions Affect Updates

Beyond understanding how AddNew, Edit, and Delete work, it’s important to understand how the BeginTrans, CommitTrans, and Rollback member functions of work with the update functions of .

By default, calls to AddNew and Edit affect the data source immediately when you call Update. Delete calls take effect immediately. But you can establish a transaction and execute a batch of such calls. The updates are not permanent until you commit them. If you change your mind, you can roll back the transaction instead of committing it.

For more information about transactions, see the article Transaction (ODBC).

How Closing the Recordset Affects Updates

If you close a recordset, or its associated CDatabase object, with a transaction in progress (you haven’t called or ), the transaction is rolled back automatically (unless your database backend is the Microsoft Jet database engine). How Rollback Affects Transactions in the article Transaction: How Transactions Affects Updates (ODBC) describes the effect this has on AddNew, Edit, or Delete operations in progress.

****Caution ****If you are using the Microsoft Jet database engine, closing a recordset inside an explicit transaction does not result in releasing any of the rows that were modified or locks that were placed until the explicit transaction is committed or rolled back. It is recommended that you always both open and close recordsets either inside or outside of an explicit Jet transaction.

How Scrolling Affects Updates

When you scroll in a recordset, the edit buffer is filled with each new current record (the previous record is not stored first). Scrolling skips over records previously deleted. If you scroll after an AddNew or Edit call without calling Update, CommitTrans, or Rollback first, any changes are lost (with no warning to you) as a new record is brought into the edit buffer. The edit buffer is filled with the record scrolled to, the stored record is freed, and no change occurs on the data source. This applies to both AddNew and Edit.

Your Updates and the Updates of Other Users

When you use a recordset to update data, your updates affect other users. Similarly, the updates of other users during the lifetime of your recordset affect you.

In a multiuser environment, other users can open recordsets that contain some of the same records you have selected in your recordset. Changes to a record before you retrieve it are reflected in your recordset. Dynasets retrieve a record each time you scroll to it, so dynasets reflect changes each time you scroll to a record. Snapshots retrieve a record the first time you scroll to it, so snapshots reflect only those changes that occur before you scroll to the record initially.

Records added by other users after you open the recordset don’t show up in your recordset unless you requery. If your recordset is a dynaset, edits to existing records by other users do show up in your dynaset when you scroll to the affected record. If your recordset is a snapshot, edits don’t show up until you requery the snapshot. If you want to see records added or deleted by other users in your snapshot, or records added by other users in your dynaset, call to rebuild the recordset. (Note that the deletions of other users show up in your dynaset.) You may also call Requery to see records you add, but not to see your deletions.

****Tip ****To force caching of an entire snapshot at once, call MoveLast immediately after you open the snapshot. Then call MoveFirst to begin working with the records. MoveLast is equivalent to scrolling over all the records, but it retrieves them all at once. Note, however, that this can lower performance and may not be required for some drivers.

The effects of your updates on other users are similar to their effects on you.

More About Update and Delete

This section provides additional information to help you work with Update and Delete.

Update Success and Failure

If Update succeeds, the AddNew or Edit mode ends. To begin an AddNew or Edit mode again, call AddNew or Edit.

If Update fails (returns FALSE or throws an exception), you remain in AddNew or Edit mode, depending on which function you called last. You can then do one of the following:

Modify a field data member and try the Update again.
Call AddNew to reset the field data members to Null, set the values of the field data members, then call Update again.
Call Edit to reload the values that were in the recordset before the first call to AddNew or Edit, then set the values of the field data members, then call Update again. After a successful Update call (except after an AddNew call), the field data members retain their new values.
Call Move (including Move with a parameter of AFX_MOVE_REFRESH, or 0), which flushes any changes and ends any AddNew or Edit mode in effect.

Update and Delete

This section applies to both Update and Delete.

On an Update or Delete operation, one and only one record should be updated. That record is the current record, which corresponds to the data values in the fields of the recordset. If for some reason no records are affected or more than one record is affected, an exception is thrown containing one of the following RETCODE values:

AFX_SQL_ERROR_NO_ROWS_AFFECTED
AFX_SQL_ERROR_MULTIPLE_ROWS_AFFECTED

When these exceptions are thrown, you remain in the AddNew or Edit state you were in when you called Update or Delete. Here are the most common scenarios in which you would see these exceptions. You’re most likely to see:

AFX_SQL_ERROR_NO_ROWS_AFFECTED when you’re using optimistic locking mode and another user has modified the record in a way that prevents the framework from identifying the correct record to update or delete.
AFX_SQL_ERROR_MULTIPLE_ROWS_AFFECTED when the table you’re updating has no primary key or unique index, and you don’t have enough columns in the recordset to uniquely identify a table row.

SQL: SQL and C++ Data Types (ODBC)

项目
2006/09/15
2 分钟可看完

****Note ****This information applies to the MFC ODBC classes. If you’re working with the MFC DAO classes, see the topic Comparison of Microsoft Jet Database Engine SQL and ANSI SQL in DAO Help.

The following table maps ANSI SQL data types to C++ data types. This augments the C language information given in Appendix D of the ODBC SDK Programmer’s Reference on the MSDN Library CD. ClassWizard manages most data-type mapping for you. If you don’t use ClassWizard, you can use the mapping information to help you write the field exchange code manually.

ANSI SQL Data Types Mapped to C++ Data Types

ANSI SQL data type	C++ data type
CHAR	CString
DECIMAL	CString1
SMALLINT	int
REAL	float
INTEGER	long
FLOAT	double
DOUBLE	double
NUMERIC	CString1
VARCHAR	CString
LONGVARCHAR	CLongBinary, CString2
BIT	BOOL
TINYINT	BYTE
BIGINT	CString1
BINARY	CByteArray
VARBINARY	CByteArray
LONGVARBINARY	CLongBinary, CByteArray3
DATE	CTime, CString
TIME	CTime, CString
TIMESTAMP	CTime, CString

1. ANSI DECIMAL and NUMERIC map to CString because SQL_C_CHAR is the default ODBC transfer type.

2. Character data beyond 255 characters is truncated by default when mapped to CString. You can extend the truncation length by explicitly setting the nMaxLength argument of RFX_Text.

3. Binary data beyond 255 characters is truncated by default when mapped to CByteArray. You can extend the truncation length by explicitly setting the nMaxLength argument of RFX_Binary.

If you are not using the ODBC cursor library, you may encounter a problem when attempting to update two or more long variable-length fields using the Microsoft SQL Server ODBC driver and the MFC ODBC database classes. The ODBC types, SQL_LONGVARCHAR and SQL_LONGVARBINARY, map to "text" and "image" SQL Server types. A CDBException will be thrown if you update two or more long variable-length fields on the same call to CRecordset::Update. Therefore, do not update multiple long columns simultaneously with CRecordset::Update. You can update multiple long columns simultaneously with the ODBC API SQLPutData. You can also use the ODBC cursor library, but this is not recommended for drivers, like the SQL Server driver, that support cursors and don’t need the cursor library.

If you are using the ODBC cursor library with the MFC ODBC database classes and the Microsoft SQL Server ODBC driver, an ASSERT may occur along with a CDBException if a call to CRecordset::Update follows a call to CRecordset::Requery. Instead, call CRecordset::Close and CRecordset::Open rather than CRecordset::Requery. Another solution is not to use the ODBC cursor library, since the SQL Server and the SQL Server ODBC driver provide native support for cursors natively and the ODBC cursor library isn’t needed.

Transaction: Performing a Transaction in a Recordset (ODBC)

项目
2006/09/15
2 分钟可看完

This article explains how to perform a transaction in a recordset.

****Note ****Only one level of transactions is supported; you cannot nest transactions.

To perform a transaction in a recordset

Call the CDatabase object’s BeginTrans member function.
If you have not implemented bulk row fetching, call the AddNew/Update, Edit/Update, and Delete member functions of one or more recordset objects of the same database as many times as needed. See the article Recordset: Adding, Updating, and Deleting Records (ODBC). If you have implemented bulk row fetching, you must write your own functions to update the data source. For an example of how to do this, see the sample .
Finally, call the CDatabase object’s CommitTrans member function. Or if an error occurs in one of the updates, or you decide to cancel the changes, call its Rollback member function.

The following example uses two recordsets to delete a student’s enrollment from a school registration database, removing the student from all classes in which the student is enrolled. The Delete calls in both recordsets must succeed, so a transaction is required. The example assumes the existence of m_dbStudentReg, a member variable of type CDatabase already connected to the data source, and the recordset classes CEnrollmentSet and CStudentSet. The strStudentID variable contains a value obtained from the user.

BOOL CEnrollDoc::RemoveStudent( CString strStudentID )
{
    // remove student from all the classes
    // the student is enrolled in

    if ( !m_dbStudentReg.BeginTrans( ) )
        return FALSE;

    CEnrollmentSet rsEnrollmentSet(&m_dbStudentReg);
    rsEnrollmentSet.m_strFilter = "StudentID = " + strStudentID;

    if ( !rsEnrollmentSet.Open(CRecordset::dynaset) )
        return FALSE;

    CStudentSet rsStudentSet(&m_dbStudentReg);
    rsStudentSet.m_strFilter = "StudentID = " + strStudentID;

    if ( !rsStudentSet.Open(CRecordset::dynaset) )
        return FALSE;

    TRY
    {
        while ( !rsEnrollmentSet.IsEOF( ) )
        {
            rsEnrollmentSet.Delete( );
            rsEnrollmentSet.MoveNext( );
        }

        // delete the student record
        rsStudentSet.Delete( );

        m_dbStudentReg.CommitTrans( );
    }

    CATCH_ALL(e)
    {
        m_dbStudentReg.Rollback( );
        return FALSE;
    }
    END_CATCH_ALL

    rsEnrollmentSet.Close( );
    rsStudentSet.Close( );

    return TRUE;

}

****Warning ****Calling BeginTrans again without calling CommitTrans or Rollback is an error.

记录集 (ODBC)

项目
2011/08/12

本主题适用于 MFC ODBC 类。

CRecordset 对象表示从数据源选定的一组记录。这些记录可以来自：

表。
查询。
访问一个或多个表的存储过程。

基于表的记录集示例是“所有客户”，它访问一个“Customer”表。查询的示例是“Joe Smith 的所有发票”。基于存储过程（有时称为预定义查询）的记录集示例是“所有过期的帐户”，它调用后端数据库中的存储过程。记录集可以联接两个或更多来自同一数据源的表，但不能联接来自不同数据源的表。

提示

有关用向导派生记录集类的信息，请参见添加 MFC ODBC 使用者和 MFC 应用程序向导的数据库支持。

提示

某些 ODBC 驱动程序支持数据库视图。从这种意义上说，视图是最初由 SQL CREATE VIEW 语句创建的查询。向导目前不支持视图，但您自己可以通过编码实现该支持。

记录集功能

所有记录集对象共享下列功能：

如果数据源不是只读的，可以指定记录集为可更新的、可追加的或只读。如果记录集是可更新的，只要驱动程序提供适当的锁定支持，就可以选择保守式或开放式锁定方法。如果数据源是只读的，则记录集也将是只读的。
可以调用成员函数在选定的记录中 Scroll。
可以 filter 记录以限制从可用记录中选择的记录。
可以基于一列或多列按升序或降序对记录进行 Sort。
可以 parameterize 记录集以限定运行时记录集的选择。

快照和动态集

有两种主要的记录集：快照和动态集。两者均由 CRecordset 类支持。每一种都共享所有记录集的通用特性，但同时也以各自的专用方式扩展通用功能。快照提供数据的静态视图，用于报表和需要数据视图在特定时间存在时的情况。动态集则用于希望其他用户所做的更新在记录集中可见而同时又不必再次查询或刷新记录集的情况。快照和动态集可以是可更新的，也可是只读的。若要反映其他用户添加或删除的记录，请调用 CRecordset::Requery。

CRecordset 还允许两种其他类型的记录集：动态记录集和仅向前记录集。动态记录集类似于动态集；但动态记录集不用调用 CRecordset::Requery 就可以反映添加或删除的任何记录。基于这个原因，动态记录集在 DBMS 上的处理时间方面通常成本较大，而且许多 ODBC 驱动程序不支持它们。相比之下，仅向前记录集为不要求更新或向后滚动的记录集提供最有效的数据访问方法。例如，可以使用仅向前记录集将记录从一个数据源移植到另一个数据源，其中只需按向前的方向在数据间移动。若要使用仅向前记录集，必须执行下列两个操作：

将 CRecordset::forwardOnly 选项作为 Open 成员函数的 nOpenType 参数传递。
在 Open 的 dwOptions 参数中指定 CRecordset::readOnly。

提示

有关 ODBC 驱动程序的动态集支持要求的信息，请参见 ODBC。有关本版 Visual C++ 中包括的 ODBC 驱动程序列表以及有关获取其他驱动程序的信息，请参见 ODBC 驱动程序列表。

您的记录集

对于希望访问的每一个不同的表、视图或存储过程，通常定义一个从 CRecordset 派生的类。（例外情况是数据库联接，其中一个记录集表示来自两个或多个表的列。）当派生记录集类时，启用记录字段交换 (RFX) 机制或批量记录字段交换（批量 RFX）机制，这两种机制与对话框数据交换 (DDX) 机制相似。 RFX 和“批量 RFX”简化了从数据源向您的记录集传输数据的过程；另外，RFX 还可以将数据从您的记录集传输到数据源。有关更多信息，请参见记录字段交换 (RFX) 和记录集：批量获取记录 (ODBC)。

记录集对象提供对所有选定记录的访问权。使用 CRecordset 成员函数（如 MoveNext 和 MovePrev）在多个选定的记录间滚动。同时，记录集对象仅表示一个选定的记录，即当前记录。通过声明与表列或数据库查询产生的记录列相对应的记录集类成员变量，可以检查当前记录的字段。有关记录集数据成员的信息，请参见记录集：结构 (ODBC)。

下列主题解释有关使用记录集对象的详细信息。这些主题按功能类别和正常的浏览顺序列出，以便可以顺序阅读。

有关打开、读取和关闭记录集的技巧的主题

有关修改记录集的技巧的主题

有关更高级的技术的主题

有关记录集工作方式的主题

请参见

任务

添加 MFC ODBC 使用者

概念

开放式数据库连接 (ODBC)

事务 (ODBC)

posted on 2022-03-24 15:18 不及格的程序员-八神阅读(95) 评论(0) 编辑收藏举报

刷新页面返回顶部