MDAC 2.5 SDK - OLE DB Providers
OLE DB Provider for SQL Server


 

Creating an OLE DB Application

In its simplest form, creating an OLE DB application involves three steps:

  1. Establishing a connection to a data source.

  2. Executing a command.

  3. Processing the results.

Establishing a Connection to a Data Source

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.

Executing a Command

After the connection to a data source is established, the consumer 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 a request for the ICommandText interface. The ICommandText::SetCommandText method is used to specify the command that is to be executed. Finally, the ICommand::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.

Processing Results

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 the 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 by 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.

Compiling OLE DB Applications

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 an application uses TCHAR variables, the application code must include #define UNICODE, which 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 Components (MDAC) SDK. For installation information, see www.microsoft.com/data. If you have downloaded a version of the MDAC SDK whose dates are later than the dates for SQL Server 7.0, place the MDAC (msdasdk) directories before the SQL Server 7.0 directories, as shown in the following 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

About OLE DB Properties

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 groups.

Setting property values involves the following steps:

  1. Determining the properties for which to set values.

  2. Determining the property sets that contain the identified properties.

  3. Allocating an array of DBPROPSET structures, one for each identified property set.

  4. Allocating an array of DBPROP structures for each property set. The number of elements in each array is the number of properties, identified in step 1, that belong to that property set.

  5. For each property, filling in the DBPROP structure.

  6. For each property set, filling in information (property set GUID, count of number of elements, and a pointer to the corresponding DBPROP array) in the DBPROPSET structure.

  7. Calling a method to set properties and passing it the count and the array of DBPROPSET structures.