ADO

ADO (ActiveX Data Objects) is Microsoft-specific technology, work as an interface to to access and manipulate data from a variety of sources through an OLE DB provider. Its primary benefits are ease of use, high speed, low memory overhead and a small disk footprint. ADO is implemented in COM (Component Object Model)-way.

Visual C++ simplifies working with the ADO methods and properties using #import compiler directive. The directive takes the name of a file containing a type library, such as the msado15.dll and generates header files containing typedef declarations, smart pointers for interfaces and enumerated constants.

#import "msado15.dll" \
  no_namespace \
  rename("EOF","adoEOF")
// or
#import "C:\Program Files\Common Files\System\ADO\msado26.tlb" \
  no_namespace \
  rename("EOF","adoEOF")

The first #import generates two files, Msado105.tlh and Msado15.tli off of the typelib contained within Msado15.dll. The structure of the .tlh file can be broken out as follows:

Forward References and Typedefs
Smart Pointer Typedef and Declarations
Type Library Items

The advantage of #import is that it takes care of AddRef, QueryInterface and Release automatically. So should not AddRef() it (or at least no need to) or release it either.

Within _com_ptr_t is a member variable, m_pInterface. As #import is a very thin wrapper, it makes no distinction with m_pInterface after the object is actually released, versus just decrementing its reference count without actually destroying the object. By explicitly calling Release() - without very explicitly calling AddRef() to balance it - #import will gladly try to release an object that doesn't exist, creating interesting side effects and crashing behavior.

It's important to initialize OLE before creating any instances of classes created by #import. For example, the following code is safe, as it declares a #import smart pointer, initializes OLE and then instantiates the smart pointer.

// declare smart pointer of Recordset
_RecordsetPtr p;

int main()
{
  // initialize OLE
  ::CoInitialize(NULL);

  // instantiate smart pointer
  HRESULT hr = p.CreateInstance(_uuidof(Recordset));
  // statements ...

  ::CoUninitialize();
  return 0;
}

Or, an instance of the struct InitOle is declared and instantiated before p. Therefore initializes OLE in it's constructor.

struct InitOle {
  InitOle() { ::CoInitialize(NULL); }
  ~InitOle() { ::CoUninitialize(); }
} _init_InitOle_;

Attempts to mix ADO (through #import) and either MFC DAO or the DAO SDK in the same implementation file will generate the compilation errors.

error C2011: 'EditModeEnum' : 'enum' type redefinition
error C2011: 'LockTypeEnum' : 'enum' type redefinition
error C2011: 'FieldAttributeEnum' : 'enum' type redefinition
error C2011: 'DataTypeEnum' : 'enum' type redefinition
error C2011: 'ParameterDirectionEnum' : 'enum' type redefinition
error C2011: 'RecordStatusEnum' : 'enum' type redefinition

To work around this

Separate ADO and DAO code into separate .cpp files. Keep the use of #import or #include <afxdao.h/dbdao.h> in separate implementation files as well.

Modify the #import statement to create a namespace for anything generated for ADO. This means have to reference the namespace when referencing an ADO object.

#include <afxdao.h>
#import "msado15.dll" \
  rename_namespace("AdoNS") \
  rename("EOF", "adoEOF")

void ADOOnly(void)
{
  using namespace AdoNS;

  _RecordsetPtr prs;

  // Generates Compile Errors:
  CDaoRecordset rs;
}

void MixAdoAndDao(void)
{
  AdoNS::_RecordsetPtr prs;

  // Compiles just fine
  CDaoRecordset drs;
}

The "." operator is used as if the variable were an instance of a class (e.g., recordset.CreateInstance). In another case, the "->" operator is used as if the variable were a pointer to an interface (e.g., recordset->Open). One variable can be used in two ways because the "->" operator is overloaded to allow an instance of a class to behave like a pointer to an interface. A private class member of the instance variable contains a pointer to the _Recordset interface; the "->" operator returns that pointer and the returned pointer accesses the members of the _Recordset object.

For each operation within a class (that is, a method or property call), there is a declaration to call the operation directly (that is, the "raw" form of the operation), and a declaration to call the raw operation and throw a COM error if the operation fails to execute successfully. If the operation is a property, there is usually a compiler directive that creates an alternative syntax for the operation that has syntax like Visual Basic.

Operations that retrieve the value of a property have names of the form GetProperty. Operations that set the value of a property have names of the form, PutProperty. Operations that set the value of a property with a pointer to an ADO object have names of the form PutRefProperty. For example, PutCollect(const _variant_t& Index, const _variant_t& pvar).

variable = objectPtr->GetProperty(); // get property value 
objectPtr->PutProperty(value);       // set property value
objectPtr->PutRefProperty(&value);   // set property with object pointer

Data type equivalent

unsigned char for byte
short for integer
long for long
_variant_t for variant, a structure that contains a value member and a data type member. It may contain a wide range of other data types including another Variant, BSTR, Boolean, IDispatch or IUnknown pointer, currency, date and so on. COM provides methods that make it easy to convert one data type to another.
_bstr_t for string/ BSTR (Basic STRing), a structure that contains a character string and the string's length. COM provides methods to allocate, manipulate and free a BSTR.

If the _variant_t or _bstr_t class has a constructor that matches the data type of the argument, it is no need to cast it. However, if the argument is ambiguous, that is, the argument's data type matches more than one constructor, then must cast the argument with the appropriate data type to invoke the correct constructor.

_variant_t VariableName(value);
_variant_t VariableName((data type cast) value);
_variant_t VariableName(value, VT_DATATYPE);
_variant_t VariableName(interface* value, bool fAddRef = true);

In C/ C++, all operands must be specified. To specify missing parameter in DAO member functions whose data type is

string: specify a _bstr_t containing a null string
variant: specify a _variant_t with a value of DISP_E_PARAMNOTFOUND and a type of VT_ERROR Alternatively, specify the equivalent constant, vtMissing, which is supplied by the #import directive. The exception are, Connection::Execute, Command::Execute and Recordset::NextRecordset, where VARIANT* should be NULL to specify missing parameter.

In COM, most operations return an HRESULT return code that indicates whether a function completed successfully. The #import directive generates wrapper code around each "raw" method or property and checks the returned HRESULT. If the HRESULT indicates failure, the wrapper code throws a COM error by calling _com_issue_errorex() with the HRESULT return code as an argument. COM error objects can be caught in a try-catch block. (For efficiency's sake, catch a reference to a _com_error object.)

These are ADO errors: they result from the ADO operation failing. Errors returned by the underlying provider appear as Error objects in the Connection object Errors collection.

The documentation for the ADO Error object indicates that the Errors collection is populated if any error occurs within ADO or its underlying provider. This is somewhat incorrect. Depending on the source of the error, or even bugs in the underyling provider to ADO (OLE-DB) or within ADO itself, the errors collection may not be populated. Thus need to track the HRESULT returned by many ADO methods, as well as if the _com_error exception has been raised by #import generated classes.

void PrintComError(_com_error &e)
{
  _bstr_t bstrSource(e.Source());
  _bstr_t bstrDescription(e.Description());

  // print Com errors
  printf("Error\n");
  printf("\tCode = %08lx\n", e.Error());
  printf("\tCode meaning = %s\n", e.ErrorMessage());
  printf("\tSource = %s\n", (LPCSTR) bstrSource);
  printf("\tDescription = %s\n", (LPCSTR) bstrDescription);
}

void PrintProviderError(_ConnectionPtr pConnection)
{
  // print provider Errors from Connection object
  // pErr is a record object in the Connection's Error collection
  ErrorPtr  pErr = NULL;

  if(pConnection->Errors->Count > 0)
  {
    long nCount = pConnection->Errors->Count;

    // collection ranges from 0 to nCount-1
    for(long i=0; i<nCount; i++)
    {
      pErr = pConnection->Errors->GetItem(i);
      printf("Error number: %x\t%s\n", pErr->Number, (LPCSTR)pErr->Description);
    }
  }
}

Sample code that demonstrates how to open a connection and report any errors encountered, as well as handling exceptions and cracking the returned HRESULT:

// Obtain the error message for a given HRESULT
CString LogCrackHR(HRESULT hr)
{
  LPVOID lpMsgBuf;
  CString strTmp;

  ::FormatMessage(FORMAT_MESSAGE_ALLOCATE_BUFFER |
                  FORMAT_MESSAGE_FROM_SYSTEM,
                  NULL,
                  hr,
                  MAKELANGID(LANG_NEUTRAL, SUBLANG_DEFAULT),
                  (LPTSTR) &lpMsgBuf,
                  0,
                  NULL);

  // STR_TMP is defined within LOG.CPP to provide safe format string
  // for both ANSI and UNICODE
  strTmp.Format("%s", (char*)lpMsgBuf);

  // Free the buffer.
  ::LocalFree(lpMsgBuf);

  return strTmp;
}

// Obtain information from the Errors Collection.
HRESULT LogAdoErrorImport(_ConnectionPtr pConn)
{
  ErrorsPtr pErrors = NULL;
  ErrorPtr pError = NULL;
  CString strTmp;
  HRESULT hr = (HRESULT)0L;
  long nCount;

  // Don't have an un-handled exception in the handler that handles exceptions!
  try
  {
    pErrors = pConn->GetErrors();

    nCount = pErrors->GetCount();

    for(long i=0; (!FAILED(hr)) && (i<nCount); i++)
    {
      TRACE("\t Dumping ADO Error %d of %d", i+1, nCount);

      hr = pErrors->get_Item((_variant_t)((long)i), &pError);

      _bstr_t bstrSource(pError->GetSource());
      _bstr_t bstrDescription(pError->GetDescription());
      _bstr_t bstrHelpFile(pError->GetHelpFile());
      _bstr_t bstrSQLState(pError->GetSQLState());

      TRACE("\t\t Number      = %ld", pError->GetNumber());
      TRACE("\t\t Source      = %s", (LPCTSTR)bstrSource);
      TRACE("\t\t Description = %s", (LPCTSTR)bstrDescription);
      TRACE("\t\t HelpFile    = %s", (LPCTSTR)bstrHelpFile);
      TRACE("\t\t HelpContext = %ld", pError->GetHelpContext());
      TRACE("\t\t SQLState    = %s", (LPCTSTR)bstrSQLState);
      TRACE("\t\t HelpContext = %ld", pError->GetHelpContext());
      TRACE("\t\t NativeError = %ld", pError->GetNativeError());
    }
  }
  catch(CException *e)
  {
    TRACE("*** UNABLE TO LOG EXCEPTION ***");
    e->Delete();
  }
  catch(...)
  {
    TRACE("*** UNABLE TO LOG EXCEPTION ***");
  }

  if(pErrors) pErrors->Release();
  if(pError) pError->Release();

  return hr;
}

void CAdoDemoDlg::OnAdoTest()
{
  HRESULT hr = S_OK;
  _ConnectionPtr pConn;

  hr = ::CoInitialize(NULL);

  if(!FAILED(hr))
    hr = pConn.CreateInstance(_uuidof(Connection));

  // The following exception handling assumes a valid Connection
  // object, so drop out if we couldn't create one for any reason.
  if (FAILED(hr))
    return;

  try
  {
    // ... Your code goes here.

    pConn->Close();

    // For any error condition, dump results to TRACE.
    // You may get a failure that does not raise an exception.
    // The ADO Errors collection will likely be empty, but check anyway.
    if(FAILED(hr))
    {
      TRACE("*** HRESULT ***");
      TRACE(LogCrackHR(hr));

      LogAdoErrorImport(pConn);
    }
  }
  catch(CException *e)
  {
    TRACE("*** Unhandled MFC Exception ***");
    e->Delete();
  }
  catch(_com_error &e)
  {
    // Crack _com_error
    _bstr_t bstrSource(e.Source());
    _bstr_t bstrDescription(e.Description());

    TRACE("Exception thrown for classes generated by #import");
    TRACE("\tCode = %08lx\n", e.Error());
    TRACE("\tCode meaning = %s\n", e.ErrorMessage());
    TRACE("\tSource = %s\n", (LPCTSTR)bstrSource);
    TRACE("\tDescription = %s\n", (LPCTSTR)bstrDescription);

    // Errors Collection may not always be populated.
    if(FAILED(hr))
    {
      TRACE("*** HRESULT ***");
      TRACE(LogCrackHR(hr));
    }

    // Crack Errors Collection.
    LogAdoErrorImport(pConn);
  }
  catch(...)
  {
    TRACE("*** Unhandled Exception ***");
  }
}

There are three kinds of providers:

Data provider owns its own data and exposes it in tabular form to the application.
Service provider encapsulates a service by producing and consuming data, augmenting features like the query processor and cursor engine in the ADO applications. It does not own the data.
Service provider may also be further defined as a service component, which must work in conjunction with other service providers or components.

ADO is powerful and flexible because it can connect to any of several different data providers and still expose the same programming model, regardless of the specific features of any given provider. However, because each data provider is unique, how the application interacts with ADO will vary slightly by data provider. The differences usually fall into one of three categories:

connection string of Connection
Command object usage
provider-specific Recordset behavior

To create a Access database file, use Microsoft OLE DB Provider for Microsoft Jet databases. Its typical connection string is

L"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=databasePath.mdb;User ID=userID;Password=password;"

If user ID is not specified, the string, L"admin", is used by default. If password is not specified, the empty string (""), is used by default.

When insert/ delete records continually, an Access database will only grow. This happens because the database always extends when new insertions occur. It never writes a new record to the space that was allocated by a previous insertion, even if those previously inserted records are currently deleted. So, if have a database that's 1 Mb size, for example (let's say 1,000 records), delete them all and then insert another 1,000 records, the database will be about 2 Mb. Periodically compact the database can decrease the size and achieve better performance.

To compact a database, either install the MS Access application or using Microsoft Jet OLE DB Provider and Replication Objects (JRO). This capability was implemented for the first time in the JET OLE DB Provider version 4.0 (Msjetoledb40.dll) and JRO version 2.1 (Msjro.dll). These DLL files are available after the install of MDAC 2.1.

// besides import msado15.dll (MDAC2.1)
#import "C:\Program Files\Common Files\System\ADO\msjro.dll" \
  no_namespace

try
{
  IJetEnginePtr jet(_uuidof(JetEngine));
  jet->CompactDatabase(L"Provider=Microsoft.Jet.OLEDB.4.0;" \
                       L"Data Source=sourceDatabasePath.mdb;" \
                       L"Jet OLEDB:Database Password=password",
                       L"Provider=Microsoft.Jet.OLEDB.4.0;" \
                       L"Data Source=DestDatabasePath.mdb;" \
                       L"Jet OLEDB:Engine Type=4;" \
                       L"Jet OLEDB:Database Password=password");
}
catch(_com_error &e)
{
  PrintComError(e);
}

Destination can be same as the source. The Jet OLEDB:Engine Type=4 is only for Jet 3.x format MDB files. If this value is left out, the database is automatically upgraded to the 4.0 version (Jet OLEDB:Engine Type=5).

A Command issued across an established connection manipulates the data source in some way. Typically the command adds, deletes, or updates data in the data source, or retrieves data in the form of rows in a table.

To execute a query without using a Command object, pass a query string to Recordset::Open method. However, a Command object is required when want to persist the command text and re-execute it or use query parameters.

The Recordset is the primary means of examining and modifying data in the rows. It allows to:

Specify which rows are available for examination.
Traverse the rows.
Specify the order in which the rows may be traversed.
Add, change or delete rows.
Update the data source with changed rows.
Manage the overall state of the Recordset.

All Recordset objects are constructed using records (rows) and fields (columns).Recordset consists of one or more Fields. Each Field (column) has among its attributes a name, a data type and a value.

Make a connection to a data source (Connection). Optionally, begin a transaction.
Optionally, create an object to represent an SQL command (Command).
Optionally, specify columns, tables, and values in the SQL command as variable parameters (Parameter).
Execute the command (Command, Connection or Recordset).
If the command is row-returning, store the rows in a cache (Recordset).
Optionally, create a view of the cache so you can sort, filter and navigate the data (Recordset).
Edit the data, by either adding, deleting or changing rows and columns (Recordset).
If appropriate, update the data source with changes from the cache (Recordset).
If a transaction was used, accept or reject the changes made during the transaction. End the transaction (Connection).

Typical ADO usage in application

_ConnectionPtr pConn = NULL;
HRESULT hr = S_OK;
hr = pConn.CreateInstance(_uuidof(Connection));

if (!FAILED(hr))
// or
if (SUCCEEDED(hr))
  hr = pConn->Open(L"pubs", L"userID", L"password");

Connect to Access database through JET (using Data Source Name, or DSN, and individual arguments instead of a connection string)
```
pConn->Open(L"Provider=Microsoft.Jet.OLEDB.4.0; \
            Data Source=databasePath.mdb", "", "",  adModeRead);
```

Connect to SQL server without using DSN

pConn->ConnectionTimeout = 5; // set time to 5s
pConn->Open(L"driver={SQL Server};Server=serverIP; \
            DATABASE=pubs; \
            UID=userID;PWD=password", "", "", adModeUnknown);

Connect to ODBC database using DSN tag

pConn->Open(L"DSN=pubs;", "", "", adModeReadWrite);

Connect using a DSN and OLE DB tags.

pConn->Open(L"Data Source=pubs;UID=userID;PWD=password;",
            "", "", adModeShareDenyWrite);

RecordSet can initialized using one of the 3 ways:

```
_RecordsetPtr rs("ADODB.Recordset");
```

_RecordsetPtr rs = NULL;
HRESULT hr = S_OK;
hr = rs.CreateInstance("ADODB.Recordset");

_RecordsetPtr rs = NULL;
HRESULT hr = S_OK;
hr = rs.CreateInstance(_uuidof(_Recordset));

The first way is not recommended because it does not return a failed HRESULT if something goes wrong.

```
if (!FAILED(hr))
// or
if (SUCCEEDED(hr))
  hr = rs->Open (L"SELECT * FROM TABLE_A",
                 (IDispatch*)pConn.GetInterfacePtr(),
                 adOpenForwardOnly, adLockOptimistic, adCmdText);
```
In RecoardSet::Open(const _variant_t& Source, const _variant_t& ActiveConnection, Enum CursorTypeEnum CursorType, Enum LockTypeEnum LockType, long Options), the Source evaluates to a valid Command object, an SQL statement, a table name, a stored procedure call, a URL or the name of a file or Stream object containing a persistently stored Recordset; while the ActiveConnection takes a reference to a _variant_t, which is a connection string (e.g., L"DSN=pubs;uid=userID;pwd=password;") or a pointer to an open Connection object (e.g., "(IDispatch*)pConn.GetInterfacePtr()"). The cast, (IDispatch*), resolves the ambiguity with another constructor that takes a pointer to an IUnknown interface. It is a crucial that ADO is an IDispatch interface. Whenever a pointer to an ADO object must be passed as a variant, that pointer must be cast as a pointer to an IDispatch interface.
```
if (!FAILED(hr))
// or
if (SUCCEEDED(hr))
{
  // Do something here ...
}
```
For example, add an entry to the table
```
rs->AddNew();
rs->PutCollect(L"FieldName", &(_variant_t(value)));
rs->Update();
```
In PutCollect(const _variant_t& Index, const _variant_t& pvar), Index is a variant, the value of which is either a numerical index of the member in the collection, or a string containing the name of the member.
```
rs->Close();
rs = NULL;
```
```
pConn->Close();
pConn = NULL;
```

When used with some providers (such as the Microsoft ODBC Provider for OLE DB in conjunction with Microsoft SQL Server), can create Recordset objects independently of a previously defined Connection object by passing a connection string with its Open method. ADO will create a Connection object, but it doesn't assign that object to an object variable. So, to open multiple Recordset objects over the same connection, explicitly create and open a Connection object; this assigns the Connection object to an object variable. If not, ADO creates a new Connection object for each new Recordset, even if pass the same connection string.

Index