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

Day 18
      Querying a Data Source with OLE DB

• Sessions
  • The IGetDataSource Interface
  • The IOpenRowset Interface
  • The ISessionProperties Interface
  • The IDBCreateCommand Interface
  • The IDBSchemaRowset Interface
  • The ITableDefinition Interface
  • The IIndexDefinition Interface
  • The ITransaction, ITransactionJoin, ITransactionLocal, and ITransactionObject Interfaces
• Commands
  • The IAccessor Interface
  • The IColumnsInfo Interface
  • The ICommand Interface
  • The ICommandProperties Interface
  • The ICommandText Interface
  • The IConvertType Interface
  • The IColumnsRowset Interface
  • The ICommandPrepare Interface
  • The ICommandWithParameters Interface
• A SQL Compendium
• SQL Queries-Data Manipulation Language
  • SELECT
  • Clauses
  • INSERT INTO
  • UPDATE
  • DELETE
• SQL-Data Definition Language
  • CREATE
  • ALTER
  • DROP
• Creating and Executing Commands
  • Creating and Executing a Command
• Accessors
  • Elements of a Parameter Accessor
• Command Parameters
• Multiple Resultsets
• Summary
• Q&A
• Workshop
  • Quiz Questions
  • Exercises

Day 18 continues the examination of OLE DB objects, specifically the Session and Command objects and the interfaces they provide. You learn how to create a Session object by using the IDBCreateSession interface of the DataSource object and how to create a Command object using the IDBCreateCommand interface of the Session object. Although OLE DB data providers don't have to support Command objects, the OLE DB ODBC data provider does support commands on ODBC data sources. The section on Command objects includes a concise summary of Structured Query Language (SQL). Today's examples focus on using the OLE DB ODBC data provider to access a SQL Server data source.

Today you will

• Use Session objects to define a transaction context, create commands, and directly access row sets.
• Work with interfaces supported by the Session object.
• Explore Command objects and how to use them to perform commands supported by the data provider.
• Work with interfaces supported by the Command object.
• Discover the SQL query/command language.
• Understand the differences between SQL data manipulation and data definition commands.
• Use Accessors.
• Use Multiple resultsets

Sessions

The Session object provides a context for transactions and commands. The IDBCreatession interface of the DataSource object creates a Session object. (The CreateSession method of the IDBCreatession interface actually creates the Session object.) You can use the Session object to

• Create a Command object.
• Access a row set directly.
• Create or modify data source tables and indexes.

The Session object is defined as supporting the following interfaces:

TSession {
     interface IGetDataSource;          // Required Interface
     interface IOpenRowset;             // Required Interface
     interface ISessionProperties;      // Required Interface
     interface IDBCreateCommand;
     interface IDBSchemaRowset;
     interface IIndexDefinition;
     interface ISupportErrorInfo;
     interface ITableDefinition;
     interface ITransaction;
     interface ITransactionJoin;
     interface ITransactionLocal;
     interface ITransactionObject;
};

This is the TSession CoType. A CoType is a way to define a group of COM objects that have similar characteristics. All COM objects that belong to the TSession CoType must expose its mandatory interface. In addition, they can expose the optional interfaces.

The IGetDataSource Interface

The IGetDataSource interface obtains an interface pointer to an interface of the DataSource object that created the Session object. This interface is required by the Session object. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides one additional method, GetDataSource. The GetDataSource method is defined as follows:

HRESULT GetDataSource(REFIID riid, IUnknown **ppDataSource);

The riid parameter specifies the interface of the DataSource object you want to access, and the ppDataSource parameter returns a pointer to the DataSource interface you requested. This method enables your application to access a DataSource object directly from a Session object (that is, without using a variable to point to the desired object).

The IOpenRowset Interface

The IOpenRowset interface accesses a row set from a data source that doesn't support commands. You will learn more about commands and the Command object later today. The IOpenRowset interface is required by the Session object. The IOpenRowset interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides one additional method, OpenRowset. The OpenRowset method is defined as follows:

HRESULT OpenRowset(IUnknown *pAggInterface, DBID *pTableID, DBID *pIndexID, 
                   REFIID riid, ULONG cPropSet, DBPROPSET rdPropSet[], 
                   IUnknown **ppRowset);

The pAggInterface parameter is used if the row set is aggregated (that is, combined with other row sets). If this parameter is NULL, that row set isn't being combined. The pTableID parameter is a DBID structure that holds the name of the data source table you want open. The DBID structure helps explain how the table name is passed. The DBID structure is defined as follows:

typedef struct tagDBID {
  union {                            // A union that holds either:
    GUID guid;                       //   An actual GUID, or
    GUID *guid;                      //   A pointer to a GUID
  } uGuid;
  DBKIND eKind;                      // A flag that determines the
                                     // type of ID being held in this
                                     // structure
  union {                            // A union that holds either:
     LPOLESTR pwszName;              //   A string name
     ULONG ulPropid;                 //   A numeric ID
  } uName;
} DBID;

enum DBKINDENUM {                    // Values for the eKind flag
                                     // The ID is defined by the
                                     // following structure values:
  DBKIND_GUID_NAME,                  // A GUID and a Name
  DBKIND_GUID_PROPID,                // A GUID and a Numeric ID
  DBKIND_NAME,                       // Just the Name
  DBKIND_PGUID_NAME,                 // A pointer to a GUID and a Name
  DBKIND_PGUID_PROPID,               // A pointer to a GUID and a Numeric ID
  DBKIND_PROPID,                     // Just a Numeric ID
  DBKIND_GUID                        // Just a GUID
};

As you can see, the DBID structure is defined to hold various ID combinations. The eKind flag defines the combination used by the DBID structure to define the ID. The DBKINDENUM enumeration structure defines the flags used by the eKind structure variable. For example, the following specification defines a DBID that holds a table name:

DBID     tableID;
LPWSTR   pwszTableName = L"CUSTOMER";

tableID.eKind = DBKIND_NAME;
tableID.uname.pwszName = pwszTableName;

The pIndexID parameter of the OpenRowset method is a DBID structure that represents the name of the associated index to open. The riid parameter is a reference ID of the row set interface to return. The interface must support row sets. Typically, this ID is IID_Rowset. The cPropSet parameter specifies the number of properties specified in the DBPROPSET array. The rgPropSet parameter is an array of DBPROPSET structures, which contain the row set properties. Finally, the ppRowset parameter returns the row set interface pointer. (A detailed discussion of opening and navigating row sets is part of Day 19, "Navigating the Resultset of a Query.")

The ISessionProperties Interface

The ISessionProperties interface gets and sets the properties of a Session object. Properties define values that determine the state of an object. The ISessionProperties interface is required for Session objects. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. Also, the interface provides two additional methods: GetProperties and SetProperties. The GetProperties method retrieves the value of a property, and the SetProperties method sets the value of a property. These methods are defined as follows:

HRESULT GetProperties(ULONG cPropIDSets, const DBPROPIDSET rgPropSets[], 
                      ULONG *pcPropSets, DBPROPSET **prgPropSets);
HRESULT SetProperties(ULONG cPropNum, DBPROPSET rgPropSets[]);

The IDBCreateCommand Interface

The IDBCreateCommand interface creates a new Command object and is optional for Session objects. This interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides one additional method, CreateCommand. The CreateCommand method is defined as follows:

HRESULT CreateCommand(IUnknown pAggInterface, REFIID riid, 
                      IUnknown pCommandInt);

The pAggInterface parameter is used if the command is part of an aggregate. The riid parameter specifies the Command interface to create. The pCommandInt parameter returns the Command interface created.

The IDBSchemaRowset Interface

The IDBSchemaRowset retrieves data source schema information. Schema information describes the data contained in the data source. The IDBSchemaRowset interface is optional for Session objects. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface provides two additional methods: GetRowset and GetSchemas. The GetRowset method returns a schema row set. The GetSchemas method returns an array of available data source schemas. These methods are defined as follows:

HRESULT GetRowset(IUnknown *pAggInterface, REFGUID rguidSchema, 
                  ULONG cRestrictions, const VARIANT rgRectricts[], 
                  REFIID riid, ULONG cPropSets, DBPROPSET rdPropSet, 
                  IUnknown ppRowset);
HRESULT GetSchemas(ULONG *pSchemas, GUID **pgSchemas, ULONG **pRestrictions);

For the GetRowset method, the pAggInterface parameter is used if the command is part of an aggregate. The rguidSchema parameter defines a schema GUID. Table 18.1 defines the schema GUIDs available. Refer to the OLE DB specification documentation for more information regarding the row sets that these schemas return. The cRestrictions parameter defines the number of column restrictions. The rgRestricts array parameter defines the column restrictions. The riid parameter defines the interface ID of the row set interface to return; typically, this value is IID_Rowset. The cPropSet parameter specifies the number of properties specified in the DBPROPSET array. The rgPropSet parameter is an array of DBPROPSET structures, which contain the row set properties. The ppRowset parameter is used to return the row set interface pointer.

For the GetSchemas method, the pSchemas parameter returns the number of schemas supported by the data source. The pgSchemas parameter returns an array of schema GUIDs. The pRestrictions parameter returns an array of schema row set column restrictions.

The ITableDefinition Interface

The ITableDefinition interface creates, deletes, and modifies data source tables. This interface is optional. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides four additional methods: AddColumn, CreateTable, DropColumn, and DropTable. These methods are defined as follows:

HRESULT AddColumn(DBID *pTableID, DBCOLUMNDESC *pColDesc, DBID **ppColId);
HRESULT CreateTable(IUnknown * pUnkOuter,
                         DBID * pTableID,
                         ULONG cColumnDescs,
                         DBCOLUMNDESC rgColumnDescs[],
                         REFIID riid,
                         ULONG cPropertySets,
                         DBPROPSET rgPropertySets[],
                         DBID ** ppTableID,
                         IUnknown ** ppRowset
HRESULT DropColumn(DBID *pTableID, DBID *pColumnID);
HRESULT DropTable(DBID *pTableID);

The DropColumn and DropTable methods should be self-explanatory, with both methods taking the name of a table and column (if applicable) to delete. With the AddColumn method, the pTableID parameter takes the name of the table to which the column will be added. The pColDesc parameter describes the column to add. The pColId parameter returns a pointer to the column that was just created. The CreateTable method pAggInterface parameter is used if the command is part of an aggregate, and pTableID specifies the name of the table to create. The cColDescs and pColDescs parameters define the number and description of the columns to create. The riid parameter specifies the row set interface to return for the table you are creating. The cPropSet parameter specifies the number of properties used in the DBPROPSET array. The rgPropSet parameter is an array of DBPROPSET structures, which contain the table properties. Finally the ppTableID and ppRowset parameters return pointers to the table ID and row set for the newly created table. Listing 18.1 demonstrates how the CreateTable and DropTable methods are used.

Listing 18.1  Using the ITABLEDEFINITION to Create and Drop a Table

 1:   DBID         cTableID;                      // Holds the table name
 2:   DBCOLUMNDESC cColDescs[2];                  // Column definitions
 3:   DBID         *pNewTableID = NULL;           // Interface to newly 
 4:                                               // created table
 5:   IRowset      *pRowset = NULL;               // Rowset interface // pointer
 6: 
 7:   cTableID.eKind = DBKIND_NAME;               // Specify the table  
 8:                                               // name to create
 9:   cTableID.uname.pwszName = L"Table1";
10: 
11:                                               // Define Column 1
12:   cColDescs[0].pwszTypeName = L"DBTYPE_CHAR"; // Specify the type of 
13:                                               // column 1
14:   cColDescs[0].pTypeInfo = NULL;              // No additional type 
15:                                               // information
16:   cColDescs[0].rgPropertySets = NULL;         // No special column 
17:                                               // properties
18:   cColDescs[0].pclsid = IID_NULL;             // If this is an OLE 
19:                                               // type column, this is 
20:                                               // where the OLE type is 
21:                                               // specified
22:   cColDescs[0].cPropertySets = 0;             // Number of properties 
23:                                               // specified
24:   cColDescs[0].ulColumnSize = 255;            // Size of the column,  
25:                                               // in this case 255 
                                                  // characters
26:   cColDescs[0].dbcid.eKind = DBKIND_NAME;     // Specify the field name 
27:   cColDescs[0].dbcid.pwszName = L"Field1";
28:   cColDescs[0].wType = DBTYPE_STR;
29:   cColDescs[0].bPrecision = 0;                // Only used for 
30:   cColDescs[0].bScale = 0;                    // floating-point types
31: 
32:   cColDescs[1].pwszTypeName = L"DBTYPE_I4";   // Define Column 2
33:   cColDescs[1].pTypeInfo = NULL;
34:   cColDescs[1].rgPropertySets = NULL;
35:   cColDescs[1].pclsid = IID_NULL;
36:   cColDescs[1].cPropertySets = 0;
37:   cColDescs[1].ulColumnSize = 0;
38:   cColDescs[1].dbcid.eKind = DBKIND_NAME;
39:   cColDescs[1].dbcid.pwszName = L"Field2";
40:   cColDescs[1].wType = DBTYPE_I4;
41:   cColDescs[1].bPrecision = 0;
42:   cColDescs[1].bScale = 0; 
43: 
44:   // Create the Table
45:   MySession->CreateTable(NULL, &TableID, 2, &ColDescs, IID_IRowset, 0, NULL, 
46:                          &NewtableID, &pRowset);
47: 
48:   // -----------------------------------------------------------------
49:   // Drop the table named Table2
50:   // -----------------------------------------------------------------
51: 
52:   cTableID.eKind = DBKIND_NAME;               // Specify the table name
53:   cTableID.uname.pwszName = L"Table2";
54:   HRESULT DropTable(&cTableID); 

Line 1 in Listing 18.1 defines a variable to hold the table name. Line 2 defines a column description array with two elements, specifying that there will be two columns. See the comments following lines 1-25 to understand what the code is doing. Line 38 makes the CreateTable call to actually create the table. Lines 45-47 drop a (different) table from the database.

The IIndexDefinition Interface

The IIndexDefinition interface enables data source indexes to be created and deleted. It is optional and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface provides two additional methods: CreateIndex and DropIndex, which are defined as follows:

HRESULT CreateIndex(   DBID *             pTableID,
   DBID *                                 pIndexID,
   ULONG                                  cIndexColumnDescs,
   const DBINDEXCOLUMNDESC                rgIndexColumnDescs[],
   ULONG                                  cPropertySets,
   DBPROPSET                              rgPropertySets[],
   DBID **                                ppIndexID
HRESULT DropIndex(DBID *pTableID, DBID *pIndexID);

The CreateIndex, pTableID, and pIndexID parameters define the table and index identifiers. The cIndexCols parameter defines the number of index columns to use when creating the index. The rdIndexColsDescs parameter defines an array of columns to use when creating the index. The cPropSet parameter specifies the number of properties used in the DBPROPSET array. The rgPropSet parameter is an array of DBPROPSET structures, which contain the index properties. The ppIndexID parameter returns a pointer to thenew index. For the DropIndex method, the pTableID and pIndexID parameters define the table and index identifiers of the index to delete. (This book doesn't delve into the DropIndex method. Refer to the discussion of SQL later today for more information about creating and deleting indexes by using the data definition capabilities of SQL.)

The ITransaction, ITransactionJoin, ITransactionLocal, and ITransactionObject Interfaces

Finally, the ITransaction, ITransactionJoin, ITransactionLocal, and ITransactionObject interfaces create transactions. (See Day 20, "Properties, Transactions, and Indexes.")

Commands

Command objects perform commands that the provider supports. Using the OLE DB ODBC provider and a database such as SQL Server, you can use the Command object to execute SQL commands. OLE DB data providers aren't required to support commands.

This section begins with a discussion of the Command object and its associated interfaces and then briefly reviews the SQL command language. After you understand the Command object and SQL, you learn how to utilize these objects when using Visual C++.

The TCommand CoType supports the following interfaces:

TCommand {
     interface IAccessor;               // Required Interface
     interface IColumnsInfo;            // Required Interface
     interface ICommand;                // Required Interface
     interface ICommandProperties;      // Required Interface
     interface ICommandText;            // Required Interface
     interface IConvertType;            // Required Interface
     interface IColumnsRowset;
     interface ICommandPrepare;
     interface ICommandWithParameters;
     interface ISupportErrorInfo;
};  

The ISupportErrorInfo interface was introduced yesterday and is covered in more detail on Day 21, "OLE DB Error Handling."

The IAccessor Interface

Accessors manage the buffer in which retrieved row sets or command parameters are stored. The CreateAccessor method creates new Accessors. An Accessor is identified by its handle (an HACCESSOR type), which is returned in an out parameter of the CreateAccessor method. An Accessor created by a Command object is inherited by the row sets that the Command object subsequently creates. Whenever the consumer finishes using an Accessor, the consumer must call the ReleaseAccessor method to release the memory it holds. This section briefly describes the IAccessor interface; a more detailed discussion of command parameters and Accessors appears at the end of today. (Row set Accessors are covered in more detail tomorrow.)

The IAccessor interface is required by Command objects. This interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides four additional methods: AddRefAccessor, CreateAccessor, GetBindings, and ReleaseAccessor. These methods are defined as follows:

HRESULT AddRefAccessor(
   DBACCESSORFLAGS      dwAccessorFlags,
   ULONG                     cBindings,
   const DBBINDING            rgBindings[],
   ULONG                     cbRowSize,
   HACCESSOR *               phAccessor,
   DBBINDSTATUS            rgStatus[]);
HRESULT GetBindings(HACCESSOR hAccessor, DBACCESSORFLAGS *pdwFlags,
                    ULONG *pNumBindings, DBBINDING *prgBinding);
HRESULT ReleaseAccessor(HACCESSOR hAccessor, ULONG *pRefCount);

Reference counts control how many times an Accessor is currently in use. If an Accessor is being used in a multithreaded environment, each thread should call the AddRefAccessor method. This procedure adds to the reference count of the Accessor. The ReleaseAccessor method frees the memory used by an Accessor. Before the memory is actually freed, the reference count is decremented. If the reference count is 0 (which means that the Accessor isn't being used anywhere else), the memory is released. The CreateAccessor method creates and allocates the memory required by is 0 (which means that the 000 isn't being used anywhere else), the memory is released. The CreateAccessor method creates and allocates the memory required by a new Accessor. The GetBindings method retrieves the data bindings associated withan Accessor. I explain these methods in more detail later today and again tomorrow (Day 19).

The IColumnsInfo Interface

The IColumnsInfo method retrieves schema information for a prepared statement. Prepared statements are commands that are precompiled to execute faster. The data provider interprets a command once, when it is defined. Then when the command is executed later, it can be executed quickly. The IColumnsInfo interface can work with a prepared statement to retrieve information regarding the columns that will be returned in the row set when the command is executed. The IColumnsInfo interface is required by the Command object. The IColumnsInfo interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface also provides two additional methods: GetColumnInfo and MapColumnIDs. These methods are defined as follows:

HRESULT GetColumnInfo(ULONG *pNumColumns, DBCOLUMNINFO **prdColInfo, 
                      OLECHAR **ppBuffer);
HRESULT MapColumnIDs(ULONG cNumColIDs, const DBID rgColIDs, ULONG rgCols);

The GetColumnInfo method retrieves information about the columns returned by a prepared statement. The pNumColumns parameter returns the number of columns created by the prepared statement. The prdColInfo is a DBCOLUMNINFO structure that contains the schema information regarding the columns returned by the prepared statement. The ppBuffer parameter returns a pointer to a block of memory, which is the memory that the GetColumnInfo method used to store strings for the prdColInfo structure. After you review the prdColInfo structure, you must free the memory through the COM task allocator by getting a pointer to IMalloc and calling its Free function or by calling CoTaskMemFree to release this memory.

The MapColumnIDs method takes an array of column IDs rgColIDs and returns another array, rgCols, which contains the ordinal position of each of these columns in the prepared statement. The rgCols array elements match up with the rgColIDs elements. For example, if element 1 of the rgCols array contains any value other than DB_INVALIDCOLUMN, such as the value 5, element 1 in the rgColIDs structure is the fifth column in the row set that the prepared statement will return. A value of DB_INVALIDCOLUMN identifies a column that isn't contained in the prepared statement. The cNumColIDs parameter specifies the number of columns contained in the rgColIDs array.

The ICommand Interface

The ICommand interface executes and manages executing commands. It is required by the Command object and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. This interface also provides three additional methods: Cancel, Execute, and GetDBSession. These methods are defined as follows:

HRESULT Cancel();
HRESULT Execute(IUnknown pAggInterface, REFIID riid, DBPARAMS *pDBParams, 
                LONG *pcNumRowsAffected, IUnknown **ppRowset);
HRESULT GetDBSession(REFID riid, IUnknown **ppSessionInterface); 

The Cancel method aborts command execution. The Execute command actually executes a command. The pAggInterface parameter is used if the row set created by the command is part of an aggregate. The riid parameter specifies the ID of the row set interface to create for the data returned by the command, typically IID_IRowset. The pDBparams method specifies command parameters; if the command doesn't use parameters, this value is NULL. The pcNumRowsAffected parameter returns the number of rows that the command changes, deletes, adds, or returns. The ppRowset command returns a pointer to the row set interface. Finally, the GetDBSesion method returns a pointer to the Session object that creates the current Command object. The riid interface specifies the Session interface to return. The ppSessionInterface parameter returns a pointer to the specified Session interface.

The ICommandProperties Interface

The ICOmmandProperties interface gets and sets the properties for the command. You can use this interface to specify the properties that the returned rowset must satisfy. As stated before, properties define values that determine the state of an object. The ICommandProperties interface is required for Command objects. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides two additional methods: GetProperties and SetProperties. The GetProperties method retrieves the value of a property, and the SetProperties method sets the value of a property. These methods are defined as follows:

HRESULT GetProperties(ULONG cPropIDSets, const DBPROPIDSET rgPropSets[], 
                      ULONG *pcPropSets, DBPROPSET **prgPropSets);
HRESULT SetProperties(ULONG cPropNum, DBPROPSET rgPropSets[]);

The ICommandText Interface

The ICommandText interface sets and retrieves the actual command text, which specifies the data source command to execute. The ICommandText interface is required to be implemented on all Command objects. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release and provides two additional methods: GetCommandText and SetCommandText. These methods are defined as follows:

HRESULT SetCommandText(REFGUID gCmdDialect, LPCOLESTR *pwszCommand);
HRESULT GetCommandText(GUID *pgCmdDialect, LPCOLESTR *pwszCommand);

The SetCommandText method specifies the data source command. The gCmdDialect specifies the command dialect GUID, for the dialect used in the command. Typically, for data sources that support the SQL command syntax, this value is DBGUID_DBSQL. The pwszCommand parameter specifies a string that contains the command. The GetTextCommand method retrieves a command text. The pgCmdDialect parameter returns the command dialect GUID, and the pwszCommand parameter returns the actual command text. Listing 18.2 demonstrates how to create and execute a command. Note the comments in the source code for an explanation of what the code is doing. The code in Listing 18.2 does no error checking, nor does it release the allocated interfaces. This is for code brevity. Of course, you should check return codes and release interfaces that you allocate in your code.

Listing 18.2  How to Create and Execute a Command by Using the COMMAND Object

 1:  IDBCreateCommand   *pCreateCommand;
 2:  ICommandText       *pCommandText;
 3:  IRowset            *pRowset;
 4:  pwszCommandStr = OLESTR("SELECT * FROM TABLE1");
 5:  LONG                cNumRows;
 6:
 7:  // Use a Session object to create a CreateCommand interface
 8:  Session->CreateSession(NULL, IID_IDBCreateComand, 
 9:                         (IUnknown **) &pCreateCommand);
10:
11:  // Create a CommandText interface
12:  pCreateCommand->CreateCommand(NULL, IID_ICommandText, 
13:                                (IUnknown **) &pCommandText);
14:
15:  // Free the CreateCommand interface pointer
16:  pCreateCommand->Release();
17:
18:  // Specify the command, using the SetCommandText method
19:  pCommandText->SetCommandText(DBGUID_DBSQL, pwszCommandStr);
20:
21:  // Execute the command
22:  pCommandText->Execute(NULL, IID_Rowset, NULL, &cNumRows, 
23:                        (IUnknown **) &pRowset); 

The IConvertType Interface

The IConvertType interface determines whether a command can convert data types. The IConvertType interface is required by the Command object and defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface defines one additional method, CanConvert, which is defined as follows:

HRESULT CanConvert(DBTYPE wTypeFrom, DBTYPE wTypeTo, 
                   DBCONVERTFLAGS wConvertFlag);

The wTypeFrom parameter specifies the type you want to convert from, and the wTypeTo parameter specifies the type you want to convert to. The wConvertFlag parameter specifies how this conversion is to be performed by using the constants DBCONVERTFLAGS COLUMN, DBCONVERTFLAGS ISFIXEDLENGTH, DBCONVERTFLAGS ISLONG, DBCONVERTFLAGS PARAMETER, and DBCONVERTFLAGS FROMVARIANT. If the method returns S_OK, the type conversion can be performed; otherwise, it cannot. Listing 18.3 demonstrates how to check whether a type conversion from an integer to a string can be performed on a parameter.

Listing 18.3  Checking Whether a Type Conversion Is Possible

 1:  if(SUCCEEDED(pCommand->CanConvert(DBTYPE_I4, DBTYPE_STR, 
     DBCONVERTFLAGS_PARAMETER))
 2:  {
 3:    cout << "Conversion can be performed!!!\n";
 4:  } 
 5:  else
 6:  {
 7:    cout << "Conversion can NOT be performed!!!!\n");
 8:  };

The IColumnsRowset Interface

The IColumnsRowset interface is similar to the IColumnsInfo interface in that IColumnsRowset also returns a row set containing schema information about the columns created by a command. This interface is optional and is provided only by more advanced data providers. It defines the standard IUnknown interface methods QueryInterface, AddRef, and Release, as well as two additional methods: GetAvailableColumns and GetColumnsRowset. These methods are defined as follows:

HRESULT GetAvailableColumns(ULONG *pNumOptCols, DBID **ppOptCols);
HRESULT GetColumnsRowset(IUnknown *pAggInterface, ULONG cNumOptCols, 
                         const DBID rgOptCols[], REFIID riid, 
                         ULONG cNumPropSets, DBPROPSET rgPropSets[], 
                         IUnknown **pColumnRowset);

The GetAvailableColumns method determines the optional columns that a command could return. The GetColumnsRowset returns a row set containing information about the columns returned by a command.

The ICommandPrepare Interface

The ICommandPrepare interface converts a command to a prepared command. A prepared command has been precompiled so that it can execute faster after it is run. If you expect a command to be executed repeatedly, it is useful to transform it into a prepared command. This technique improves application performance. The ICommandPrepare interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. It defines two additional methods: Prepare and Unprepare, which are defined as follows:

HRESULT Prepare(ULONG cNumUsages);
HRESULT Unprepare();

The Prepare method takes a single parameter, cNumUsages, which the command optimizer can use to determine the appropriate way to save the command interpretation. If this value is 0, the default optimization method is used. The higher the value, in theory, the more the data provider will try to optimize the command. The Unprepare command deletes the precompiled command.

The ICommandWithParameters Interface

The last interface provided by the Command object is the optional ICommandWithParameters. The ICommandWitParameters interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface defines three additional methods: GetParameterInfo, MapParameterNames, and SetParameterInfo, which are defined as follows:

HRESULT GetParameterInfo(ULONG *pNumParams, DBPARAMINFO prgParamInfo, 
                         OLECHAR **ppBuffer);
HRESULT MapParameterNames(ULONG cNumParams, const OLECHAR *rgParamNames[], 
                          LONG rgParamOrds[]);
HRESULT SetParameterInfo(ULONG cNumParams, const ULONG rgParamOrds[], 
                         const DBPARAMBINDINFO rgParamBindInfo[]);

The GetParameterInfo method retrieves parameter information. The MapParameterNames method maps parameter names to their ordinal positions. The SetParameterInfo method specifies command parameter values. At the end of today's lesson, I'll show you how to create commands that use parameters, and I'll explain the appropriate methods in more detail.

The next section is a brief survey of SQL. Using SQL is the easiest way to retrieve row sets and manage the information contained in the data source. You should use SQL with any data source that supports it.

A SQL Compendium

This section provides you with a concise summary. This summary of SQL should be helpful for you when learning and working with OLE DB Command objects.

As you know, SQL is the standard language for manipulating relational database information. The American National Standards Institute (ANSI) is responsible for defining computer industry standards. The ANSI SQL-89 standard was established in 1989. Most relational databases comply to the 1989 standard (although each vendor's implementation of SQL is unique in some respects). In 1992 the ANSI SQL-92 standard was introduced. Level I is the highest of the three levels of compliance to the ANSI standard.

You learned earlier that SQL provides two subsets of commands. One set of commands is used for data manipulation, and the other subset is used for data definition. Data manipulation language enables you to select and modify database data. Data definition language enables you to change the database schema (tables, fields, and indexes).

SQL Queries-Data Manipulation Language

This overview of the SQL command language begins with the data manipulation command subset. The data manipulation commands are the most frequently used SQL commands. The intent of this brief discussion is to give you enough information to write most of the SQL commands your applications will require.

The following discussion assumes that you have a database named Customer, which contains Tables 18.2-18.4:

SELECT

The SELECT statement retrieves subsets of records in the database. SELECT statements read data from the database; they don't change any data. The results of SELECT statements are row sets; I'll discuss this relationship and how to access and navigate row set data in more detail tomorrow.

The most basic SELECT statement has the following form:

SELECT fields FROM table

The fields parameter represents the fields of the table you want to access and the table parameter represents the database table from which you want to access data. The fields parameter can be the actual names of each field in your table, separated by commas; if you want all the fields contained in the table, use the asterisk (*) instead. To retrieve only the CustomerID and CompanyName fields from a table named Customer, use the following SELECT statement:

SELECT CustomerID, CompanyName FROM Customer

To retrieve all the fields from the table named Customer, use the following SELECT statement:

SELECT * FROM Customer

Clauses

You can add various clauses to SQL commands to specify subsets of data to operate on, to change the ordering and grouping of the data, and to specify access to external databases. The following paragraphs explain how these clauses apply to the SELECT statement.

WHERE

The WHERE clause of a SELECT statement limits the set of records selected. The SELECT statement controls which fields are retrieved from a table; the WHERE clause filters which data is selected from a table. You can also use the WHERE clause to join two or more tables.

The next example shows how the WHERE clause filters the records from a table. A SELECT statement with a WHERE clause has the following form:

SELECT fields FROM table
WHERE field COMPAREOP value {LOGICALOP field COMPAREOP value}...

The field parameter specifies the name of a field, and the value parameter specifies the value of that field. The COMPAREOP parameter is a SQL comparison operator, and the LOGICALOP parameter is a SQL logical operator. The portion of the WHERE clause contained in the brackets is an optional expression, which can be repeated up to 40 times to create complex SELECT statements.

Table 18.5 summarizes the SQL comparison operators, and Table 18.6 summarizes the SQL logical operators. For the most part, these logical and comparison operators should be familiar to any programmer who has constructed an IF statement.

The following SELECT statement builds on the earlier example. This statement retrieves only the CustomerID and CompanyName fields from a table named Customer where the StateOrProvince is NY.

SELECT CustomerID, CompanyName FROM Customer
WHERE StateOrProvince = 'NY'

As the preceding example shows, you don't have to include a WHERE clause field in the fields that are retrieved. However, a WHERE clause field must be a member of the table or tables from which you are retrieving data. You're probably already familiar with how the =, <=, >=, and <> comparison operators work. The IN, BETWEEN, and LIKE comparison operators are explained next.

The following SELECT statement retrieves all the fields from the Customer table where the StateOrProvince is NY, NJ, or CA.

SELECT * FROM Customer
WHERE StateOrProvince IN ('NY', 'NJ', 'CA')

The IN operator requires a set of values to be defined. If the field's value is in the specified set, the resulting subset of data will include that record.

The BETWEEN operator specifies a range of values that a field's value must be in. You can use the following SELECT statement to retrieve all the fields from the Customer table where the CustomerID is in the range 1 to 1000 inclusive:

SELECT * FROM Customers
WHERE CustomerID BETWEEN 1 AND 1000

You can combine the previous two SELECT statements to retrieve all the fields from the Customer table where the CustomerID is between 1 and 1000 and the StateOrProvince is NY, NJ, or CA. For example, look at the following code:

SELECT * FROM Customers
WHERE StateOrProvince IN ('NY', 'NJ', 'CA')
AND   CustomerID BETWEEN 1 AND 1000

This example shows how you can combine the WHERE statement expressions to create complex filters. WHERE expressions are evaluated from left to right; you may use parentheses to control the evaluation order if necessary.

The LIKE operator can be used in pattern matching. To specify a match to a single character, the ? is used. To specify a match to a number of characters, the * is used. This method is similar to wild card matching with the DOS DIR command. Table 18.7 shows which values a sample LIKE statement will match.

You can use the following SELECT statement to retrieve all the fields from the Customer table where the StateOrProvince begins with an N:

SELECT * FROM Customers
WHERE StateOrProvince LIKE('N*')

You have seen how to use the WHERE clause to filter the data retrieved by the SELECT statement. The WHERE clause can also link two or more tables into a single resulting set of data.

The capability to join multiple tables together is the real power of relational databases. You don't have to worry about the details of how to accomplish this task; SQL handles these details for you. A SELECT statement that joins two or more tables together has the simplest form:

SELECT table1.field1, table2.field2 FROM table1, table2
WHERE  table1.field1 = table2.field2

This example illustrates two important new concepts. First, the FROM clause of the SELECT statement specifies more than one table. Second, the . operator is introduced in naming fields, for example, table1.field1. Field1 is a member of table1. If the fields you are selecting have different names, the . operator isn't required. The . operator makes the name of the field you are selecting unique. Although the . operator isn't required, it does help when you are creating complex queries. You can combine the . operator with the * field specifier to retrieve all the fields from a table. The statement table1.* would retrieve all the fields from table1.

The following SELECT statement retrieves all the customer information, along with an order number for each associated order that the customer has placed from the sample database specified earlier:

SELECT Orders.OrderID, Customer.*
WHERE  Orders.CustomerID = Customer.CustomerID

You don't have to include the Orders.CustomerID field in the set of fields that you are retrieving. On the other hand, you must use the . operator; without it SQL wouldn't know whether you were talking about the CustomerID field in the Orders table or the CustomerID field in the Customer table.

The capability of the WHERE clause to filter selected data can be combined with the capability to join two or more tables. For example, you can extend the preceding SELECT statement to return only the records where the OrderId is between 1 and 2000:

SELECT Orders.OrderID, Customers.* FROM Customers, Orders
WHERE  Orders.CustomerID = Customers.CustomerID
AND    Order.OrderID BETWEEN 1 AND 2000

Earlier you saw how the IN comparison operator specifies a set of data for a field value. You can also create this subset of data for the IN operator by using another query. A subquery creates a set of data that a WHERE clause can use to match a field value. For example, the following SELECT statement selects all the Customer fields that have an order Promised-byDate greater than 05/25/97:

SELECT Customers.* FROM Customers
WHERE CustomerID IN
        (SELECT Orders.CustomerID FROM Orders
         WHERE  Orders.Promised-byDate > #05/25/97#)

This example performs two SELECT statements. One SELECT statement, the subquery, creates the set of CustomerIDs from the Orders table that has a Promised-byDate greater than 05/25/97. The other SELECT statement uses the results for the first SELECT statement with the IN logical operator to filter the Customer records.

The general format for subqueries is

[ic:syntax]expression [NOT] IN (subquery)
comparison [ANY | ALL | SOME] (subquery)
[NOT] EXISTS (subquery)

You are already familiar with the IN operator. Similarly, you can use the ANY, ALL, or SOME operators to match any, all, or just some of the fields in subquery. The EXISTS operator checks to see whether subquery returns any records.

Aggregate Functions

Aggregate functions enable SELECT statements to return a result that applies to a group of records. Table 18.5 summarizes the aggregate functions available in SQL.

The following SELECT statement determines the total amount of all the orders in the Order Detail table:

SELECT SUM([Order Detail].LineTotal) FROM [Order Detail]

GROUP BY

The GROUP BY clause combines records with identical field values into a single record. The GROUP BY clause is useful with SQL aggregate functions. For example, you can retrieve the total amount of all orders for each CustomerID with the following SELECT statement:

SELECT [Order Detail].CustomerID, SUM([Order Detail].LineTotal)
FROM   [Order Detail]

This SELECT statement will work, but it will return duplicate records-one for each order a customer has placed. The GROUP BY clause eliminates these duplicate records. To use the GROUP BY clause, you would rewrite this SELECT statement as

SELECT   [Order Detail].CustomerID, SUM([Order Detail].LineTotal)
FROM     [Order Detail]
GROUP BY [Order Detail].CustomerID

The rewritten statement will return a single record for each CustomerID. Each record will contain the CustomerID and total of all orders in the Order Detail table for that CustomerID.

Aliasing Field Names

When data is selected from a table, the name of the field in the resulting row set is the same as the name of the field in the table. You can change the name of the field in the resulting row set by using the technique called field aliasing. For example, you can retrieve all the CustomerIDs from the Customer table, calling the CustomerID field CustomerNum in the resulting row set, with the following SELECT statement:

SELECT CustomerID AS CustomerNum FROM Customers

HAVING

The HAVING clause is used with the GROUP BY clause. The HAVING clause filters the grouped data resulting from the GROUP BY clause in the same way that the WHERE clause filters the data of the SELECT statement. HAVING and WHERE expressions are constructed in the same way, and both are limited to 40 expressions. For example, you can retrieve the total amount of all orders for CustomerIDs whose totals are greater than $1,000, with the following SELECT statement:

SELECT   [Order Detail].CustomerID,
         SUM([Order Detail].LineTotal) AS TotalAmt
FROM     [Order Detail]
GROUP BY [Order Detail].CustomerID
HAVING   TotalAmt > 1000

ORDER BY

The ORDER BY clause sorts the SELECT statement resultant set of records. You may specify multiple sort keys and sort records in ascending or descending order. For example, you can retrieve all the records in the Customer table sorted by CompanyName in ascending order with the following SELECT statement:

SELECT * FROM Customers
ORDER BY CompanyName ASC

The following SQL statement performs the same selection, sorted in descending order:

SELECT * FROM Customers
ORDER BY CompanyName DESC

To retrieve all the records in the Customer table sorted by StateOrProvince in ascending order, and then CompanyName in ascending order, use the following:

SELECT * FROM Customers
ORDER BY StateOrProvince, CompanyName ASC

If the ordering directive (ASC or DESC) is omitted, the records will be sorted in ascending order by default.

DISTINCT and DISTINCTROW

The DISTINCT clause removes duplicate records from the resulting data set. The following SELECT statement retrieves the unique customer contact last names from the Customers table:

SELECT DISTINCT ContactLastName FROM Customers

If more than one customer contact has the last name Jones, the resulting subset of data will include only one record.

The DISTINCTROW clause selects data that is distinct in any of the fields. For example, you can retrieve all the nonduplicate records in the Customers table with the following SELECT statement:

SELECT DISTINCTROW * FROM Customers

TOP

The TOP clause is used with the ORDER BY clause. With the TOP clause, you can limit the number of records returned to the TOP n number of records, where n is specified in the SELECT statement. For example, you can retrieve the top 50 total amount of all orders for each CustomerID with the following SELECT statement:

SELECT   TOP 50 [Order Detail].CustomerID,
                SUM([Order Detail].LineTotal) AS TotalAmt
FROM     [Order Detail]
GROUP BY [Order Detail].CustomerID
ORDER BY TotalAmt

The TOP clause can also specify a percentage. The following query returns the top 10% of total amounts:

SELECT   TOP 10 PERCENT [Order Detail].CustomerID,
                        SUM([Order Detail].LineTotal) AS TotalAmt
FROM     [Order Detail]
GROUP BY [Order Detail].CustomerID
ORDER BY TotalAmt

JOIN

Creating a join is one of the more powerful functions that a relational database can perform. Table 18.6 summarizes the three types of joins that relational databases can create.

The JOIN clause is used in the following manner:

FROM table1 [LEFT | RIGHT | INNER] JOIN table2
ON table1.field1 = table2.field2

Creating an INNER JOIN is the same as creating a join by using the WHERE clause. LEFT and RIGHT joins produce additional records, as specified in Table 18.6.

One way to retrieve customer information and an order number for each associated order that a customer has placed (a SELECT statement using the WHERE clause) was shown earlier:

SELECT Orders.OrderID, Customers.* FROM Orders, Customers
WHERE  Orders.CustomerID = Customer.CustomerID

You can achieve the same result by using the following SELECT statement with an INNER JOIN:

SELECT Orders.OrderID, Customers.*
FROM   Orders INNER JOIN Customers
ON     Orders.CustomerID = Customer.CustomerID

The preceding information should enable you to use the SQL language to retrieve data from a database. The following sections introduce three SQL commands (INSERT INTO, UPDATE, and DELETE) that enable you to modify records in the database.

INSERT INTO

The INSERT INTO command adds records to a table. You can insert records from the result of another SELECT statement, or you can append single records by specifying their values. If any field is omitted from the target of the table insert, it will become a NULL value. You can use the following SQL statement to append a single record to the Customers table:

INSERT INTO Customers(CustomerID, CompanyName, ContactFirstName,
                      ContactLastName, CompanyOrDepartment,
                      BillingAddress, City, StateOrProvince,
                    PostalCode, Country, ContactTitle, PhoneNumber,
                      Extension, FaxNumber, EmailAddress)
VALUES (100, 'ABC Manufacturing', 'Marie', 'McCartan', 'Executive'
        '123 Main Street', 'Buffalo', 'New York', '14225', 'USA',
        'President', '716-555-1212', '123', '716-555-2121',
        'president@abcmfg.com')

UPDATE

The UPDATE command modifies records in a table, based on specified criteria. The UPDATE command is useful for changing multiple records or fields in multiple tables. For example, here's how to update the sales tax rate to 6% for all orders in the Orders table that are shipped to CA:

UPDATE Orders
SET SalesTaxRate = 0.06
WHERE Orders.ShipState = 'CA'

DELETE

The DELETE command removes records from a table that meet specified criteria. When records are deleted, they cannot be recovered. Here's how to delete all the records from the Customers table that represent customers from San Diego, CA:

DELETE FROM Customers
WHERE  Customers.City = 'San Diego' AND
       Customers.StateOrProvince = 'CA'

SQL-Data Definition Language

In addition to retrieving, adding, and modifying records in database tables, SQL has three commands that can modify the schema of the database:

• CREATE creates tables and indexes.
• DROP deletes tables.
• ALTER modifies table fields and indexes.

CREATE

The CREATE command creates new tables and indexes. The following example creates a new table named Products:

CREATE TABLE Products (ProductID INTEGER, ProductDesc TEXT(50))

The new table contains two fields: the ProductID and the ProductDesc. As you can see, the type of the field is specified after the field name.

The following SQL statement creates a new unique index on the ProductID field for the newly created Products table:

CREATE UNIQUE INDEX ProdIndex ON Products (ProductID)

ALTER

The ALTER command adds or removes fields and indexes to or from a table. The following SQL statement adds the new field SupplierID and ProductColor to the Products table:

ALTER TABLE Products ADD COLUMN SupplierID INTEGER
ALTER TABLE Products ADD COLUMN ProductColor TEXT(30)

Here's how to remove the ProductColor field from the Products table:

ALTER TABLE Products DROP COLUMN ProductColor

Here's how to add a secondary index on the SupplierID field in the Products table:

ALTER TABLE Products ADD CONSTRAINT ProdSuppIdx FOREIGN KEY SupplierID

And here's how to delete the newly created index from the Products table:

ALTER TABLE Products DROP CONSTRAINT ProdSuppIdx

DROP

The DROP command deletes tables. The DROP command removes the table and its associated indexes, unlike the DELETE command that deletes the selected records from the table. Even if all the table records are deleted from a table by using the DELETE command, the empty table and its indexes will still be present. You cannot recover a dropped table. The following SQL statement deletes the Products table that you just created and modified:

DROP TABLE Products

Creating and Executing Commands

Now that you have a better understanding of the Session objects, Command objects, and SQL, you can begin to apply your knowledge by writing some code. Today's business concludes by discussing several issues related to command processing:

• How to create and execute commands
• How to create commands with parameters and how to use parameter Accessors
• How to use command states
• How to create commands that return multiple resultsets

Creating and Executing a Command

The process of creating and executing commands is fairly straightforward (you might want to refer to Listing 18.2 for a review):

1. Create a Command object by using the Session interface CreateCommand method.
2. Obtain access to the ICommandText interface of the Command object.
3. Specify the command string.
4. Use the Execute method of the Command object to actually run the command.
5. Navigate the row set created, if applicable (discussed in more detail tomorrow).
6. Release the interfaces accessed.

Listing 18.4 continues with the simple application from Listing 17.4. Listing 18.4 starts by using the CreateSession method of the IDBCreateSession interface and adds some code to create a Session object. The Session object is then used to create a Command object with the CreateCommand method of the IDBCreateCommand interface. Finally, the command text is specified, and the command is executed. A simple SQL query retrieves the fields CUSTID and CUSTNAME from the CUSTOMERS table in the IDCDatabase and creates a row set that contains the information found in the CUSTOMERS table. (I'll explain the process of navigating and accessing row sets in more detail tomorrow.)

To build the application, run Visual Studio and select the File, New menu choice. Click the Projects tab and specify a Win32 Console Application. Call the application COMMANDTEST. Click OK, specify that you want to create an empty project, and click the Finish button. 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 COMMANDTEST.CPP. Enter the code shown in Listing 18.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 the Project, Settings menu on the Link tab. When you build the project, it should compile and link with no errors or warnings. For code brevity, the code in Listing 18.4 does no error checking, nor does it release the allocated interfaces. Of course, you should check return codes and release interfaces that you allocate in your code.

Listing 18.4  Creating and Executing a Simple Command

  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:    DBPROP            InitProperties[4];
 29:    DBPROPSET         rgInitPropSet[1];
 30:    int               i;
 31:    LONG              cNumRows; 
 32:
 33:    // The Command to execute
 34:    LPCTSTR           wCmdString = 
 35:                        OLESTR("SELECT * FROM Customers");
 36:
 37:    // Initialize the Component Object Module Library
 38:    CoInitialize(NULL);
 39:
 40:    // Obtain Access to the OLE DB - ODBC Provider
 41:    CoCreateInstance(CLSID_MSDASQL, NULL, CLSCTX_INPROC_SERVER, 
 42:                     IID_IDBInitialize, (void **) &pIDBInitialize);
 43:
 44:    // Initialize the property values that are the same for each
 45:    // property. . .
 46:    for (i = 0; i < 4; i++ ) {
 47:        VariantInit(&InitProperties[i].vValue);
 48:        InitProperties[i].dwOptions = DBPROPOPTIONS_REQUIRED;
 49:        InitProperties[i].colid = DB_NULLID;
 50:    }
 51:
 52:    // level of prompting that will be done to complete the connection // process
 53:    InitProperties[0].dwPropertyID = DBPROP_INIT_PROMPT;
 54:    InitProperties[0].vValue.vt = VT_I2;
 55:
 56:    // Specify the User Name
 57:    InitProperties[1].dwPropertyID = DBPROP_AUTH_USERID;
 58:    InitProperties[1].vValue.vt = VT_BSTR;
 59:    // Note: The L cast directive casts the string into a UNICODE // string....
 60:    InitProperties[1].vValue.bstrVal = SysAllocString((LPOLESTR)L"");
 61:
 62:    // Specify the appropriate Password
 63:    InitProperties[2].dwPropertyID = DBPROP_AUTH_PASSWORD;
 64:    InitProperties[2].vValue.vt = VT_BSTR;
 65:    InitProperties[2].vValue.bstrVal = SysAllocString((LPOLESTR)L"");
 66:
 67:    // Specify the Data Source name
 68:    InitProperties[3].dwPropertyID = DBPROP_INIT_DATASOURCE;
 69:    InitProperties[3].vValue.vt = VT_BSTR; 
 70:    InitProperties[3].vValue.bstrVal = 
 71:         SysAllocString((LPOLESTR)L"OrdersDb");
 72:
 73:    rgInitPropSet[0].guidPropertySet = DBPROPSET_DBINIT;
 74:    rgInitPropSet[0].cProperties = 4;
 75:    rgInitPropSet[0].rgProperties = InitProperties;
 76:
 77:    // set initialization properties
 78:    pIDBInitialize->QueryInterface(IID_IDBProperties, 
 79:                      (void **)&pIDBProperties);
 80:    pIDBProperties->SetProperties(1,rgInitPropSet);
 81:    pIDBProperties->Release();
 82:
 83:    // Call the Initialize method to establish the connection to
 84:    // the ODBC data source specified above
 85:    pIDBInitialize->Initialize();
 86:
 87:    // Create a Session object...
 88:    pIDBInitialize->QueryInterface(IID_IDBCreateSession, 
 89:                       (void **) &pCreateSession);
 90:
 91:    // Create a Command object...
 92:    pCreateSession->CreateSession(NULL, IID_IDBCreateCommand, 
 93:                                 (IUnknown **) &pCreateCommand);
 94:
 95:    // Access the ICommandText interface
 96:    pCreateCommand->CreateCommand(NULL, IID_ICommandText, 
 97:                                 (IUnknown **) &pCommandText);
 98:
 99:    // Specify the command text
100:    pCommandText->SetCommandText(DBGUID_DBSQL, wCmdString);
101:
102:    // Execute the command
103:    HRESULT hr;
104:    hr = pCommandText->Execute(NULL, IID_IRowset, NULL, &cNumRows, 
105:                         (IUnknown **) &pRowset); 
106:
107:    // This is where we would navigate the rowset returned...
108:    if (SUCCEEDED(hr))
109:    {
110:      ::MessageBeep(MB_OK);
111:    }
112:
113:    // Free Up Allocated Memory
114:    pRowset->Release();
115:    pCommandText->Release();
116:    pCreateCommand->Release();
117:    pCreateSession->Release();
118:    pIDBInitialize->Uninitialize();
119:    pIDBInitialize->Release();
120:
121:    // Release the Component Object Module Library
122:    CoUninitialize();
123:  };

Read the comments in the Listing 18.4 source code to understand the details of what the code is doing.

Accessors

The last major topic for today is how to create commands that use parameters. Command parameters are just like the parameters of methods or procedures in Visual C++. Command parameters accept values that are specified at runtime. Accessors specify and store parameter values. You can use parameters to input or retrieve values. The DBPARAMBINDINFO structure specifies parameters with the SetParameterInfo method of the ICommandWithParametrs interface.

In SQL commands, parameters are specified by using the ? specifier. For example, the following SQL statement creates a SQL command that inserts records into the Customers table:

INSERT INTO CUSTOMERS (CUSTID, CUSTNAME) VALUES (?, ?)

The values of the fields (shown as the two ?) are specified as parameters when the command is executed. Before you examine code that creates a command that uses parameters, the following section considers the DBPARAMBINDINFO structure.

Elements of a Parameter Accessor

The DBPARAMBINDINFO structure specifies parameter bindings. This structure has the following definition:

typedef struct tagDBPARAMBINDINFO {
LPOLESTR pwszDataSourceType;
    LPOLESTR pwszName;
    ULONG ulParamSize;
    DBPARAMFLAGS dwFlags;
    BYTE bPrecision;
    BYTE bScale;
    } DBPARAMBINDINFO;

The pwszDataSourceType field defines the type of the parameter. The pwszName field specifies the name of the parameter, if applicable; otherwise, it is NULL. The ulParamSize parameter specifies the size of the parameter. The dwFlags parameter specifies the relevant parameter flags. Table 18.10 describes the possible parameter flag values. The bPrecision field specifies the number of digits used by a numeric value, if applicable. Finally, the bScale field specifies the number of digits to the right of the decimal point if the number is positive or the number of digits to the left if the number is negative.

Command Parameters

Listing 18.5 demonstrates how to create and execute the parameterized INSERT command you looked at earlier. Note that the ICommandWithParameters interface specifies the parameter values. Tomorrow, you will learn more about using Accessors to specify field values before executing the parameterized command.

Listing 18.5  How to Create a Parameterized Command

 1:  IDBCreateCommand       *pCreateCommand;
 2:  ICommandText           *pCommandText;
 3:  ICommandWithParameters *pCommandWithParams;
 4:  DBPARAMBINDINFO        ParamBindInfo[2];
 5:  ULONG                  ParamOrdinals[] = {1,2};
 6:  LPCSTR                  pwszCommandStr = 
 7:      OLESTR("INSERT INTO Customers (CustNumber, CustLastName) 
 8:     [ic:ccc] VALUES (?, ?)");
 9: 
10:  // Use a Session object to create a CreateCommand interface
11:  Session->CreateSession(NULL, IID_IDBCreateComand, 
12:                         (IUnknown **) &pCreateCommand);
13: 
14:  // Create a CommandText interface
15:  pCreateCommand->CreateCommand(NULL, IID_ICommandText, 
16:                                (IUnknown **) &pCommandText);
17: 
18:  // Free the CreateCommand interface pointer
19:  pCreateCommand->Release();
20: 
21:  // Specify the command, using the SetCommandText method
22:  pCommandText->SetCommandText(DBGUID_DBSQL, pwszCommandStr);
23: 
24:  // Specify the command parameter information
25:  ParamBindInfo[0].pwszDataSourceType = L"DBTYPE_II4";
26:  ParamBindInfo[0].pwszName = L"CUST_ID";
27:  ParamBindInfo[0].ulParamSize = sizeof(DWORD);
28:  ParamBindInfo[0].dwFlags = DBPARAMFLAGS_ISINPUT;
29:  ParamBindInfo[0].bPrecision = 0;
30:  ParamBindInfo[0].bScale = 0; 
31: 
32:  ParamBindInfo[1].pwszDataSourceType = L"DBTYPE_CHAR";
33:  ParamBindInfo[1].pwszName = L"CUST_NAME";
34:  ParamBindInfo[1].ulParamSize = 255;
35:  ParamBindInfo[1].dwFlags = DBPARAMFLAGS_ISINPUT;
36:  ParamBindInfo[1].bPrecision = 0;
37:  ParamBindInfo[1].bScale = 0;
38: 
39:  pCommandText->QueryInterface(IID_ICommandWithParameters, 
40:                         (void **) &pCommandWithParams);
41:  pCommandWithParams->SetParameterInfo(2, ParamOrdinals,   ParamBindInfo);
42:  
43:  // Release interfaces
44:  pCommandText->Release();
45:  pCommandWithParams->Release();

Multiple Resultsets

If you specify a query that returns multiple resultsets, you need to use the IMultipleResults interface. You can create multiple results by using stored procedures or by specifying multiple commands in the Command object statement.

The IMultipleResults interface defines the standard IUnknown interface methods QueryInterface, AddRef, and Release. The interface defines one additional method, GetResult. This method is defined as follows:

HRESULT GetResult(IUnknown *pAggInterface, LONG lResv, REFID riid, 
                  LONG *pNumRows, IUnknown **ppRowset);

The discussion of OLE DB objects resumes tomorrow with the Rowset object, which accesses the results of queries. You will learn how to navigate these resulting query row sets and how to access the data they contain.

Summary

Day 18 opens with discussions of the Session object and the Command object and their associated interfaces. You learned how to create Session and Command objects and how to create and execute commands. You read a compendium of the SQL command language, which is supported by some OLE DB data providers, as well as its data manipulation and data definition aspects. One of today's applications demonstrates how to connect to a data source, create a session, and specify and execute a simple SQL command. You also learned about parameterized commands and the DBBINDPARAMINFO structure. Day 18 ends with an explanation of how to manage commands that can return multiple row sets.

Q&A

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

Q	How do I determine the types supported by a data source?
A	The IDBSchemaRowset interface determines the types a data source supports. Use the GetSchemas method, using the DBSCHEMA_PROVIDER_TYPES GUID. I will discuss how to navigate and retrieve this row set data tomorrow. Also, open the OLEDB.H file or the OLE DB Specification help file and search for the DBTYPE string. You will find an enumeration object that contains the definitions for each type supported by OLE DB.
Q	What's the best way to manage a data source: with methods such as CreateTable and CreateIndex or with SQL commands?
A	If your data source provider supports SQL language commands, you should definitely use the SQL command language to manage your data source. As you can see from the examples in this lesson, you use far less code to create a table with SQL commands than you use when you create a table with the CreateTable command.
Q	How do I know whether my data source supports the SQL command language?
A	To determine whether a data source supports the SQL command language, you need to use the GetProperties method of the DataSource object, checking for the DBPROP_SQLSUPPORT property. The use of properties is discussed in greater detail during Day 20. If the data source supports this property, it will also support the ANSI standard SQL.

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 Questions

1. What is the role of the Session object?
2. What Session interface method retrieves information about the data source schema?
3. What is the role of the Command object?
4. What Command interface method specifies an actual command?
5. How are parameters specified in commands, and why would you use them?
6. What Command interface method can you use to potentially increase the performance of a query?

Exercises

1. Modify the code in Listing 18.4 to perform a data definition command such as the CREATE TABLE, DROP TABLE, or ALTER TABLE command.
2. Modify the code in Listing 18.5 to perform a parameterized SELECT instead of an INSERT.

© Copyright, Sams Publishing. All rights reserved.