Teach Yourself Database Programming
with Visual C++ 6 in 21 days
Now that you know how to access a data source, create a session, and specify commands, you are ready to learn how to navigate and access the data contained in the resulting row sets. Day 19 starts with a discussion of the Rowset object and its associated interfaces. Today you start to bring together the concepts presented in the previous three lessons so that you can begin to make productive use of OLE DB. Many of today's examples focus on using the OLE DB ODBC provider to access a SQL Server data source.
Today we will
So far this week you have learned how to access a data source, create a session, and even create a command that can generate a row set. However, you don't yet know how to retrieve and access the data contained in these row sets. You are about to bridge this gap in your understanding of OLE DB. OLE DB uses Rowset objects to provide access to data source data in a tabular form. The row sets that result from executing a command are only one type of row set that OLE DB can generate. You can use Session objects to create row sets and the IDBSchemaRowset interface of the Session object to retrieve row sets that contain schema information. (Refer to Day 18, "Querying a Data Source.")
The TRowset CoType supports the following interfaces:
TRowset {
interface IAccessor; // Required Interface
interface IColumnsInfo; // Required Interface
interface IConvertType; // Required Interface
interface IRowset; // Required Interface
interface IRowsetInfo; // Required Interface
interface IColumnsRowset;
interface IConnectionPointContainer;
interface IRowsetChange;
interface IRowsetIdentity;
interface IRowsetLocate;
interface IRowsetResynch;
interface IRowsetScroll;
interface IRowsetUpdate;
interface ISupportErrorInfo;
};
The following sections explain the interfaces supported by the Rowset object and describe how to retrieve row set data.
| NOTE |
The ISupportErrorInfo interface was described on Day 17, "Accessing a Data Source with OLE DB," and will be considered again on Day 21, "OLE DB Error Handling." The IAccessor, IColumnsInfo, IColumnsRowset, and IConvertType interfaces were presented yesterday (Day 18). Column Accessors are covered in more detail later today. |
The IRowset interface is the primary interface used to access row sets and the data they contain. The IRowset interface is required by the Rowset object and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides five additional methods: AddRefRows, GetData, GetNextRows, ReleaseRows, and RestartPosition. These methods are defined as follows:
HRESULT AddRefRows(ULONG cNumRowHandles, const HROWS rhRows[],
ULONG rgRefCounts[], DBROWSTATUS rRowStatus[]);
HRESULT GetData(HROW hRow, HACCESSOR hRowAccessor, void *pBuffer);
HRESULT GetNextRows(HCHAPTER hChapter, LONG lNumRowsToSkip,
LONG lNumRows, ULONG *plNumRows, HROW **phRows);
HRESULT ReleaseRows(ULONG cNumRows, const HROW rhRows[],
DBROWOPTIONS rRowOptions[], ULONG rRefCounts[],
DBROWSTATUS rRowStatus[]);
HRESULT RestartPosition(HCHAPTER hChapter);
After looking at the remaining Rowset interfaces, today's presentation focuses on the implementation details of obtaining and navigating row set data. Much of that discussion centers on using the IRowset interface. This section explains what these interface methods do and the parameters they use.
The GetNextRows method retrieves the next sequential set of rows from a row set. hChapter is the chapter handle. For nonchaptered rowsets, hChapter is ignored. The lNumRowsToSkip specifies the number of rows to skip before retrieving the next set of rows. If this value is 0, no rows are skipped. The lNumRows parameter specifies the number of rows to retrieve. The plNumRows parameter returns the number of rows actually retrieved. The phRows parameter returns an array of row handles, which can be used to retrieve the actual row data.
The GetData method actually retrieves the row data. The hRow parameter specifies a row handle returned by the GetNextRows method. The hRowAccessor parameter specifies the row Accessor handle that retrieves the data. The pBuffer parameter specifies a memory buffer in which the actual row data is stored.
The ReleaseRows method releases the set of rows obtained by the GetNextRows method. The cNumRows parameter specifies the number of rows to release, and the rHRows parameter specifies an array of row handles to release. The last three parameters typically will be NULL. The rRowOptions parameter specifies additional options to be used when releasing a row, the rRefCounts parameter specifies an array of row reference counts, and the rRowStatus parameter returns the status of each row that was released.
The AddRefRows method increments row reference counts. The cNumRowHandles parameter specifies the number of row handles passed. The rhRows array specifies the array of row handles to increment the reference count for; the rgRefCounts parameter returns an array of new row reference counts, once for each handle. The rRowStatus parameter returns the status of each row.
Finally, the RestartPosition method returns to the beginning of the row set when the next GetNextRows method is called. RestartPosition takes a single parameter that is reserved for future use.
The IRowsetInfo retrieves information about a row set, including the maximum number of active rows and how many modifications on a row set can be buffered before they can be committed. The IRowsetInfo interface can also return to a bookmarked row. (I'll discuss bookmarks in more detail later today.) The IRowsetInfo interface is required by the Rowset object and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides three additional methods: GetProperties, GetReferencedRowset, and GetSpecification. These methods are defined as follows:
HRESULT GetProperties(ULONG cPropIDSets, const DBPROPIDSET rgPropSets[],
ULONG *pcPropSets, DBPROPSET **prgPropSets);
HRESULT GetReferencedRowset(ULONG iColumnNum, REFIID riid,
IUnknown **ppRowsetInterface);
HRESULT GetSpecification(REFIID riid, IUnknown **pSpecInterface);
Day 17 covers the GetProperties method in relation to the IDBProperties interface; GetProperties retrieves row set properties. The GetReferencedRowset method returns an interface pointer to a bookmarked rowset. The iColumnNum parameter of the GetReferencedRowset method identifies the ordinal position of the column that contains the bookmark marker. The riid parameter specifies the interface ID of the interface to return in the ppRowsetInterface parameter. The GetSpecification method retrieves an interface pointer to the OLE DB object that created the row set. The riid parameter specifies an interface GUID, and the pSpecInterface parameter returns a pointer to that interface.
The IConnectionPointContainer interface is a standard COM interface and is used when connecting data to other COM or OLE objects. It is an optional interface and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides two additional methods: EnumConnectionPoints and FindConnectionPoint. These methods are defined as follows:
HRESULT EnumConnectionPoints(IEnumConnectionPoints **ppEnum); HRESULT FindConnectionPoint(REFIID riid, IConnectionPoint *ppConnectPoint);
| NOTE |
Refer to the Visual C++ documentation for more information regarding the use of this interface with Rowset objects. |
The IRowsetChange interface deletes rows, inserts rows, and modifies the data contained in a row. It is an optional interface that defines the standard IUnknown interface methods QueryInterface, AddRef, and Release, as well as three additional methods: DeleteRows, InsertRow, and SetData. As you can probably tell by their names, the DeleteRows method deletes row set rows, the InsertRow method inserts a new row into a row set, and the SetData method changes row set data. These methods are defined as follows:
HRESULT DeleteRows(HCHAPTER hChapter, ULONG cNumRows,
const HROW rhRows[], DBROWSTATUS rRowStatus[]);
HRESULT InsertRow(HCHAPTER hChapter, HACCESSOR hAccesor,
void *pBuffer, HROW *phRow);
HRESULT SetData(HROW hRow, HACCESSOR hAccessor, void *pBuffer);
For the DeleteRows method, the first parameter hChapter is the chapter handle. The cNumRows parameter determines the number of rows to delete. The rhRows parameter defines an array of row handles that are to be deleted. On return, the rRowStatus array is an array of row DBROWSTATUS structures that define the status of each deleted row.
For the InsertRow method, the first parameter hChapter is the chapter handle. The hAccessor parameter defines the Accessor for the row to insert. The pBuffer parameter defines the data buffer, containing the actual row data, described by the Accessor. On return, the phRow parameter is the row handle of the newly added row.
For the SetData method, the hRow parameter defines the handle of the row to modify. The hAccessor parameter defines the Accessor for the row, and the pBuffer parameter defines the buffer containing the modified row data.
| TIP |
SQL commands provide the same functionality as the IRowsetChange interface methods and generally use less code to do so. You can use SQL to delete, insert, and modify data. You should use the SQL commands if the data provider supports them. |
The IRowsetIdentity interface determines whether two row set rows are identical. Already, you probably realize that row set rows are identified by handles. A handle is just a fancy name for a special address that identifies where a row is stored in memory. You can use this interface to determine whether two row handles actually point to the same row set. The IRowsetIdentify interface is optional. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides one additional method: IsSameRow. This method is defined as follows:
HRESULT IsSameRow(HROW hRow1, HROW hRow2);
The IsSameRow method takes two parameters, which are the handles of the rows to compare. If the row handles point to the same row, this method returns S_OK. If not, it returns S_FALSE. Both these values give a TRUE result when the SUCCEEDED macro is used. You will need to test for S_OK explicitly or at least nest an explicit test under a SUCCEEDED macro.
IRowsetLocate is an optional interface that retrieves rows nonsequentially from a row set. This interface has methods that can use bookmarks to jump around in a row set. The standard IUnknown interface methods QueryInterface, AddRef, and Release are defined by the IRowsetLocate interface, as well as four additional methods: Compare, GetRowsAt, GetRowsByBookmark, and Hash. These methods are defined as follows:
HRESULT Compare(HCHAPTER hChapter, ULONG cBookMarkLen1,
const BYTE *pBookMark1, ULONG cBookMarkLen2,
const BYTE *pBookMark2, DBCOMPARE *pCompare);
HRESULT GetRowsAt(HWATCHREGION hReservedHandle1, HCHAPTER hChapter,
ULONG cBookMarkLen, const BYTE *pBookMark,
LONG lNumRowsOffset, LONG lNumRowsToFetch,
ULONG *lNumRowsRetrieved, HROW **phRows);
HRESULT GetRowsByBookmark(HCHAPTER hChapter, ULONG lNumRowsToFetch,
const ULONG rBookmarksLen[],
const BYTE *rBookmarks[],
HROW rhRows[], DBROWSTATUS rRowStatus[]);
HRESULT Hash(HCHAPTER hChapter, ULONG lNumBookmarks,
const ULONG rBookmarksLen[], const BYTE *rBookmarks,
DWORD rHashedBookmarks[],DBROWSTATUS rRowStatus[]);
The Compare method compares two bookmarks to determine whether one is before, after, or points to the same position as the other. The GetRowsAt method retrieves a row set from a bookmarked position, as well as a specified offset. The GetRowsByBookmark method retrieves the rows that match a set of bookmarks. The Hash method retrieves the hash value (a quick lookup key for a record) that identifies a bookmarked record. If you would like more information about these methods, refer to the OLE DB Specification help file.
The IRowsetResynch interface synchronizes row sets when they are part of a transaction. Transactions, as well as this interface, are covered in more detail on Day 20, "Properties, Transactions, and Indexes." The IRowsetResynch interface is optional and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides two additional methods: GetVisibleData and ResynchRows. These methods are defined as follows:
HRESULT GetVisibleData(HROW hRow, HACCESSOR hAccessor, void *pBuffer);
HRESULT ResynchRows(ULONG cNumRowsToResynch, const HROW rhRows[],
ULONG *pNumRowsResynched, HROW **phRowsResynched,
DBROWSTATUS **pRowStatus);
The GetVisibleData method retrieves the row set data that is visible in the transaction scope. The ResynchRows method synchronizes the data contained in the row set buffer with the actual data source.
The IRowsetScroll interface facilitates the integration of row sets with window scroll bars. This interface is optional. A Rowset object must implement the IRowsetLocate interface to support this interface. The IRowsetScroll interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides two additional methods: GetApproximatePosition and GetRowsAtRatio. These methods are defined as follows:
HRESULT GetApproximatePosition(HCHAPTER hChapter, ULONG cLenBookmark,
const BYTE *pBookmark, ULONG *plPosition,
ULONG *plNumRows);
HRESULT GetRowsAtRatio(HWATCHREGION hChapter,
HCHAPTER hReserveredhandle2, ULONG lNumerator,
ULONG lDemoninator, LONG lNumRowsToFetch,
ULONG *plNumRowsFetched, HROW **phRows);
The GetApproximatePosition method approximately determines the row position of a bookmarked row. The cLenBookmark parameter specifies the size of a bookmark, and the pBookmark parameter specifies a pointer to the approximate position of the bookmark. The plPosition parameter returns the approximate position, and the plNumRows parameter returns the total number of rows.
The GetRowsAtRatio method retrieves a number of rows located at a specific percentage location in the row set. The percentage location is determined by dividing the lNumerator parameter by the lDenominator parameter. The lNumRowsToFetch parameter specifies the number of rows to retrieve. The plNumRowsFetched parameter returns the number of rows actually retrieved, and the phRows parameter returns an array of associated row handles.
The final interface supported by the Rowset object is the IRowsetUpdate interface, which is optional. This interface buffers changes made with the IRowsetChange interface and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides five additional methods: GetOriginalData, GetPendingRows, GetRowStatus, Undo, and Update. These methods are defined as follows:
HRESULT GetOriginalData(HROW hRow, HACCESSOR hAccessor, void *pBuffer);
HRESULT GetPendingRows(HCHAPTER hChapter, DBPENDINGSTATUS dRowStatus,
ULONG *plNumPendRows, HROW **phPendRows,
DBPENDINGSTATUS **pPendStatus);
HRESULT GetRowStatus(HCHAPTER hChapter, ULONG lNumRows,
const HROW rhStatusRows, DBPENDINGSTATUS pStatus[]);
HRESULT Undo(HCHAPTER hChapter, ULONG lNumRows, const HROW rhUndoRows[],
ULONG *plNumRowsUndone, HROW **phRows,
DBROWSTATUS **pRowStatus);
HRESULT Update(HCHAPTER hChapter, ULONG lNumRows,
const HROW rhUpdateRows[], ULONG *plNumRowsUpdated, HROW **phRows, DBROWSTATUS **pRowStatus);
The GetOriginalData method is used to update the data from the data source, ignoring any changes that have not been written back. For example, if you make changes to a row and they aren't written back, you can use this method to return to the state before the changes were made. The hRow parameter specifies the row handle to re-retrieve. The hAccessor parameter defines the Accessor for the row to retrieve. The pBuffer parameter defines the data buffer that contains the actual row data described by the Accessor.
The GetPendingRows method retrieves the rows of a row set that have some operation pending, such as a new, changed, or deleted row. The status you're looking for is defined by the dRowStatus parameter, which can be a combination of DBPENDINGSTATUS_NEW, DBPENDINGSTATUS_CHANGED, or DBPENDINGSTATUS_DELETED. These flags can be combined with a logical OR. The plNumPendRows parameter returns the number of rows with the type of pending operation being searched. The phPendRows returns an array of row handles for each of these rows, and the pPendStatus parameter returns an array containing the actual status of each of these rows. The pPendStatus array is useful when you query more than one status type.
The GetRowStatus method is similar to the GetPendingRows method. The GetRowStatus method doesn't search for a specific status, though; it returns the status of the group of rows specified. The lNumRows parameter specifies the number of row handles in the array parameter rhStatusRows. The row status will be retrieved for the row handles in this array. The pStatus array is returned and contains the status of each row in the rhStatusRows array.
The Undo method returns a row to the state it was in before the last time it was written back to a data source. You can use the Undo method on a group of row handles specified by the rhUndoRows parameter that contains an array of row handles. The lNumRows parameter specifies the number of rows in this array. The plNumRowsUndone parameter returns the number of rows that operation attempted to undo. The phRows parameter returns an array of the undone row handles; if this parameter is NULL, this array isn't returned. The pRowStatus parameter can return an array containing the status of each row that was undone; if this parameter is NULL, the row status isn't returned.
Finally, the Update method writes back a set of rows to the data source. You can use the Update method on a group of row handles specified by the rhUpdateRows parameter that contains an array of row handles. The lNumRows parameter specifies the number of rows in this array. The plNumRowsUpdates parameter returns the number of rows that operation attempted to update. The phRows parameter returns an array of the updated row handles; if this parameter is NULL, the array isn't returned. The pRowStatus parameter can return an array containing the status of each row that was updated; if this parameter is NULL, the row status isn't returned.
By now you are probably wondering how you can put the Rowset object and its associated interfaces to work. You already understand how to use OLE DB to connect to a data source, initiate a session, and even execute a command. This section explains a basic six-step plan for accessing the data contained in a row set:
Step 1 uses the GetColumnInfo method to determine the data contained in the row set. To understand this step, think back to Listing 18.4, in which you created an OLE DB application that connects to an ODBC data source and performs a simple query. The query SELECT * FROM Customers creates a row set that contains all the columns for all the records in the CUSTOMERS table. After the Rowset object is created by executing the command, you can call the GetColumnInfo method to retrieve the size, type, precision, and scale.
Listing 19.1 demonstrates how to obtain the IColumnsInfo
interface and call its GetColumnInfo method. In step
6 of this process, you will see how Listing 19.4 integrates all
the code from the listings developed in the past two days. Note
that the code in Listing 19.1 doesn't check the return value from
QueryInterface. Without testing the HRESULT,
there's no way to know whether the pColumnsInfo interface
pointer is valid. You will need to test for the return value in
any production code.
Listing 19.1 Using the GETCOLUMNIFO Method to Determine
What Information Is Stored in a Row Set
1: IColumnsInfo* pColumnsInfo; 2: DBCOLUMNINFO* pDBColumnInfo; 3: ULONG lNumCols; 4: 5: // Obtain access to the IColumnsInfo interface, from the Rowset 6: // object 7: pRowset->QueryInterface(IID_IColumnsInfo, (void **) &pColumnsInfo); 8: 9: // Retrieve the Column Information 10: pColumnsInfo->GetColumnInfo(&lNumCols, &pDBColumnInfo, NULL); 11: 12: // Free the Column Information Interface 13: pColumnsInfo->Release();
This code creates a structure that contains column information. The next step is to create column bindings. Before going on to step 2, you need to know what bindings and Accessors are and how you can use them.
When you retrieve a row of data by using the GetData method, you create a buffer that contains the actual data in the data source row. How do you determine what's stored in this buffer? Where is the buffer stored? OLE DB uses the DBBINDING structure to define the columns stored in this buffer. The DBBINDING structure is defined as follows:
typedef struct tagDBBINDING {
ULONG iOrdinal;
ULONG obValue;
ULONG obLength;
ULONG obStatus;
ITypeInfo * pTypeInfo;
DBOBJECT * pObject;
DBBINDEXT * pBindExt;
DBPART dwPart;
DBMEMOWNER dwMemOwner;
DBPARAMIO eParamIO;
ULONG cbMaxLen;
DWORD dwFlags;
DBTYPE wType;
BYTE bPrecision;
BYTE bScale;
} DBBINDING;
This binding structure defines how columns are stored in the row buffer. The iOrdinal field defines the ordinal position of the field. The obValue field defines the buffer offset (the number of bytes from the beginning of the row buffer) to where the column's value is stored. The obLength field defines the buffer offset to where the length of the column is stored. The obStatus field defines the buffer offset to where the status of the column is stored. The pObject field is used when accessing OLE objects. The dwPart field describes which parts of the buffer are bound.
The dwPart field is created by logically ORing the DBPART_VALUE, DBPART_LENGTH, and DBPART_STATUS constants. These constants define the parts of the column you want to store in the buffer. The dwMemOwner field describes whether the buffer memory is client or provider owned; typically, this field is DBMEMOWNER_CLIENTOWNED. The eParamIO parameter field defines the type of parameter described. The values for this field can be DBPARAMIO_NOTPARAM for fields, DBPARAMIO_INPUT for an input parameter, or DBPARAMIO_OUTPUT for an output parameter. These constants can be logically ORed together.
The cbMaxLen field defines the maximum length of the column. The wType field defines the data type of the column. The bPrecision field defines the column's precision, and the bScale field defines the column's scale. The dwFlags field determines special field attributes, such as whether the field is a bookmark index, whether it can be deferred, and whether it can hold a null value. The pTypeInfo and pBindExt are reserved for future use. The pTypeInfo and pBindExt fields should be NULL.
How do you know what to store in this binding structure? The binding structure should contain an array of binding structures, with an element for each column in the row set. To fill in the fields of the binding structure, you need to return to step 1, where you obtained the DBCOLUMNINFO structure. The DBCOLUMNINFO structure is defined as follows:
typedef tagDBCOLUMNINFO {
LPOLESTR pwszName;
ITypeInfo *pTypeInfo;
ULONG iOrdinal;
DBCOLUMNFLAGS dwFlags;
ULONG ulColumnSize;
DBTYPE wType;
BYTE bPrecision;
BYTE bScale;
DBID columnid;
} DBCOLUMNINFO;
Many fields in this structure are the same fields required by
the binding structure. Therefore, after you obtain the DBCOLUMNINFO
structure, you can allocate space for a DBBINDING structure
array, one element for each column in the row set. You will step
through each column in the DBCOLUMNINFO and fill out
the corresponding DBBINDING array element with the appropriate
information. Listing 19.2 demonstrates step 2, which obtains the
column bindings using column information.
Listing 19.2 Obtaining the Column Bindings Information
from the Column Information Structure
1: // Create a DBBINDING array
2: pBindings = new DBBINDING[lNumCols];
3:
4: // Using the ColumnInfo Structure, Fill Out the pBindings Array
5: for(j = 0; j < lNumCols; j++) {
6: // Ordinal Positions Start at 1
7: pBindings[j].iOrdinal = j+1;
8: // Buffer offset, re-calculated at the end of this loop
9: pBindings[j].obValue = cbColOffset;
10: // You're Not Retrieving the Column Length
11: pBindings[j].obLength = 0;
12: // You're Not Retrieving the Column Status
13: pBindings[j].obStatus = 0;
14: // These Parameters Are for Future Use...
15: pBindings[j].pTypeInfo = NULL;
16: pBindings[j].pObject = NULL;
17: pBindings[j].pBindExt = NULL;
18: // We're Just Retrieving the Value Part
19: pBindings[j].dwPart = DBPART_VALUE;
20: // The Memory Will Be Client Owned
21: pBindings[j].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
22: // This Binding Does Not Define a Parameter
23: pBindings[j].eParamIO = DBPARAMIO_NOTPARAM;
24: // Use the ColumnInfo Structure to Get the Column Size
25: pBindings[j].cbMaxLen = pDBColumnInfo[j].ulColumnSize;
26: pBindings[j].dwFlags = 0;
27: // Use the ColumnInfo Structure to Get the Column Type
28: pBindings[j].wType = pDBColumnInfo[j].wType;
29: // Use the ColumnInfo Structure to Get the Column Precision
30: pBindings[j].bPrecision = pDBColumnInfo[j].bPrecision;
31: // Use the ColumnInfo Structure to Get the Column Scale
32: pBindings[j].bScale = pDBColumnInfo[j].bScale;
33:
34: // Re-calulate the Next Buffer Offset by
35: // Adding the Current Offset to the Maximum Column Length
36: // Obtained from the ColumnsInfo Structure
37: cbColOffset = cbColOffset + pDBColumnInfo[j].ulColumnSize;
38: };
Now that you have created the column bindings, you need to create the row set Accessor.
Accessors are easy. They are just handles to DBBINDING binding structures. These handles are easier and faster to pass as parameters than passing around the whole DBBINDING binding structure.
| TIP |
You can create an Accessor by combining multiple DBBINDING structure arrays. A row set can use more than one Accessor (for different views of the row set data, depending on requirements). Today's focus is on using a single binding structure and single Accessor to access row sets. |
The CreateAccessor method of the IAccessor interface
creates an Accessor handle from a binding structure.
After you have finished using an Accessor handle, you
must release it with the ReleaseAccessor method. Failure
to release the Accessor handle when you have finished
using it will cause a memory leak. Listing 19.3 displays step
3 in the six-step process of retrieving data from a row-creating
an Accessor from the binding information array. Note
that the code in Listing 19.3 doesn't check the return value from
QueryInterface. You will need to test for the return
value in your production code.
Listing 19.3 Creating an Accessor with the Previously
Created Binding Array
1: // Obtain Access to the IAccessor Interface
2: pRowset->QueryInterface(IID_IAccessor, (void **) &pAccessor);
3:
4: // Create an Accessor Handle, Using the CreateAccessor Method
5: pAccessor->CreateAccessor(DBACCESSOR_ROWDATA, // We Are
6: // Retrieving Row
// Data
7: lNumCols, // The Number of
// Columns
8: // We Are
// Binding
9: pBindings, // The Bindings
// Structure
10: 0, // Not Used
11: &hAccessor, // The Returned
// Accessor
12: // Handle
13: NULL); // We're Not
// Returning
14: // Status
// Information
15:
16: // Use The Accessor To Access The Row Data... Steps 4 Through 6
17:
18: // Free Up Allocated Memory
19: pAccessor->ReleaseAccessor(hAccessor, NULL);
20: pAccessor->Release();
You are now halfway through the process of accessing the previously defined row set data. The next two steps retrieve a row from a row set and then retrieve the actual data described by the Accessor you just created. Step 6 is a required housekeeping step that frees up any memory allocated for the retrieved row data.
Two Rowset object methods are instrumental to the process of getting the data: GetNextRows and GetData. The GetNextRows method retrieves a set of rows from a row set. To improve performance, more than one row can be read at a time. The GetData method creates a buffer, which you can read using the binding information. You need to create two loops to read all the rows generated by your query: an outer loop that retrieves a set of rows until all the sets have been returned and an inner loop that loops through these row subsets and prints out the column information they contain. After you use the row subset, you must call the ReleaseRows method to release the memory allocated to the subset.
The GetData method fills a character buffer, and the binding structure determines where each field begins in that buffer. The binding structure contains the obValue field, which defines the offset from the beginning of the buffer to the location of the column's value. This offset value is an index into the buffer returned by the GetData method. Depending on how you use these row values, you might have to do some typecasting.
| NOTE |
The application is responsible for allocating and freeing the buffer used by the GetData method. The size of this buffer is determined as you calculate the current offset when defining the bindings. When that loop is complete, the cbColOffset variable contains the length of the buffer. You allocate a character string of that length. After you have finished using the GetData method, you can free this buffer. |
Now that you understand what's involved in these last three steps, you should be able to assemble all the steps to access and display the row set data. Listing 19.4 presents the complete simple OLE DB application. This application accesses the OrdersDb ODBC data source, creates a session, and executes the SQL command SELECT CustNumber, CustFirstName FROM CUSTOMERS. The application also displays the title of each column and the data contained in each row. The output of this application appears in Listing 19.5.
To build the application, run Visual Studio and select File, New. Click the Projects tab and specify a Win32 Console Application. Call the application ROWACCESS. Click OK, specify that you want to create an empty project, and click Finish. After AppWizard runs, create a new C++ source file as part of the project. You can call it whatever you think is appropriate, such as ROWACCESS.CPP. Enter the code shown in Listing 19.4 into the source file.
You will need to change the input libraries for the linker to the following:
oledbd.lib kernel32.lib user32.lib gdi32.lib winspool.lib comdlg32.lib
advapi32.lib
shell32.lib ole32.lib oleaut32.lib uuid.lib
You do this under Project, Settings on the Link tab. When you build the project, it should compile and link with no errors or warnings. Note that the code in Listing 19.4 doesn't check the return value from QueryInterface. You will need to test for the return value in your production code.
Listing 19.4 The Completed ROWACCESS Application,
Demonstrating How to Access and Display Query Result Row Data
1: #define UNICODE
2: #define _UNICODE
3: #define DBINITCONSTANTS
4: #define INITGUID
5:
6: // Standard Application Includes
7: #include <windows.h>
8: #include <stdio.h>
9: #include <tchar.h>
10: #include <stddef.h>
11: #include <iostream.h>
12:
13: // OLE DB Header Files
14: #include <oledb.h>
15: #include <oledberr.h>
16:
17: // OLE DB - ODBC Provider Header Files
18: #include <msdaguid.h>
19: #include <msdasql.h>
20:
21: void main() {
22: IDBInitialize* pIDBInitialize = NULL;
23: IDBCreateSession* pCreateSession = NULL;
24: IDBCreateCommand* pCreateCommand = NULL;
25: IRowset* pRowset = NULL;
26: ICommandText* pCommandText = NULL;
27: IDBProperties* pIDBProperties;
28: IColumnsInfo* pColumnsInfo;
29: IAccessor* pAccessor;
30: HACCESSOR hAccessor;
31: DBCOLUMNINFO* pDBColumnInfo;
32: DBPROP InitProperties[4];
33: DBPROPSET rgInitPropSet[1];
34: int i;
35: ULONG j,
36: lNumCols,
37: cbColOffset = 0;
38: ULONG lNumRowsRetrieved;
39: HROW hRows[5];
40: HROW* pRows = &hRows[0];
41: LONG cNumRows;
42: DBBINDING* pBindings;
43: char *pBuffer;
44: WCHAR* pStringsBuffer;
45:
46: // The Command to execute
47: LPCOLESTR wCmdString =
48: OLESTR("SELECT CustNumber, CustFirstName FROM CUSTOMERS");
49:
50: // Initialize the Component Object Module Library
51: CoInitialize(NULL);
52:
53: // Obtain Access to the OLE DB - ODBC Provider
54: CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
55: IID_IDBInitialize, (void **) &pIDBInitialize);
56:
57: // Initialize the property values that are the same for each
58: // property...
59: for (i = 0; i < 4; i++ ) {
60: VariantInit(&InitProperties[i].vValue);
61: InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
62: InitProperties[i].colid = DB_NULLID;
63: }
64:
65: // level of prompting that will be done to complete the
66: // connection process
67: InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
68: InitProperties[0].vValue.vt = VT_I2;
69: InitProperties[0].vValue.iVal = DBPROMPT_NOPROMPT;
70:
71: // Specify the Username
72: InitProperties[1].dwPropertyID = DBPROP_AUTH_USERID;
73: InitProperties[1].vValue.vt = VT_BSTR;
74: // Note: The L cast directive casts the string into a UNICODE
// string....
75: InitProperties[1].vValue.bstrVal = SysAllocString((LPOLESTR)L"");
76:
77: // Specify the appropriate Password
78: InitProperties[2].dwPropertyID = DBPROP_AUTH_PASSWORD;
79: InitProperties[2].vValue.vt = VT_BSTR;
80: InitProperties[2].vValue.bstrVal = SysAllocString((LPOLESTR)L"");
81:
82: // Specify the Data Source name
83: InitProperties[3].dwPropertyID = DBPROP_INIT_DATASOURCE;
84: InitProperties[3].vValue.vt = VT_BSTR;
85: InitProperties[3].vValue.bstrVal =
86: SysAllocString((LPOLESTR)L"OrdersDb");
87:
88:
89: rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
90: rgInitPropSet[0].cProperties = 4;
91: rgInitPropSet[0].rgProperties = InitProperties;
92:
93: // set initialization properties
94: pIDBInitialize->QueryInterface(IID_IDBProperties,
95: (void **)&pIDBProperties);
96: pIDBProperties->SetProperties(1,rgInitPropSet);
97: pIDBProperties->Release();
98:
99: // Call the Initialize method to establish the connection to
100: // the ODBC data source specified above
101: pIDBInitialize->Initialize();
102:
103: // Create a Session object...
104: pIDBInitialize->QueryInterface(IID_IDBCreateSession, (void **)
105: &pCreateSession);
106:
107: // Create a Command object...
108: pCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
109: (IUnknown **) &pCreateCommand);
110:
111: // Access the ICommandText interface
112: pCreateCommand->CreateCommand(NULL, IID_ICommandText, (IUnknown **)
113: &pCommandText);
114:
115: // Specify the command text
116: pCommandText->SetCommandText(DBGUID_DBSQL, wCmdString);
117:
118: // Execute the command
119: pCommandText->Execute(NULL, IID_IRowset, NULL, &cNumRows,
120: (IUnknown **) &pRowset);
121:
122: // *******************************************************************
123: // Step 1: Determine the data contained in the row set, using the
124: // GetColumnInfo method.
125: // *******************************************************************
126:
127: // Obtain access to the IColumnsInfo interface, from the Rowset
// object
128: pRowset->QueryInterface(IID_IColumnsInfo, (void **) &pColumnsInfo);
129:
130: // Retrieve the Column Information
131: pColumnsInfo->GetColumnInfo(&lNumCols, &pDBColumnInfo,
&pStringsBuffer);
132:
133: // Free the Column Information Interface
134: pColumnsInfo->Release();
135:
136: // *******************************************************************
137: // Step 2: Obtain the column bindings, using column information.
138: // *******************************************************************
139:
140: // Create a DBBINDING array
141: pBindings = new DBBINDING[lNumCols];
142:
143: // Using the ColumnInfo Structure, Fill Out the pBindings Array
144: for(j = 0; j < lNumCols; j++) {
145: // Ordinal Positions Start at 1
146: pBindings[j].iOrdinal = j+1;
147: // Buffer offset, re-calculated at the end of this loop
148: pBindings[j].obValue = cbColOffset;
149: // We're Not Retrieving the Column Length
150: pBindings[j].obLength = 0;
151: // We're Not Retrieving the Column Status
152: pBindings[j].obStatus = 0;
153: // These Parameters Are for Future Use...
154: pBindings[j].pTypeInfo = NULL;
155: pBindings[j].pObject = NULL;
156: pBindings[j].pBindExt = NULL;
157: // We're Just Retrieving the Value Part
158: pBindings[j].dwPart = DBPART_VALUE;
159: // The Memory Will Be Client Owned
160: pBindings[j].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
161: // This Binding Does Not Define a Parameter
162: pBindings[j].eParamIO = DBPARAMIO_NOTPARAM;
163: // Use the ColumnInfo Structure to Get the Column Size
164: pBindings[j].cbMaxLen = pDBColumnInfo[j].ulColumnSize;
165: pBindings[j].dwFlags = 0;
166: // Use the ColumnInfo Structure to Get the Column Type
167: pBindings[j].wType = pDBColumnInfo[j].wType;
168: // Use the ColumnInfo Structure to Get the Column Precision
169: pBindings[j].bPrecision = pDBColumnInfo[j].bPrecision;
170: // Use the ColumnInfo Structure to Get the Column Scale
171: pBindings[j].bScale = pDBColumnInfo[j].bScale;
172:
173: // Re-calulate the Next Buffer Offset by
174: // Adding the Current Offset to the Maximum Column Length
175: // Obtained from the ColumnsInfo Structure
176: // Note: When Done with This Loop, This Value Will Be the
177: // Length Of the Record Buffer
178: cbColOffset = cbColOffset + pDBColumnInfo[j].ulColumnSize;
179: };
180:
181: // *******************************************************************
182: // Step 3: Create an Accessor, using the binding information and the
183: // CreateAccessor method.
184: // *******************************************************************
185:
186: // Obtain Access to the IAccessor Interface
187: pRowset->QueryInterface(IID_IAccessor, (void **) &pAccessor);
188:
189: // Create an Accessor Handle, Using the CreateAccessor Method
190: pAccessor->CreateAccessor(DBACCESSOR_ROWDATA, // We Are Retrieving
191: // Row Data
192: lNumCols, // The Number of
// Columns
193: // We Are Binding
194: pBindings, // The Bindings
// Structure
195: 0, // Not used
196: &hAccessor, // The Returned
// Accessor
197: // Handle
198: NULL); // We're Not
// Returning
199: // Status
// Information
200:
201: // Loop through the ColumnInfo Structure and Display
202: // Each Column Name...
203: for(j = 0; j < lNumCols; j++) {
204: // Note: the %S Let's Us Print Out UNICODE Strings from printf
205: printf("%S\t",pDBColumnInfo[j].pwszName);
206: };
207: printf("\n-------------------------------------------------------- \n");
208:
209: // *******************************************************************
210: // Step 4: Retrieve the row set handles, using the GetNextRows // method.
211: // *******************************************************************
212:
213: // Get a Set of 5 Rows...
214: pRowset->GetNextRows(0, // For future use
215: 0, // Number of Rows to // Skip
216: 5, // Number of Rows to
217: // Retrieve
218: &lNumRowsRetrieved, // Number of Rows
219: // Returned
220: &pRows); // The Row Buffer
221:
222: // Allocate Space for the Row Buffer
223: pBuffer = new char[cbColOffset];
224:
225: // While We Have Retrieved Some Rows...Display Them
226: while(lNumRowsRetrieved > 0) {
227: // For Each Row in the Set We Retrieved, Print Out the
228: // Fields We Retrieved...
229: for(j = 0; j < lNumRowsRetrieved; j++) {
230: // Clear the Buffer
231: memset(pBuffer, 0, cbColOffset);
232:
233: // *******************************************************************
234: // Step 5: Retrieve the actual row data using the GetData method.
235: // *******************************************************************
236:
237: // Get the Row Data Values
238: pRowset->GetData(hRows[j], hAccessor, pBuffer);
239: // Print Out The Field Values
240: // Note Our Typecast of the First Column to a Long, Because
241: // It's a Numeric Field...Also We Will Print Only the First
242: // 40 Characters of the Second Column
243: // Also, We Use the obvalue Value to Index The Buffer
244: printf("%ld\t%.40s\n", (ULONG) pBuffer[pBindings[0].obValue],
245: &pBuffer[pBindings[1].obValue]);
246: };
247:
248: // *******************************************************************
249: // Step 6: Release the row handles before retrieving the next set of
250: // row handles.
251: // *******************************************************************
252:
253: // Release the Rows Retrieved...
254: pRowset->ReleaseRows(lNumRowsRetrieved, hRows, NULL, NULL, NULL);
255: // Get a Set of 5 Rows...
256: pRowset->GetNextRows(0, // For future use
257: 0, // Number of Rows to
// Skip
258: 5, // Number of Rows to
259: // Retrieve
260: &lNumRowsRetrieved, // Number of Rows
261: // Returned
262: &pRows); // The Row Buffer
263:
264: };
265:
266: // Free Up All Allocated Memory
267: delete [] pBuffer;
268: pAccessor->ReleaseAccessor(hAccessor, NULL);
269: pAccessor->Release();
270: delete [] pBindings;
271: pRowset->Release();
272: pCommandText->Release();
273: pCreateCommand->Release();
274: pCreateSession->Release();
275: pIDBInitialize->Uninitialize();
276: pIDBInitialize->Release();
277:
278: // Release the Component Object Module Library
279: CoUninitialize();
280:
281: };
The code in Listing 19.5 should produce this output:
1: CustNumber CustFirstName 2: ------------------------------ 3: 1 Bruce 4: 2 Homer 5: 3 Clark 6: 4 John 7: 5 Bill
The key feature of this simple application is that it isn't tied to any particular query result. You can modify the SQL command to access a different number of rows or for different tables, and the application will still work!
Today's discussion of how to access row set information continues by examining the more advanced aspects of data access and navigation: column types supported, handling these various types, defining and using cursors, using bookmarks, and making changes to row data.
Listing 19.4 shows you how to navigate a row set sequentially, using the GetNextRows method of the IRowset interface. As you know, a cursor is a type of pointer that points to the current row you are accessing. With the GetNextRows method, the cursor starts at the beginning of the row set and moves sequentially through the row set as you call the GetNextRows method.
As demonstrated in the example, you can use the GetNextRows method to move the cursor a number of rows at a time. The GetNextRows method functions until the end of the row set; then the RestartPosition method of the IRowset interface repositions the cursor to the beginning of the row set.
If a data provider supports nonsequential access, it can access rows based on a key value, called a bookmark in OLE DB terminology. OLE DB supports two types of bookmarks: numeric and key value bookmarks.
| TIP |
Check the DBPROP_BOOKMARKTYPE of the DataSource object to determine whether a data provider supports bookmarks. |
As you may recall from the discussion of bindings earlier today,
you can use the dwFlags field of the binding structure
to indicate whether a field can be used as a bookmark. This is
done by setting the dwFlags field to the DBCOLUMNSINFO_ISBOOKMARK
flag. If a field is a bookmark, it can be used as a search key.
OLE DB also defines a set of standard bookmarks, which are outlined
in Table 19.1.
| DBBMK_INVALID | An undefined bookmark |
| DBBMK_FIRST | A bookmark to the first row |
| DBBMK_LAST | A bookmark to the last row |
Bookmarks are valid only while a row set is opened. After the
row set is closed, all bookmarks on that row set become invalid.
The IRowsetLocate interface uses bookmarks to reposition
the row set cursor to access records nonsequentially. Listing
19.5 demonstrates how the GetRowsAt method retrieves
a specific row. This example assumes that the same query that
was executed in Listing 19.4 and assumes that the CustomerID
field has been defined as a bookmark type field. Note that the
code in Listing 19.5 doesn't check the return value from QueryInterface.
You will need to test for the return value in your production
code.
Listing 19.5 Using the GETROWSAT Method to Jump
to a Particular Record
1: // Lookup CustomerID 3!
2: lLookup = 3;
3:
4: // Obtain Access to the IRowsetLocate Interface
5: pRowset->QueryInterface(IID_IAccessor, (void **) &pRowsetLocate);
6: pRowsetLocate->GetRowsAt(0, // Reserved for Future
// Use
7: NULL, // Reserved for Future
// Use
8: sizeof(lLookup), // Size of the
// Bookmark
9: (BYTE *) &lLookup, // The Bookmark
10: 0, // Number of Rows to
// Skip
11: 1, // Number of Rows to
// Retrieve
12: &lNumRowsRetrieved, // Number of Rows
// Retrieved
13: &pRows); // Row Handles
14:
15: // Clear the Buffer and Retrieve the Data
16: memset(pBuffer, 0, cbColOffset);
17: pRowset->GetData(hRows[0], hAccessor, pBuffer);
18: // Print Out The Field Values
19: printf("%ld\t%.40s\n", (ULONG) pBuffer[pBindings[0].obValue],
20: &pBuffer[pBindings[1].obValue]);
21: pRowset->ReleaseRows(lNumRowsRetrieved, hRows, NULL, NULL, NULL);
One way to improve application performance is to set up columns to be deferred, that is, retrieved only when the GetData method is called. If a column isn't deferred, its value is read when it is retrieved from the data source. Deferring the column read can improve performance, especially if you retrieve large columns or a large number of columns. To specify reading a column only when the GetData method is called, you must set the DBPROP_DEFERRED property for the column. If a column contains an OLE object, it is set to be a deferred column by default.
OLE DB supports all the standard Windows data types. You can use
these types to describe database columns. A column's type is found
in the wType field of both of the DBCOLUMNINFO
and DBBINDING structures. Table 19.2 lists all the column
type constants and the associated descriptions that OLE DB supports.
| DBTYPE_EMPTY | A type was not specified. Used when defining variant type fields. |
| DBTYPE_NULL | A NULL value. Used when defining variant type fields. |
| DBTYPE_RESERVED | Reserved for future use. |
| DBTYPE_I1 | A single-byte integer, signed. |
| DBTYPE_I2 | A 2-byte integer, signed. |
| DBTYPE_I4 | A 4-byte integer, signed |
| DBTYPE_I8 | An 8-byte integer, signed. |
| DBTYPE_UI1 | A single-byte integer, unsigned. |
| DBTYPE_UI2 | A 2-byte integer, unsigned. |
| DBTYPE_UI4 | A 4-byte integer, unsigned. |
| DBTYPE_UI8 | An 8-byte integer, unsigned. |
| DBTYPE_R4 | A single-precision floating point. |
| DBTYPE_R8 | A double-precision floating point. |
| DBTYPE_CY | A currency value. |
| DBTYPE_DECIMAL | An exact numeric decimal value, stored in OLE form. |
| DBTYPE_NUMERIC | An exact numeric decimal value, stored in standard form. |
| DBTYPE_DATE | A date stored in OLE form. |
| DBTYPE_BOOL | A Boolean value, stored in OLE form. |
| DBTYPE_BYTES | An array of bytes. The length is specified by the cbMaxLen field. |
| DBTYPE_BSTR | A Unicode character string. The length of the string is stored in the first two bytes. |
| DBTYPE_STR | An ANSI NULL-terminated character string. |
| DBTYPE_WSTR | A Unicode NULL-terminated character string. |
| DBTYPE_VARIANT | A variant in OLE form. |
| DBTYPE_IDISPATCH | A pointer to an OLE object. |
| DBTYPE_IUNKNOWN | A pointer to an OLE interface. |
| DBTYPE_GUID | A GUID (Globally Unique Identifier). |
| DBTYPE_ERROR | An error code. |
| DBTYPE_BYREF | A pointer to a type. Used in combination with the other data types listed here. For example, to specify a pointer to a single-byte signed integer, use DBTYPE_I1 | DBTYPE_BYREF. (Note: The | specifies a logical OR operation.) |
| DBTYPE_ARRAY | A pointer to a SAFEARRAY. |
| DBTYPE_VECTOR | A DBVECTOR structure used to define an array of another type; used in conjunction with another type. For example, to specify a vector of single-byte signed integers, use DBTYPE_I1 | DBTYPE_VECTOR. |
| DBTYPE_UDT | A user-defined data type. |
| DBTYPE_DBDATE | A DBDATE structure. |
| DBTYPE_DBTIME | A DBTIME structure. |
| DBTYPE_DBTIMESTAMP | A DBTIMESTAMP structure. |
Binary Large Objects (BLOBs) can hold images, long text fields,
or other large binary field types. OLE DB supports BLOB fields
if your data provider also supports them. Consult your data source
documentation to determine how to create a BLOB type field. BLOB
columns can be retrieved as one large chunk and stored in memory,
or they can be retrieved one piece at a time. When you are accessing
a BLOB column or one chunk of data, the process of retrieving
the field data is the same as for any other column. The only difference
is that you need to allocate a large buffer. Listing 19.6 demonstrates
how to retrieve a 7,500-byte BLOB column from a data source. This
example assumes that this column will never be bigger than 7,500
bytes. Typically, a BLOB field has the type DBTYPE_BYTES,
DBTYPE_WSTR, or DBTYPE_STR.
Listing 19.6 How to Retrieve a BLOB Column as a
Single Memory Object
1: #define BLOB_LENGTH 7500
2:
3: HACCESSOR hAccessor;
4: DBBINDING pBinding[1];
5: IRowset *pRowset;
6: IAccessor *pAccessor;
7: void *pData;
8: HROW *phRow;
9: ULONG lNumRows;
10:
11: // The First Column
12: pBindings[0].iOrdinal = 1;
13: // Buffer offset, Just one column in our row set
14: pBindings[0].obValue = 0;
15: // We're Not Retrieving the Column Length
16: pBindings[0].obLength = 0;
17: // We're Not Retrieving the Column Status
18: pBindings[0].obStatus = 0;
19: // These Parameters Are for Future Use...
20: pBindings[0].pTypeInfo = NULL;
21: pBindings[0].pObject = NULL;
22: pBindings[0].pBindExt = NULL;
23: // We're Just Retrieving the Value Part
24: pBindings[0].dwPart = DBPART_VALUE;
25: // The Memory Will Be Client Owned
26: pBindings[0].dwMemOwner = DBMEMOWNER_CLIENTOWNED;
27: // This Binding Does Not Define a Parameter
28: pBindings[0].eParamIO = DBPARAMIO_NOTPARAM;
29: // A 7500 Byte Wide Column
30: pBindings[0].cbMaxLen = BLOB_LENGTH;
31: pBindings[0].dwFlags = 0;
32: // Use the ColumnInfo Structure to Get the Column Type
33: pBindings[0].wType = DBTYPE_BYTES;
34: // Use the ColumnInfo Structure to Get the Column Precision
35: pBindings[0].bPrecision = pDBColumnInfo[j].bPrecision;
36: // Use the ColumnInfo Structure to Get the Column Scale
37: pBindings[0].bScale = pDBColumnInfo[j].bScale;
38:
39: // Obtain Accessor Interface
40: pRowset->QueryInterface(IID_Accessor, (void **) &pAccessor);
41:
42: // Create an Accessor, Using the Binding Information
43: pAccessor->CreateAccessor(DBACCESSOR_ROWDATA, // A Row
44: 1, // 1 Column
45: pBinding, // The Bindings
46: BLOB_LENGTH, // 7500 bytes
47: &hAccessor, // The Accessor
48: NULL); // No Status
// Returned
49:
50: Data = new char[BLOB_LENGTH]; // Cast it to the appropriate type
// later
51:
52: pRowset->GetNextRows(NULL, 0, 1, &lNumRows, &phRow);
53: pRowset->GetData(phRow[0], hAccessor, pData);
54 // Remember to release pAccessor;
As this example shows, the process of accessing a BLOB column as a single in-memory object isn't very difficult. You can also access a BLOB column by using the OLE streaming interfaces. Day 20 covers these interfaces in more detail, including how to use them to access BLOB columns.
Unicode strings are used to support international character sets. Many strings defined by OLE DB are defined as Unicode character strings. Each Unicode character is 16-bits, twice the size of an ANSI character string. For the most part, NULL-terminated Unicode strings can be manipulated like NULL-terminated ANSI strings. The following special considerations apply to Unicode strings:
The final topic for today is cursors. Cursors are used to navigate a row set. If you have used Data Access Objects (DAO), Remote Data Objects (RDO), or Open Database Connectivity (ODBC) you are probably already familiar with different types of cursors that facilitate record navigation and locking. This discussion concentrates on the various types of cursors, why they are used, and the Rowset object properties that must be set to invoke them. Tomorrow's topics (Day 20) include OLE DB object properties and the implementation of cursors in more detail.
When using a Static cursor, the order of the rows is in the natural data source order, and if the row set is changed in any way while it is opened, those changes aren't reflected. Changes are recognized only when the row set is closed and reopened. To specify that a row set uses a static type of cursor, the DBPROP_CANSCROLLBACKWARDS property is set to VARIANT_TRUE and the DBPROP_OTHERINSERT and DBPROP_OTHERUPDATEDELETE properties are set to VARIANT_FALSE.
When you use a KeySet cursor, the order of the rows is in some sorted order based on a key. As with a Static cursor, if the row set is changed in any way while it is opened, those changes aren't reflected immediately. If a row is updated, the changes will be reflected the next time the row is retrieved, but if a row is deleted or inserted, the change is recognized only when the row set is closed and reopened. To specify that a row set uses a KeySet type of cursor, the DBPROP_CANSCROLLBACKWARDS and DBPROP_OTHERUPDATEDELETE properties are set to VARIANT_TRUE, and the DBPROP_OTHERINSERT is set to VARIANT_FALSE.
When you use a Dynamic cursor, the currently open row set actively reflects all changes to the row set. Changes are reflected the next time the row is retrieved. To specify that a row set should use a Dynamic type of cursor, the DBPROP_CANSCROLLBACKWARDS, DBPROP_OTHERUPDATEDELETE, and DBPROP_OTHERINSERT properties are set to VARIANT_TRUE.
Day 19 opens with a survey of the Rowset object and its interfaces, then sets out a six-step plan for retrieving data, and finally explains how to handle BLOB columns and specify various cursor types.
Today you learned how to access row set data sequentially, how to use bookmarks to access row set data in a random fashion, and how to use deferred access to improve application performance. Day 19 ends with a discussion of different types of cursors, how they affect row set access, and the Rowset properties that must be set to invoke these cursor types. The discussion of OLE DB continues tomorrow with a more detailed look at properties, transactions, and OLE DB streaming mechanisms.
This section answers some common questions related to today's topics.
| Does OLE DB provide any way for me to search for a particular value in a row set? | |
| OLE DB provides the IRowsetIndex interface, which I didn't discuss in this lesson. You can use this interface with an associated data source index to search for specific rows in a row set, based on key values. A data provider must support indexing to use this interface. | |
| Are queries the only way to create row sets? | |
| No, row sets can be generated directly from the Session object if a data provider doesn't support the Rowset object. Row sets generated in this manner will contain the complete contents of a data provider object in a tabular form. (In the case of a database, a data provider object would typically be the contents of a table.) The IDBSchemaRowset interface can also be used to generate row sets that contain data source schema information. | |
| How does OLE DB handle multiuser row set access? | |
| OLE DB provides a Transaction object for managing record locking and sharing in a multiuser environment. Day 20 covers the Transaction object in more detail. |
The Workshop quiz questions test your understanding of today's material. (The answers appear in Appendix F, "Answers.") The exercises encourage you to apply the information you learned today to real-life situations.
© Copyright, Sams Publishing. All rights reserved.