In its simplest form, creating an OLE DB application involves three steps:
To access the Microsoft® OLE DB Provider for SQL Server (SQLOLEDB), the consumer must first create an instance of a data source object by calling the CoCreateInstance method. Each OLE DB provider is identified by a unique class identifier (CLSID). For SQLOLEDB, the class identifier is CLSID_SQLOLEDB.
The data source object exposes the IDBProperties interface, which the consumer uses to provide basic authentication information such as server name, database name, user ID, and password. The IDBProperties::SetProperties method is called to set these properties.
The data source object also exposes the IDBInitialize interface. After the properties are set as described, connection to the data source is established by calling the IDBInitialize::Initialize method.
After the connection to a data source is established, the consumer then calls the IDBCreateSession::CreateSession method to create a session. The session acts as a command, rowset, or transaction factory.
To work directly with individual tables or indexes, the consumer requests the IOpenRowset interface. The IOpenRowset::OpenRowset method opens and returns a rowset that includes all rows from a single base table or index.
To execute a command (such as SELECT * FROM Authors), the consumer requests the IDBCreateCommand interface. The consumer can execute the IDBCreateCommand::CreateCommand method to create a command object and request for the ICommandText interface. The ICommandText::SetCommandText method is used to specify the command that is to be executed. Finally, the Execute command is used to execute the command. The command can be any SQL statement, procedure name, and so on. Not all commands produce a result set (rowset) object. Commands such as SELECT * FROM authors produce a result set.
If a rowset object is produced by either execution of a command or generation of a rowset object directly from the provider (as described earlier), the consumer needs to retrieve and access data in the rowset.
Rowsets are central objects that enable all OLE DB data providers to expose data in tabular form. Conceptually, a rowset is a set of rows in which each row has column data. A rowset object exposes interfaces such as IRowset (contains methods for fetching rows from the rowset sequentially), IAccessor (permits the definition of a group of column bindings describing the way tabular data is bound to consumer program variables), IColumnInfo (provides information about columns in the rowset), and IRowsetInfo (provides information about rowset itself).
A consumer can call the IRowset::GetData method to retrieve a row of data from the rowset into a buffer. Before GetData is called, the consumer describes the buffer using a set of DBBINDING structures. Each binding describes how a column in a rowset is stored in a consumer buffer and contains information such as the ordinal of the column (or parameter) to which the binding applies, what is bound (data value, length of the data, and its binding status), offset in the buffer to each of these parts, and length and type of the data values as they exist in the consumer’s buffer.
When getting the data, the provider uses information in each binding to determine where and how to retrieve data from the consumer’s buffer. When setting data in the consumer’s buffer, the provider uses information in each binding to determine where and how to return data in the consumer’s buffer.
After the DBBINDING structures are specified, an accessor is created (IAccessor::CreateAccessor). An accessor is a collection of bindings. This accessor is used to get or set the data in the consumer’s buffer.
OLE DB applications must include Oledb.h, Sqloledb.h, and Oledberr.h (if using error constants defined in this file). Most applications use wide character strings to make OLE DB function calls. If applications are using TCHAR variables, then the application must include #define UNICODE in the application. It converts the TCHAR variables to wide character strings. OLE DB applications must be linked with the Oledb.lib file. When SQL Server 7.0 is installed using custom setup, the header files are installed in the C:\Mssql7\Devtools\Include directory and the library files are installed in the C:\Mssql7\Devtools\Lib directory. The SQL Server Include and Lib directories should be in the compiler’s INCLUDE and LIB path.
The latest versions of these files can be downloaded with the latest Microsoft Data Access SDK from http://www.microsoft.com/data. If you have downloaded a version of the Microsoft Data Access SDK whose dates are later than the dates for SQL Server 7.0, place the MSDA directories before the SQL Server 7.0 directories; for example:
LIB=c:\msdasdk\oledb\lib;c:\mssql7\DevTools\lib;c:\msdev\lib;
c:\msdev\mfc\lib
INCLUDE=c:\msdasdk\oledb\include;c:\mssql7\DevTools\include;
c:\msdev\include;c:\msdev\mfc\include
Consumers set property values to request specific object behavior. For example, consumers use properties to specify the interfaces to be exposed by a rowset. Consumers get the property values to determine the capabilities of an object such as rowset, session, or a data source object.
Each property has a value, type, description, and read/write attribute, and for rowset properties, an indicator of whether it can be applied on a column-by-column basis.
A property is identified by a GUID and an integer representing the property ID. A property set is a set of all properties that share the same GUID. In addition to the predefined OLE DB property sets, SQLOLEDB implements provider-specific property sets and properties in them. Each property belongs to one or more property groups. A property group is the group of all properties that apply to a particular object. Some property groups include the initialization property group, data source property group, session property group, rowset property group, table property group, column property group, and so on. There are properties in each of these property group.
Setting property values involves: