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

Day 20
      Properties, Transactions, and Indexes

• Properties and Groups
  • Getting Properties
  • Setting Properties
  • A Review of OLE DB Object Properties
• Transactions
  • The ITransaction Interface
  • The ITransactionLocal Interface
  • The ITransactionOptions Interface
  • The ITransactionObject Interface
  • The ITransactionJoin Interface
  • Creating Transactions
  • Committing and Aborting Transactions
  • Nesting Transactions
  • Isolation Levels and Locking
• The Index Object
  • The IRowsetIndex Interface
  • Using the Index Object
• Summary
• Q&A
• Workshop
  • Quiz
  • Exercises

Today's lesson covers three important topics in OLE DB application development: properties, transactions, and the Index object. You have already learned about OLE DB objects and have used their methods, but you haven't yet discovered how to change the state of an object. Today you will learn how to use properties to control the state of an object, or in other words, how to set and retrieve property values of OLE DB objects by using special interfaces. The other major topics for today are the Transaction object and the Index object.

Today you will

• Learn how to use properties and groups to set the state of various OLE DB objects by using the interfaces they provide.
• Retrieve and set property values.
• Explore the major properties supported by OLE DB objects.
• Create, commit, and abort Transaction objects by using the supported interfaces.
• Use transactions to ensure data security and integrity.
• Use the interfaces supported by the Index object.
• Use the Index object to search for a row in the row set by using a key value.

Properties and Groups

Properties specify the attributes of an OLE DB object and can determine how the object behaves. Some properties can only be read, whereas others can be read and written. Properties are identified by globally unique identifier (GUID) constants and grouped according to the object on which they function. The following objects have special properties: Columns, DataSource, DataSource initialization objects, Index, Rowset, Session, and Table. Today's explanation of properties begins by discussing how to retrieve a property value.

Getting Properties

Most OLE DB objects use the GetProperties method to retrieve property values. Table 20.1 summarizes the methods used by each OLE DB object to access its properties. Because most objects use the GetProperties method, this section focuses on that mechanism for retrieving property values.

The GetProperties method is defined as follows:

GetProperties(ULONG lNumPropIDs, DBPROPIDSET rPropIDs[],
              ULONG lNumPropSets, DBPROPSET **prPropSets);

The lNumPropIDs parameter specifies the number of elements in the rPropIDs array. The rPropIDs array contains a collection of DBPROPIDSET structures that specify the collection of properties for which you want to see values. The DBPROPIDSET structure is defined as follows:

typedef struct tagDBPROPIDSET {
     DBPROPID *rgPropertyIDs;
     ULONG    cPropertyIDs;
     GUID     guidPropertySet;
} DBPROPIDSET;

The rgPropertyIDs field defines an array of Property IDs. That is, the DBPROPIDSET holds a collection of property IDs, and the GetProperties method actually takes a collection of DBPROPIDSET structures, which in themselves contain a collection of property IDs. You will typically define a single DBPROPIDSET structure, which contains a collection of the property IDs you want to retrieve. The cPropertyIDs field defines the number of elements in the rgPropertyIDs array. The guidPropertySet field holds a GUID that defines the property group to which the properties in the rgPropertyIDs array belong. Table 20.2 defines the group GUID constants and the OLE DB object they define.

To retrieve a property value, you must create the DBPROPIDSET structure that contains the properties you want to retrieve. On return, the prPropSet parameter contains a pointer to an array of DBPROPSET structures, and each array element contains a collection of property values. The DBPROPSET structure is defined as the following:

typedef struct tagDBPROPSET {
     DBPROP *rgProperties;
     ULONG   cProperties;
     GUID    guidPropertySet;
} DBPROPSET;

The rgProperties field defines an array of DBPROP structures. The DBPROP structures hold the actual property values. The cProperties field defines the number of elements in the rgProperties array. The guidProperties defines the property group to which the properties in the rgProperties array belong. The DBPROP structure, which holds the actual property values, is defined as follows:

typedef struct tagDBPROP {
     DBPROPID      dwPropertyID;
     DBPROPOPTIONS dwOptions;
     DBPROPSTATUS  dwStatus;
     DBID          colid;
     VARIANT       vValue;
} DBPROP;

The dwPropertyID field defines the property ID; it is defined as a simple DWORD type. The dwOptions field determines whether the property is optional or required and what to do if much of processing is required to set the value. The dwOptions field is a combination of the constants: DBPROPOPTIONS_REQUIRED if the value is required and DBPROPOPTIONS_SETIFCHEAP to determine whether it should be set if it takes much processing. The dwStatus determines the status of the property. Table 20.3 defines the possible values of the dwStatus field and their meanings. The colid field defines the column for which this property is defined. The colid is a DBID type structure, as you might recall from Day 18, "Querying a Data Source." Finally the vValue field defines the value of the property; it's defined as a standard C++ VARIANT type.

Now that you have the background information you need in order to use the GetProperties method, you can construct the appropriate structures to retrieve a property's value and interpret the results. You need to define one structure to define the property ID you want to retrieve, and you need to define one structure to return the actual property values. (A listing and descriptions of OLE DB object property IDs appear later in this lesson.)

In the Day 18 compendium of the SQL command language, I discussed the use of the GetProperties method of the IDBProperties interface to determine whether the data provider supported the SQL command language. The property ID to check for SQL support is DPROP_SQLSUPPORT. This property is represented as a long integer, which determines the level of SQL support provided by the data provider. Listing 20.1 demonstrates how to retrieve the DBPROP_SQLSUPPORT property of the DataSource object to determine the level of SQL support provided by the data provider. Note that for brevity, the code in Listing 20.1 doesn't check the return value from GetProperties. Without testing the HRESULT, you have no way of knowing whether the properties were actually returned. In your production code, you will need to test for the return value.

Listing 20.1  Determining the Level of SQL Support Provided by a Data Provider, Using the DBSQL_SUPPORT Property

 1:    PropID =  DBPROP_SQLSUPPORT;              // SQL Support
 2:                                              // Interface
 3:    PropIDSet.rgPropertyIDs = &PropID;        // Specify the
 4:                                              // property ID
 5:    PropIDSet.cPropertyIDs = 1;               // Only one Property ID
 6:    PropIDSet.guidPropertySet = DBPROPSET_DATASOURCEINFO;
                                                 // It's a DataSource
 7:                                              // object Property
 8:
 9:    // Retrieve the Properties
10:    pIDBProperties->GetProperties(1, &PropIDSet, &lNumProps,
                                        &pPropSet);
11:
12:    // Interpret the property value
13:    if(pPropSet->rgProperties->vValue.lVal & DBPROPVAL_SQL_NONE) {
14:       cout << "SQL Commands are not supported\n";
15:    };
16:
17:    if(pPropSet->rgProperties->vValue.lVal &
             DBPROPVAL_SQL_ODBC_MINIMUM) {
18:       cout << "ODBC Minimum SQL Commands supported\n";
19:    };
20:
21:    if(pPropSet->rgProperties->vValue.lVal &
             DBPROPVAL_SQL_ODBC_CORE) {
22:       cout << "ODBC Core SQL Commands supported\n";
23:    };
24:
25:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_ODBC_EXTENDED) {
26:       cout << "ODBC Extended SQL Commands supported\n";
27:    };
28:
29:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_ANSI92_ENTRY) {
30:       cout << "ANSI 92 Entry SQL Commands supported\n";
31:    };
32:
33:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_FIPS_TRANSITIONAL) {
34:       cout << "FIPS Transitional SQL Commands supported\n";
35:    };
36:
37:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_ANSI92_INTERMEDIATE) {
38:       cout << "ANSI 92 Intermediate SQL Commands supported\n";
39:    };
40: 
41:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_ANSI92_FULL) {
42:       cout << "ANSI 92 Full SQL Commands supported\n";
43:    };
44:
45:    if(pPropSet->rgProperties->vValue.lVal &
              DBPROPVAL_SQL_ANSI89_IEF) {
46:       cout << "ANSI 89 Integrity Enhancement Facility
                 SQL Commands supported\n";
47:    };

Setting Properties

To establish a connection to the DataSource object by using the OLE DB ODBC provider, you used the SetProperties method (refer to Day 18) to specify the required ODBC connection parameters. The primary method that OLE DB objects use to specify property values is the SetProperties method. The SetProperties method is defined as follows:

SetProperties(ULONG cPropertySets, DBPROPSET rgPropertySets[]);

The cPropertySets parameter specifies the number of elements in the rgPropertySets array. This array contains a collection of property values. The rgPropertySets parameter is a DBPROPSET type, discussed earlier today. To set the value of a property, you need to create the rgPropertySets array and call the SetProperties method.

Property status values are listed in Table 20.3. Table 20.4 lists the methods that each OLE DB object uses in order to specify property values.

Now that you understand the structures involved in specifying a property, you can specify a property value and check the status of the property set operation. Listing 20.2 demonstrates how to use the DBPROP_INIT_TIMEOUT property. (The complete source code is on the CD-ROM.) This property is part of the DBPROPSET_DBINIT group and is used to specify the number of seconds to wait while a connection to the data source is established. Note that for brevity, the code in Listing 20.2 doesn't check the return values. In your production code, you will need to test for the return values.

Listing 20.2  How to Use the SETPROPERTIES Method to Set the DBPROP_INIT_TIMEOUT Property

 1:    // Obtain Access To The OLE DB - ODBC Provider
 2:    CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER,
 3:                      IID_IDBInitialize, (void **) &pIDBInitialize);
 4:
 5:    // Initialize the property values
 6:    VariantInit(&InitProperties[0].vValue);
 7:    InitProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
 8:    InitProperties[0].colid = DB_NULLID;
 9:    InitProperties[0].dwPropertyID = DBPROP_INIT_TIMEOUT;
10:    InitProperties[0].vValue.vt = VT_I4;
11:
12:    // Set the timeout value to 90 seconds
13:    InitProperties[0].vValue.lVal = 90;
14:
15:    // Specify the Property Set
16:    rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
17:    rgInitPropSet[0].cProperties = 1;
18:    rgInitPropSet[0].rgProperties = InitProperties;
19:
20:    // set initialization properties
21:    pIDBInitialize->QueryInterface(IID_IDBProperties,
                                          (void **)&pIDBProperties);
22:    pIDBProperties->SetProperties(1,rgInitPropSet);
23:    // Remember to release pIDBInitialize and pIDBProperties.

A Review of OLE DB Object Properties

Table 20.5 lists all the properties that OLE DB objects support. This table lists each property ID and its variant type. An asterisk (*) after a property name signifies that it is a read-only property.

Now that you understand how to set and retrieve properties, and you know all the properties supported by OLE DB objects, you can begin to manipulate OLE DB objects at a higher level of detail. You will use these properties in the examples presented during the remainder of the book.

Transactions

Transactions control operations on a data source and encapsulate data source operations. A transaction starts before an operation begins and can commit the operation or abort the changes an operation makes. Transactions can coordinate multiuser operations on a data source. Typically, transactions enable a user to start some data source operation and to abort this operation at a later point. The use of transactions can also significantly improve application performance. The OLE DB TTransaction CoType is defined as follows:

TTransaction {
     interface IConnectionPointContainer;    // Required Interface
     interface ITransaction;                 // Required Interface
     interface ISupportErrorInfo;
};

The IConnectionPointContainer and ISupportErrorInfo are discussed in previous lessons (Day 19, "Navigating the Result of a Query," and Day 17, "Accessing a Data Source with OLE DB"), and error handling is discussed in more detail on Day 21, "OLE DB Error Handling." On Day 17 you learned that the Session object supports the following Transaction-related interfaces: ITransaction, ITransactionJoin, ITransactionLocal, and ITransactionObject. I'll discuss these interfaces in detail before examining the actual mechanics of using them.

The ITransaction Interface

The ITransaction interface controls a transaction; it commits transactions, aborts transactions, and obtains the current transaction status. The ITransaction interface is required by the Transaction object but optional for the Session object. The ITransaction interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides three additional methods: Abort, Commit, and GetTransactionInfo. These methods are defined as follows:

HRESULT Abort(BOID *pboidReason, BOOL fRetaining, BOOL fAsync);
HRESULT Commit(BOOL fRetaining, DWORD grfCommitType, DWORD grfReserved);
HRESULT GetTransactionInfo(XACTTRANSINFO *pTransInfo);

The Abort method aborts a series of operations performed on a data source. For logging purposes, the pboidReason parameter specifies a reason that the transaction is being aborted. This parameter is a BOID type, which is defined as follows:

typedef struct BOID {
     BYTE rgb[16];
};

This simply defines a 16-byte buffer. If no reason is required, the BOID_NULL constant is used. The fRetaining parameter defines the retain behavior of the abort operation, which is sometimes referred to as Autocommit mode. If true, when the abort operation is carried out and subsequent transactions are created, the abort is performed in a separate (server-side) process thread. If false, a new thread is not created to carry out the abort. The fAsync flag determines whether the operation is carried out in the background. If true, the abort command returns immediately, and the abort processing happens in the background.

The Commit method commits a series of operations performed on a data source. The fRetaining parameter functions in the same way as it does in the Abort method, controlling the Autocommit mode. The grfCommitType parameter specifies the type of commit to be performed. Table 20.6 defines and describes the various types of commit parameters. The grfReserved parameter is reserved for future use and should always be 0.

The last method of the ITransation interface, GetTransactionInfo, retrieves information about a transaction. It returns a single parameter, pTransInfo, which is a pointer to an XACTTRANSINFO structure. This structure holds information about the transaction and is defined as follows:

typedef struct tagXACTTRANSINFO {
     XACTUOW   uow;
     ISOLEVEL  isoLevel;
     ULONG     isoFlags;
     DWORD     grfTCSupported;
     DWORD     grfRMSupported;
     DWORD     grfTCSupportedRetaining;
     DWORD     grfRMSupportedRetaining;
} XACTRANSINFO;

The uow field defines the level at which the transaction works, typically row. The XACTUOW type is defined as the BOID type that was defined earlier. The isoLevel field specifies the isolation level (discussed in more detail shortly) of this transaction. The isoFlags field is reserved for future use and will always be 0. The grfTCSupported field specifies special transactional-level flags. The grfRMSupported, grfTCSupportedRetaining, and grfRMSupportedRetaining are reserved for future use and will currently return 0. Unless you need to determine the isolation level of a previously started transaction, the GetTransactionInfo method currently does not return much more useful information.

The ITransactionLocal Interface

Just as you can use the ITransaction interface to end a transaction by committing or aborting it, you can use the ITransactionLocal interface of the Session object to begin a transaction. The ITransactionLocal interface is optional for the Session object and is supported only if the data provider supports transactions. The ITransactionLocal interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides two additional methods: GetOptionsObject and StartTransaction. These methods are defined as follows:

HRESULT GetOptionsObject(ITransactionOptions **pOptionsInt);
HRESULT StartTransaction(ISOLEVEL isoLevel, ULONG isoFlagsResv,
                         ITransactionOptions *pIOptions,
                         ULONG *lNewTransLevel);

The StartTransaction method begins a set of operations that will be enveloped by a transaction. The isoLevel parameter specifies the isolation level of the transaction. The lisoFlagResv parameter is reserved for future isolation level flags, but for now it should be 0. The pIOptions parameter specifies a pointer to the ITransactionsOption interface. If options aren't required, you can pass a NULL value. Next I will discuss how to obtain and use the ITransactionsOption interface. The lNewTransLevel parameter returns the new transaction level.

The GetOptionsObject obtains access to the ITransactionOptions interface. A pointer to this interface is returned in the pOptionsInt parameter. What options, then, can be specified for a transaction?

The ITransactionOptions Interface

As I stated earlier, the ITransactionOptions interface specifies transaction-level options. This interface is accessed through the GetOptionsObject method. The ITransactionOptions interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. Also, the interface provides two additional methods: SetOptions and GetOptions. These methods are defined as follows:

HRESULT SetOptions(XACTOPT *pOptions);
HRESULT GetOptions(XACTOPT *pOptions);

The names tell us that these methods set and retrieve option values. Both methods take a single parameter, a pointer to an XACTOPT structure, which defines the transaction options. This structure is defined as follows:

typedef struct tagXACTOPT {
     ULONG ulTImeout;
     unsigned char szDescription[MAX_TRANS_DESC];
} XACTOPT;

The ulTimeout field defines the number of milliseconds that the transaction can remain open before it's automatically aborted. If this value is 0, there will be no time limit. The szDescription field defines the transaction's description.

The ITransactionObject Interface

The ITransactionObject interface obtains access to a Transaction object by specifying its level. This interface is especially useful when using nested transactions (which are discussed shortly). This interface is provided through the Session object and is available only if transactions are supported. The ITransactionObject interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface provides one more method: GetTransactionObject. This method is defined as follows:

HRESULT GetTransactionObject(ULONG lTransLevel, ITransaction **ppITrans);

The lTransLevel specifies the transaction level for which you are searching. The pITrans parameter returns the level's corresponding Transaction object.

The ITransactionJoin Interface

The final transaction interface discussed today is the ITransactionJoin interface, which supports distributed transactions. This interface is provided through the Session object and is available only if transactions are supported. The ITransactionJoin interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface provides two additional methods: GetOptionsObject and JoinTransaction. These methods are defined as follows:

HRESULT GetOptionsObject(ITransactionOptions **ppITransOptions);
HRESULT JoinTransaction(IUnknown *punkDistTrans, ISOLEVEL isoLevel,
                        ULONG isoFlags, ITransactionOptions
                            *pITransOptions);

The GetOptionsObject retrieves distributed transaction options. The JoinTransaction method joins a distributed transaction, a transaction in which multiple clients can take part. Currently, the OLE DB ODBC data provider doesn't support distributed transactions.

Creating Transactions

To create a transaction that encapsulates a series of operations, you must first obtain access to the ITransactionLocal interface and then use the StartTransaction method to open the transaction. The transaction remains opened until it's committed or aborted. Listing 20.3 demonstrates how to use the StartTransaction method to open a database transaction. Note that for brevity, the code in Listing 20.3 doesn't check the return values. In your production code, be sure you test for the return values.

Listing 20.3  How to Start a Transaction

 1:    // Create a Session object...
 2:    pIDBInitialize->QueryInterface(IID_IDBCreateSession,
 3:                                   (void **) &pCreateSession);
 4:
 5:    // Create a Command object...
 6:    pCreateSession->CreateSession(NULL, IID_IDBCreateCommand,
 7:                                  (IUnknown **) &pCreateCommand);
 8:
 9:    // Access the ICommandText interface
10:    pCreateCommand->CreateCommand(NULL, IID_ICommandText,
11:                                  (IUnknown **) &pCommandText);
12:
13:    // Specify the command text
14:    pCommandText->SetCommandText(DBGUID_DBSQL, wCmdString);
15:
16:    // Access the Transaction Interface
17:    pCreateCommand->QueryInterface(IID_ITransactionLocal,
18:                                   (void **) &pTransLocal);
19:
20:    // Start the Transaction
21:    pTransLocal->StartTransaction(ISOLATIONLEVEL_SERIALIZABLE, 0,
22:                                  NULL, &lTransLevel);
23:    cout << "Transaction Level = " << lTransLevel << "\n";
24:
25:    // Execute the command
26:    pCommandText->Execute(NULL, IID_IRowset, NULL, &cNumRows,
27:                          (IUnknown **) &pRowset);
28:    // Remember to release the interface pointers.

Committing and Aborting Transactions

When the StartTransaction method is called, all database operations are saved until they are committed or aborted. When a transaction is committed, all the operations that have been saved are actually carried out on the data source. As you saw earlier, the commit operation enables you to specify whether it is carried out asynchronously, semi-synchronously, or synchronously and whether Autocommit mode is used. The state of any open row sets depends on the DBPROP_COMMITPRESERVE property (a Rowset object group property). If DBPROP_COMMITPRESERVE is true, the row set remains valid and is automatically synchronized when the transaction is committed or aborted; otherwise, the row set becomes invalid. This property is set on the basis of row set by row set. The Commit method is provided through the ITransaction interface, so you must access this interface to access the Commit method. The Commit method will commit the transaction at the current level. Listing 20.4 demonstrates how to commit the transaction started in Listing 20.3. Note that you should check the return value of QueryInterface in your production code.

Listing 20.4  How to Commit the Current Transaction

 1:    // Commit The Transaction
 2:    pCreateCommand->QueryInterface(IID_ITransaction,
                                          (void **) &pTrans);
 3:    pTrans->Commit(FALSE,XATTC_SYNC,0);

Aborting a transaction is just as easy. Listing 20.5 demonstrates how to use the Abort method to abort a transaction at the current level. Again, note that you should check the return value of QueryInterface in your production code.

Listing 20.5  How to Abort the Current Transaction

 1:    // Abort The Transaction
 2:    pCreateCommand->QueryInterface(IID_ITransactionLocal,
                                          (void **) &pTrans);
 3:    pTrans->Abort(NULL, FALSE, TRUE);

Nesting Transactions

If the data provider supports it, transactions can be nested. A nested transaction is created by starting a transaction while another transaction is already open. The inner transaction must be released before outer transactions can be committed or aborted.

Isolation Levels and Locking

OLE DB supports four levels of transaction isolation, which ensures coherent access to a data source. The transaction levels are Read Uncommitted, Read Committed, Repeatable Read, and Serializable.

With Read Committed isolation, any row sets that are created will view only rows that have been changed after those changes have been committed. Operations in this type of isolation level will see any rows that have been added or deleted. With Repeatable Read isolation, any changes to rows already read will not be seen, and any operation that deletes a row will not be seen. Any new rows that have been added will be visible. With Read Uncommitted, no attempt is made to isolate any types of changes. Rows that are retrieved will reflect the current database state. Finally, with Serializable isolation, the rows retrieved are guaranteed to be in the consistent state, reflecting only the changes of the current transaction.

Provider-specific, user-defined locking mechanisms implement these isolation levels. Table 20.7 defines the constants related to each isolation level. The isolation level is determined when the StartTransaction method starts a transaction.

The Index Object

The final OLE DB object today is the Index object. The Index object can directly access a data source index through a row set mechanism. Index objects access data source tables in a sorted manner and search for records, based on a key value. The TIndex CoType is defined as follows:

TIndex {
     interface IAccessor;           // Required Interface
     interface IColumnsInfo;        // Required Interface
     interface IConvertType;        // Required Interface
     interface IRowset;             // Required Interface
     interface IRowsetIndex;        // Required Interface
     interface IRowsetInfo;         // Required Interface
     interface IRowsetChange;
     interface ISupportErrorInfo;
};

You have already seen all these interfaces-except for the IRowsetIndex interface-in the context of the Rowset object. The next section describes the IRowsetIndex interface and explains how to implement the Index object.

The IRowsetIndex Interface

The IRowsetIndex interface is required by the Index object. It retrieves index information, searches for specific key values, and limits the set of visible rows to a range of key values. The IRowsetIndex interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides three more methods: GetIndexInfo, Seek, and SetRange. These methods are defined as follows:

HRESULT GetIndexInfo(ULONG *plNumKeyCols, DBINDEXCOLUMNDESC
                     **pprdIdxColDesc, ULONG *plNumIdxProps,
                     DBPROPSET **prgIdxProps);
HRESULT Seek(HACCESSOR hAccessor, ULONG lNumKeys, void *pSeekData,
             DBSEEK dwSeekOpt);
HRESULT SetRange(HACCESSOR hAccessor, ULONG lNumStartKeyCols,
                 void *pStartKeyData, ULONG lNumEndKeyCols,
                 void *pEndKeyData, DBRANGE dwRangeOpt);

The GetIndexInfo method retrieves information about the index. The plNumKeyCols parameter returns the number of key columns in this index. The prdIdxColDesc parameter returns an array that describes each column. The plNumIdxProps parameter returns the number of elements in the prgIdxProps array. The prgIdxProps parameter returns an array that holds a collection of index properties.

The Seek method searches for a specific record in the index row set. The hAccessor parameter holds an Accessor to a set of bindings that describe the key structure. The lNumKeys parameter specifies the number of keys in the search criteria. The pSeekData parameter specifies the key set data for which you are searching. The dwSeekOpt parameter specifies the seek options. Seek options are outlined in Table 20.8.

The SetRange method restricts the rows visible in the index row set to a range of key values. The hAccessor parameter holds an Accessor to a set of bindings that describe the key structure. The lNumStartKeyCols defines the number of key columns in the start range key. The pStartKeyData parameter specifies the start key data. The lNumEndKeyCols defines the number of key columns in the end range key. The pEndKeyData parameter specifies the end key data. The dwRangeOpt parameter specifies how the start and end keys will be used to limit the rows that will be visible. Table 20.9 defines the range options. These options can be combined with a logical OR operation.

Using the Index Object

Here's how to open and use an Index object:

1. Open an index row set, using the IOpenRowset->OpenRowset method of the Session object.
2. Retrieve the columns information, using the IColumnsInfo->GetColumnInfo method of the Index object.
3. Create a binding structure that includes the rows that are part of the index. The dwFlags field of the column information structure will contain the flag value DBCOLUMNFLAGS_ISBOOKMARK if the column is a key field.
4. Use the binding structure to create an Accessor, using the IAccessor->CreateAccessor method of the Index object.
5. Use the IRowsetIndex interface methods to manipulate the index.

Summary

Day 20 focuses on three main topics: OLE DB properties and groups, the Transaction object, and the Index object. Today you learned how to retrieve and set property values and how to use the different types associated with properties. You reviewed the set of properties that OLE DB objects support and how to use these properties to determine whether a data source supports use of the SQL command language. You learned how to use the Transaction object to encapsulate your data source operations and to commit and abort transactions. Other transaction-related topics discussed today are nesting transaction, transaction isolation levels, and how to control locking. Today's final topic is the Index object, its associated interfaces, and how to use the Index object to search for specific rows in a row set that contain specific key values. Tomorrow's theme is how to integrate error handling into your OLE DB applications.

Q&A

This section answers some common questions related to today's topics.

Q	How can you use transactions to improve application performance?
A	When a transaction is opened, all operations that are performed are buffered until they are committed or aborted. In a client/server environment, using transactions can cut down on the network traffic. Only transactions that actually commit are transmitted to the server data source. This performance improvement doesn't come without a cost, however. In this case, the cost can be higher memory requirements (physical or disk space) on the client side. In a multiuser environment, though, this tradeoff can be worthwhile because it can yield significant performance improvements under the right conditions.
Q	How do isolation levels relate to specific locking schemes?
A	OLE DB doesn't specify the locking mechanisms used by a provider. The OLE DB specification leaves it to the data provider to implement its own appropriate locking methods that support the defined isolation levels. When using OLE DB, you should focus on isolation levels rather than on specific locking mechanisms, and you must use transactions to invoke these locking methodologies.
Q	How can you determine whether a data provider supports its own set of properties?
A	You can use the GetPropertyInfo method of the IDBProperties interface to return information about all properties supported by the data provider. This method returns a DBPROPINFOSET structure that contains information about each property supported by a data provider. You must navigate through this structure to determine the provider-specific properties it supports.

Workshop

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

Quiz

1. List the major property groups.
2. What structure returns the collection of property values?
3. Name the property that determines the level of SQL command support provided by an OLE DB data provider?
4. What method opens a transaction?
5. What isolation level maintains the most consistent access to a row set in a multiuser environment?
6. What property ensures that a row set is retained in a valid state after a transaction is closed?
7. What Index object method searches for a specific key value in an index?

Exercises

1. Review the properties supported by the OLE DB objects.
2. Open the OLE DB ODBC provider Help file and review the provider-specific properties it supports.

© Copyright, Sams Publishing. All rights reserved.