수안이의 컴퓨터 연구실

Introduction

Initializing or establishing a connection is a common pattern in many areas of Windows programming. If you want to start drawing on a window, you need first to obtain a device context by calling GetDC or CreateDC, and when you are done, you close it by calling ReleaseDC or DeleteDC. If you want to start a conversation between a client and a server using Windows sockets then you establish a connection first, and when you are done, you close it. In a similar pattern, if you want to query a database for certain records then you need to establish the connection, and when you are done, you close it. In this article, I will show you how to start a connection using ATL OLE DB consumer classes and how to obtain a session so that you can use it to query or edit the database.

To initialize the connection to a database using an OLE DB provider, you need two ATL classes: CDataSource and CSession. To use them, include the atldbcli.h file:

#include <atldbcli.h>

Why two classes? It is because you can create many sessions on the same connection. Once you open the data source using the Open or OpenFromInitializationString methods, you most likely want to start a session too. Through this session, you may create transactions and query or edit the database. This is actually needed even if you are using the schema classes. There is only one overload for the OpenFromInitializationString method, and nine overloads for the Open method.

• HRESULT OpenFromInitializationString(LPCOLESTR szInitializationString);

I will explain all of the Open method overloads, but will mostly use the first one in case I need to display the Data Link Properties dialog to allow the user to select an OLE DB provider and its connection properties.

• HRESULT Open(HWND hWnd = GetActiveWindow(), DBPROMPTOPTIONS dwPromptOptions = DBPROMPTOPTIONS_WIZARDSHEET);
• HRESULT Open(const CLSID& clsid, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(const CLSID& clsid, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(LPCSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(LPCWSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(LPCWSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(LPCSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);
• HRESULT Open(const CEnumerator& enumerator, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
• HRESULT Open(const CEnumerator& enumerator, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

Note that ATL consumer classes are based on COM, and this means:

1. We have to call CoInitialize before using these classes, probably at InitInstance, and then similarly call CoUninitialize later at the program in ExitInstance.
2. We will need to deal with HRESULT return values as indicators for success or failure.

Enumerating OLE DB Providers using CEnumerator class

CEnumerator oProviders;

HRESULT hr = oProviders.Open( );
if(SUCCEEDED(hr))
{
    // The following macro is to initialize
    // the conversion routines
    USES_CONVERSION;

    while(oProviders.MoveNext( ) == S_OK)
    {
// Now you have the provider name
// in oProviders.m_szName and description
// in oProviders.m_szDescription

#ifdef _UNICODE
        TRACE(oProviders.m_szName);
        TRACE(L"\n");
#else
        TRACE(W2A(oProviders.m_szName));
        TRACE("\n");
#endif
    }
    oProviders.Close( );
}

CEnumerator class provides the means to enumerate all OLE DB providers installed on the system. It is a convenient way of providing a custom interface for the user to select a provider and its connection properties. If you need to enumerate OLE DB providers using the standard dialog then just call the Open method of the CDataSource class without any parameters.

hr = ds.Open( );

The Standard OLE DB Data Link Properties Dialog

Note that m_szName of CEnumerator is usually the ProgID of the provider which may be used to open the connection in several overloads of the Open method.

The code works in both Unicode and ANSI builds of the application because of the #ifdef used. The USES_CONVERSION macro is used to initialize several conversion routines such as A2W which converts an ANSI string to Unicode, and W2A which does the opposite. m_szName is a wide string pointer which can be used directly in Unicode functions, but we have to convert it to ANSI using W2A routine when working in single byte builds.

The algorithm described in the example above is a typical way of ATL consumer classes. We first declare a class which actually contains an accessor with several data members such as m_szName and m_szDescription. When we call the MoveNext method, these data members are filled with the appropriate values from the next available row until MoveNext returns DB_S_ENDOFROWSET and exits the loop. We will find this pattern common when we start retrieving records from the database.

Please refer to ProvDefs.h on the accompanying disk for typedefs of the ProgIDs and ClassIDs of the above mentioned providers.

Using DBPROPSET structure and CDBPropSet class

The DBPROPSET structure is used to pass certain properties about the connection and, as we will see later, rowsets retrieved. A DBPROPSET structure contains an array of DBPROPs which resemble the desired properties. To use the structure, we first create an array of DBPROPs and assign their values. Then we attach this array to a DBPROPSET structure. If the properties belong to different property sets, then we combine the same set properties into one DBPROPSET. At the end, we will have an array of property sets. In several of the CDataSource::Open method overloads, we see a pointer to a DBPROPSET structure. This structure is filled with the connection properties and passed along with the count of the property sets. The following code segment clarifies our point:

Collapse

// the macro for conversions
USES_CONVERSION;

// build the property array of 3 properties
DBPROP rgProperties[3];
// property 1: the datasource
rgProperties[0].colid = DB_NULLID;
rgProperties[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties[0].dwStatus = 0;
rgProperties[0].vValue.vt = VT_BSTR;
rgProperties[0].vValue.bstrVal = ::SysAllocString(W2COLE(L"server_name"));
// property 2: the database name
rgProperties[1].colid = DB_NULLID;
rgProperties[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[1].dwPropertyID = DBPROP_INIT_CATALOG;
rgProperties[1].dwStatus = 0;
rgProperties[1].vValue.vt = VT_BSTR;
rgProperties[1].vValue.bstrVal = ::SysAllocString(W2COLE(L"Northwind"));
// property 3: the user id
rgProperties[2].colid = DB_NULLID;
rgProperties[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties[2].dwPropertyID = DBPROP_AUTH_USERID;
rgProperties[2].dwStatus = 0;
rgProperties[2].vValue.vt = VT_BSTR;
rgProperties[2].vValue.bstrVal = ::SysAllocString(W2COLE(L"sa"));

// combine the 3 properties into a property set structure
DBPROPSET rgPropertySet[1];
rgPropertySet[0].cProperties = 3;
rgPropertySet[0].guidPropertySet = DBRGPROPERTIESET_DBINIT;
rgPropertySet[0].rgProperties = rgProperties;

// now open the SQL Server database connection
HRESULT hr = m_ds.Open(L"SQLOLEDB", rgPropertySet, 1);
if(SUCCEEDED(hr))
    m_ds.Close();

DBPROPOPTIONS_REQUIRED is specified to indicate that this property is required to establish the connection. If not, the provider returns an error and sets dwStatus to DBPROPSTATUS_NOTSUPPORTED. The other value for dwOptions is DBPROPOPTIONS_OPTIONAL which indicates that this property is optional and the provider should not fail if the property is not met.

We could have a two property set array instead of one. In each item, we define a set of related properties as below:

Collapse

// the macro for conversions
USES_CONVERSION;

// build the property array of 3 properties
DBPROP rgProperties1[3];
// property 1: the datasource
rgProperties1[0].colid = DB_NULLID;
rgProperties1[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[0].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProperties1[0].dwStatus = 0;
rgProperties1[0].vValue.vt = VT_BSTR;
rgProperties1[0].vValue.bstrVal = ::SysAllocString(W2COLE(L"server_name"));
// property 2: the database name
rgProperties1[1].colid = DB_NULLID;
rgProperties1[1].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[1].dwPropertyID = DBPROP_INIT_CATALOG;
rgProperties1[1].dwStatus = 0;
rgProperties1[1].vValue.vt = VT_BSTR;
rgProperties1[1].vValue.bstrVal = ::SysAllocString(W2COLE(L"Northwind"));
// property 3: the user id
rgProperties1[2].colid = DB_NULLID;
rgProperties1[2].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties1[2].dwPropertyID = DBPROP_AUTH_USERID;
rgProperties1[2].dwStatus = 0;
rgProperties1[2].vValue.vt = VT_BSTR;
rgProperties1[2].vValue.bstrVal = ::SysAllocString(W2COLE(L"sa"));

// build another property array of 1 property
DBPROP rgProperties2[1];
// Property 1: application name
rgProperties2[0].colid = DB_NULLID;
rgProperties2[0].dwOptions = DBPROPOPTIONS_REQUIRED;
rgProperties2[0].dwPropertyID = SSPROP_INIT_APPNAME;
rgProperties2[0].dwStatus = 0;
rgProperties2[0].vValue.vt = VT_BSTR;
rgProperties2[0].vValue.bstrVal = 
   ::SysAllocString(W2COLE(L"my application title"));

// combine the 3 properties into a property set structure
DBPROPSET rgPropertySet[2];
rgPropertySet[0].cProperties = 3;
rgPropertySet[0].guidPropertySet = DBPROPSET_DBINIT;
rgPropertySet[0].rgProperties = rgProperties1;
rgPropertySet[1].cProperties = 1;
rgPropertySet[1].guidPropertySet = DBPROPSET_SQLSERVERDBINIT;
rgPropertySet[1].rgProperties = rgProperties2;

// now open the SQL Server database connection
HRESULT hr = m_ds.Open(L"SQLOLEDB", rgPropertySet, 2);
if(SUCCEEDED(hr))
    m_ds.Close();

What is this application name property doing? Open “master” database and look for “sysprocesses” table before calling m_ds.Close method. Open this table and you will find “my application title” listed in the program_name field. This means that it is one of the processes that is currently opening an SQL Server database.

The setting of properties in the previous examples may look a little complex. I don’t like working with structures in this way. Therefore, I will present an ATL class that will cut the learning curve of dealing with properties and property sets. This class is CDBPropSet. Look how the above code will look like using this class:

CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};

rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"sa");

hr = m_ds.Open("SQLOLEDB", rgPropertySet, 1);

That’s it. AddProperty method takes care of adding properties of different types and initializing them. Passing the pointer to the class is the same as passing the pointer to the DBPROPSET structure because the class is derived from that structure.

Connection related properties

There are many connection related properties. I will not list all of them here but for a full list, please refer to the MSDN Library. The following table shows the most important properties for our purpose. These properties are applicable in part or in full to most relational databases.

Some of the terms have been broken with a '-' (hyphen) to avoid browser scrolling.

• DBPROP_INIT_DATASOURCE

  The server name, or the database name. If a server name, then DBPROP_INIT_CATALOG carries the database name.

• DBPROP_INIT_CATALOG

  The database name in case DBPROP_INIT_DATASOURCE carries the server name.

• DBPROP_AUTH_USERID

  The login user ID.

• DBPROP_AUTH_PASSWORD

  The login password.

• DBPROP_AUTH_INTEGRATED

  Indicates the security system used whether it is a user ID-password or SSPI for the Windows security system which uses the currently logged-in user credentials.

• DBPROP_AUTH_MASK_PASSWORD

  Send the password to the provider in a masked form.

• DBPROP_AUTH_ENCRYPT_PASSWORD

  Send the password to the provider in an encrypted form.

• DBPROP_INIT_ASYNC

  Indicates whether the connection is synchronous or asynchronous. If asynchronous, the Open method returns immediately and the connection is performed in the background. In synchronous mode, the open attempt does not return until success or failure.

• DBPROP_INIT_GENERALTIMEOUT

  The default timeout period that is used when obtaining rowsets and executing commands.

• DBPROP_INIT_LOCATION

  The location of the database.

• DBPROP_INIT_MODE

  The sharing mode of the opened database. This is usually applicable to file databases such as Microsoft Access.

• DBPROP_INIT_HWND

  The window handle of the prompt dialog parent.

• DBPROP_INIT_PROMPT

  Indicates whether to prompt the user for missing connection information or not.

• DBPROP_INIT_TIMEOUT

  The database connection attempt timeout period in seconds. If the time specified in this property expires and the connection attempt is still in process, the Open method returns unsuccessfully.

CDataSource::Open method

Since we have described all the components of establishing a connection, we can now describe the CDataSource::Open method overloads. I will list a code example on how to use each overload, but each time a different database type is used. OLE DB is a powerful framework because once you open a connection to a database of your choice, you can start querying it or executing commands in a similar way among all databases.

• HRESULT Open(HWND hWnd = GetActiveWindow(), DBPROMPTOPTIONS dwPromptOptions = DBPROMPTOPTIONS_WIZARDSHEET);

  This is the only method that allows you to open the standard Data Link Properties dialog to specify the connection parameters including the provider visually. hWnd is the window handle of the standard dialog parent which defaults to the currently active window. The second parameter is to set the style of the dialog. The default is DBPROMPTOPTIONS_WIZARDSHEET which applies to a wizard style property page. You may specify DBPROMPTOPTIONS_PROPERTYSHEET for a property sheet style.

  HRESULT hr = m_ds.Open( );
  if(SUCCEEDED(hr))
      m_ds.Close( );

  The rest of the overloads are a mix-and-match among a set of parameters. Those parameters are:

  • clsid: the ClassID of the provider to be used
  • pPropSet: the property set array
  • nPropertySets: the property set array count
  • pName: the server name or database name
  • pUserName: the login user ID
  • pPassword: the login password
  • nInitMode: the sharing mode for file databases such as Microsoft Access
  • szProgID: the ProgID of the provider to be used
  • enumerator: the CEnumerator class used to specify the provider
• HRESULT Open(const CLSID& clsid, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open Microsoft SQL Server database named "Northwind" installed on a 
  // server name "server_name" using the currently Windows logged-in user 
  // credentials
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
  rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_INTEGRATED, L"SSPI");
  
  CLSID clsid = {0xc7ff16cL,0x38e3,0x11d0,
                {0x97,0xab,0x0,0xc0,0x4f,0xc2,0xad,0x98}};
  hr = m_ds.Open(clsid, rgPropertySet, 1);

• HRESULT Open(const CLSID& clsid, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open a Microsoft Access database with
  // path C:\Program Files\Microsoft
  //      Office\Office10\Samples\Northwind.mdb exclusively
  
  CLSID clsid = {0xdee35070L,0x506b,0x11cf, 
                {0xb1,0xaa,0x0,0xaa,0x0,0xb8,0xde,0x95}};
  hr = m_ds.Open(clsid, _T("C:\\Program Files\\Microsoft Office" 
       "\\Office10\\Samples\\Northwind.mdb"), 
       NULL, NULL, DB_MODE_SHARE_EXCLUSIVE);

• HRESULT Open(LPCSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open an Oracle database using Microsoft OLE DB Provider for Oracle
  // Open OraDB1 database with user name SYS and password 
  // change_on_install
  // Note that it is using the ANSI string for the provider
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"OraDB1");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"SYS");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, L"change_on_install");
  
  hr = m_ds.Open("MSDAORA", rgPropertySet, 1);

• HRESULT Open(LPCWSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open an Oracle database using Oracle Provider for OLE DB
  // Open OraDB1 database with user name SYS and password 
  // change_on_install
  // Note that it is using the UNICODE string for the provider ProgID
  
  hr = m_ds.Open(L"OraOLEDB.Oracle", _T("OraDB1"), 
                 _T("SYS"), _T("change_on_install"));

• HRESULT Open(LPCWSTR szProgID, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);

  // Open an MySQL database using a location "localhost", a database 
  // name of "mysql", a user id of "root"
  
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_LOCATION, L"localhost");
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"mysql");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, L"root");
  
  hr = m_ds.Open("MySQLProv", rgPropertySet, 1);

• HRESULT Open(LPCSTR szProgID, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open a DB2 database named TOOLSDB with dbadmin user id and password
  hr = m_ds.Open("IBMDADB2.1", "TOOLSDB", "dbadmin", "dbadmin");

• HRESULT Open(const CEnumerator& enumerator, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
  Collapse

  // Open Northwind SQL Server database in "server_name" using SSPI 
  // Integrated security
  CEnumerator oProviders;
  
  CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
  
  rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, L"server_name");
  rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, L"Northwind");
  rgPropertySet[0].AddProperty(DBPROP_AUTH_INTEGRATED, L"SSPI");
  
  hr = oProviders.Open( );
  if(SUCCEEDED(hr))
  {
      // The following macro is to initialize the conversion routines
      USES_CONVERSION;
  
      while((hr = oProviders.MoveNext( )) == S_OK)
      {
  #ifdef _UNICODE
          if(lstrcmpi(oProviders.m_szName, L"SQLOLEDB") == 0)
  #else
          if(lstcmpi(W2A(oProviders.m_szName), "SQLOLEDB") == 0)
  #endif
          {
              hr = m_ds.Open(oProviders, rgPropertySet, 1);
              break;
          }            
      }
      oProviders.Close( );
  }

• HRESULT Open(const CEnumerator& enumerator, LPCTSTR pName, LPCTSTR pUserName = NULL, LPCTSTR pPassword = NULL, long nInitMode = 0);

  // Open Northwind Access database exclusively
  CEnumerator oProviders;
  
  hr = oProviders.Open( );
  if(SUCCEEDED(hr))
  {
      // The following macro is to initialize
      // the conversion routines
      USES_CONVERSION;
  
      while((hr = oProviders.MoveNext( )) == S_OK)
      {
  #ifdef _UNICODE
          if(lstrcmpi(oProviders.m_szName, 
             L" Microsoft.Jet.OLEDB.4.0") == 0)
  #else
          if(lstcmpi(W2A(oProviders.m_szName), 
                  " Microsoft.Jet.OLEDB.4.0") == 0)
  #endif
          {
              hr = m_ds.Open(oProviders, T("C:\\Program Files\\" 
                   "Microsoft Office\\Office10\\Samples\\Northwind.mdb"), 
                   NULL, NULL, DB_MODE_SHARE_EXCLUSIVE);
              break;
          }            
      }
      oProviders.Close( );
  }

I intentionally went through each overload to demonstrate the connection to different types of databases.

OpenFromInitializationString method

HRESULT OpenFromInitializationString(LPCOLESTR szInitializationString, 
                                             bool fPromptForInfo = false);

The first parameter is the connection string which specifies all the properties to perform the connection including the provider ProgID. The string should be supplied in a wide format. A2W and W2COLE functions may be used to obtain the wide format of an ANSI string. The second parameter indicates whether to prompt the user for the missing information or not. The default is not to prompt and the connection attempt just fails. The connection string must be in a certain format where key-value pairs are separated by a semicolon. The key is the property name and the value is its value. The following table shows the property names and their corresponding property IDs:

The following shows correct connection strings:

// Open an Oracle database using Microsoft Provider
Provider=MSDAORA;Data Source=OraDB1;User Id=SYS;Password=change_on_install

// Open an MySQL database
Provider=MySQLProv;Location=localhost;Data Source=mysql;User Id=root

// Open a Microsoft Access database
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program Files\
             Microsoft Office\Office 10\Samples\Northwind.mdb

So far, we have seen how to open a database. To close it, simply call the Close method of CDataSource. Opening a database is a time consuming task, so unless you specifically need to close the connection after a query, you should open the database at program load and create the CDataSource and CSession members of the main window or application class and use this same connection to query or edit the database throughout the application.

CSession class

Now, as we have the connection open, we are ready to create a session to be able to work with the database. The CSession class handles the opening of sessions and creating transactions. To open a session, use the CSession::Open method.

HRESULT Open(const CDataSource& ds);

It takes one parameter which is a reference to the open data source. For example:

CDataSource ds;
CSession session;
HRESULT hr = ds.OpenFromInitializationString(L"Provider=MySQLProv;" 
             "Location=localhost;Data Source=mysql;User Id=root");

if(SUCCEEDED(hr))
{
    hr = session.Open(ds);
    if(SUCCEEDED(hr))
    {
        // The session is now open
        // use it to query or edit the database

        session.Close();
    }
    ds.Close();
}

As shown in the example, the Close method should be called to close the session.

The most important characteristic of a session is the ability to create transactions. A transaction is a set of one or more commands executed on the database with the necessity to either commit all the commands or discard all of them. For example, if we want to make changes to the Orders table and then add an entry in the history log table, then we need to make sure that the sequence is fully executed and not just updating the Orders table without updating the history log due to an interruption in the process. Therefore, we wrap the sequence within a transaction and no changes are persisted to the database until all the commands are executed. Therefore, there are three methods to handle transactions in the session object. StartTransaction begins the transaction process. Then the user has to either call Commit method to actually persist the changes to the database, or Abort to discard any changes since StartTransaction was called. If neither Commit nor Abort were called and then another transaction was started on the same session, then the new transaction is considered nested, and a call to Commit later will commit both transactions. The following shows a draft of the process.

hr = m_session.Open(m_ds);
hr = m_session.StartTransaction( );
…. Do update 1
…. Do update 2
…. Do update n
m_session.Commit( );

Error Handling

As we have seen so far, all methods return HRESULTs. We may check if this return value is a success indicator by using the SUCCEEDED(hr) macro. If we want to check for a failure then we use the FAILED(hr) macro.

hr = m_ds.Open( );
if(SUCCEEDED(hr))
{
    // The connection was opened
}
else
{
    // The connection failed
}

A useful little feature in Visual Studio is the ability to write the variable name in the Watch window to show its value at debug time. If we type hr then we can see its value. Moreover, if we type hr,hr then we get the constant name for the error code. The following figure shows E_FAIL instead of the plain number as the value of hr:

This is useful in many cases of debugging the failure of opening a connection or executing a command, but what if we needed a more detailed description for the error occurred. I will discuss three methods to obtain a description of the error.

Method 1: IErrorInfo interface

An OLE DB application may indicate that an error has occurred by calling the SetErrorInfo API method. The consumer application can check for this error through GetErrorInfo. GetErrorInfo returns a pointer to an IErrorInfo interface. By calling the IErrorInfo::GetDescription method, the user can get an error description. The following code snippet shows how to do that:

IErrorInfo* pErrInfo;
HRESULT hr = ::GetErrorInfo(0, &pErrInfo);
if(SUCCEEDED(hr))
{
    BSTR bstrErrDescription;
    pErrInfo->GetDescription(&bstrErrDescription);

    CString strDescription = bstrErrDescription;
    AfxMessageBox(LPCTSTR(strDescription);

    pErrInfo->Release();
    ::SysFreeString(bstrErrDescription);
}

GetDescription fills a BSTR variable. There are many ways to convert this type of a string to ANSI or Unicode versions. One way is just simply to assign it to a CString. Another way is to use the OLE2T method. So, I will leave it to the user. When finishing, we need to do a little cleanup by calling Release on the interface pointer and SysFreeString to free the BSTR returned from GetDescription.

Method 2: CDBErrorInfo class

USES_CONVERSION;
CDBErrorInfo einfo;
BSTR bstrDescription;
ULONG nRecords = 0;
HRESULT hr = einfo.GetErrorRecords(&nRecords);
if(SUCCEEDED(hr) && nRecords > 0)
{
LCID lcid = GetSystemDefaultLCID();
    for(ULONG nIndex = 0; nIndex < nRecords; nIndex++)
    {
        hr = einfo.GetAllErrorInfo(nIndex, lcid, &bstrDescription);
        if(SUCCEEDED(hr))
        {
            AfxMessageBox(OLE2T(bstrDescription));
            SysFreeString(bstrDescription);
        }
    }
}

As shown in the code, GetErrorRecords is called to fill the CDBErrorInfo with the error array (named records). We loop through the records and call GetAllErrorInfo on each record to obtain the description. This is another way of doing it and a similar method is implemented in the AtlTraceErrorRecords function which is described in method 3.

Method 3: AtlTraceErrorRecords

In case you wanted to see the resulting errors in the debugger window when returning from a query, you may just call AtlTraceErrorRecords. It uses the algorithm described in method 2 to trace the resulting errors. You may just call this method without going through the hassle of writing the code to get that information.

Creating Databases on the fly

You probably had cases when you needed to create the database for the user when he/she logs in the first time of using your application, or maybe restore the database upon failure by re-creating it programmatically. Fortunately, there is a method to do that for many database types. Some databases like Oracle won’t just let you create a database by issuing a CREATE DATABASE method at least through the OLE DB Provider as it requires many steps to do that, but others may allow you to create the database by just executing a few lines of code. I will describe the methods for some databases and apologize for not covering all of them.

Creating a Microsoft Access database programmatically

I suggest two approaches for creating a Microsoft Access database programmatically:

1. This may seem unusual but it does work. Create a blank Microsoft Access database and then copy and rename it whenever you want to create a new blank database. The following code snippet shows how to do that. Assume that the blank database copy is named db.mdb:

   BOOL CreateAccessDatabase(LPCTSTR szTemplatePath, 
                              LPCTSTR szNewDatabasePath)
   {
       return CopyFile(szTemplatePath, szNewDatabasePath, TRUE);
   }

   szTemplatePath is the path and name for the master copy database and szNewDatabasePath is the new path and name for the created database.

2. The second approach is to use the CreateDataSource method of the IDBDataSourceAdmin interface. This interface is implemented in Microsoft.Jet.OLEDB.4.0 provider and can be used to create or destroy a data source explicitly. The following function shows how to use this interface to create a blank .mdb file at a path specified in the szDatabasePath parameter. This method is explained in a Knowledge Base article, but I tried here to avoid using interface pointers as much as possible and use ATL classes instead.
   Collapse

   BOOL CreateAccessDatabase(LPCTSTR szDatabasePath)
   {
       CDataSource ds;
       IDBDataSourceAdmin* pIDBDataSourceAdmin = NULL;
   
       CLSID clsid = {0xdee35070L,0x506b,0x11cf, 
                     {0xb1,0xaa,0x0,0xaa,0x0,0xb8,0xde,0x95}};
       HRESULT hr = CoCreateInstance(clsid, NULL, CLSCTX_INPROC_SERVER, 
                    __uuidof(IDBInitialize), (void**)&ds.m_spInit);
       if (FAILED(hr))
           return FALSE;
   
       USES_CONVERSION;
   
       CDBPropSet rgPropertySet(DBPROPSET_DBINIT);
       rgPropertySet.AddProperty(DBPROP_INIT_DATASOURCE, 
                                 T2BSTR(szDatabasePath));
   
       hr = ds.m_spInit->QueryInterface(IID_IDBDataSourceAdmin, 
           (void**)&pIDBDataSourceAdmin);
       if(FAILED(hr))
       {
           ds.Close();
           return FALSE;
       }
   
       hr = pIDBDataSourceAdmin->CreateDataSource(1, 
            &rgPropertySet, NULL, IID_NULL, NULL);
       if(FAILED(hr))
       {
           pIDBDataSourceAdmin->Release();
           ds.Close();
           return FALSE;
       }
   
       pIDBDataSourceAdmin->Release();
   
       ds.Close();
   
       return TRUE;
   }

Creating a MySQL database programmatically

I will try to create a MySQL database by issuing a "CREATE DATABASE" command through the context of the default database named "mysql" which is setup automatically upon the installation of MySQL. I will use the CCommand class to do that, but will postpone the discussion of this class. The following method creates a MySQL database at a location specified in the szLocation parameter and with a name passed in the szDatabaseName parameter. Also, we need the User ID and Password to open a connection to the mysql database first.

Collapse

BOOL CreateMySQLDatabase(LPCTSTR szLocation, 
       LPCTSTR szDatabaseName, LPCTSTR szUserId, LPCTSTR szPassword)
{
    USES_CONVERSION;
    
    CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
    rgPropertySet[0].AddProperty(DBPROP_INIT_LOCATION, T2BSTR(szLocation));
    rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, T2BSTR(_T("mysql")));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, T2BSTR(szUserId));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, T2BSTR(szPassword));

    CDataSource ds;
    HRESULT hr = ds.Open(_T("MySQLProv"), rgPropertySet, 1);
    if(FAILED(hr))
        return FALSE;

    CSession session;
    hr = session.Open(ds);
    if(FAILED(hr))
    {
        ds.Close();
        return FALSE;
    }

    CCommand<CNoAccessor, CNoRowset> cmd;
    CString strCommand;
    strCommand.Format(_T("CREATE DATABASE '%s';"), szDatabaseName);
    hr = cmd.Open(session, LPCTSTR(strCommand), 
                  NULL, NULL, DBGUID_DBSQL, false);
    if(FAILED(hr))
    {
        session.Close();
        ds.Close();
        return FALSE;
    }

    session.Close();
    ds.Close();

    return TRUE;
}

Creating a Microsoft SQL Server database programmatically

In a similar method of creating a MySQL database, we can open the "master" database and issue a "CREATE DATABASE" command through it. The difference is that we specify a server location as the data source and a database name as the catalog. The following method does just that:

Collapse

BOOL CreateSQLServerDatabase(LPCTSTR szServerName, 
            LPCTSTR szDatabaseName, LPCTSTR szUserId, LPCTSTR szPassword)
{
    USES_CONVERSION;
    
    CDBPropSet rgPropertySet[1] = {DBPROPSET_DBINIT};
    rgPropertySet[0].AddProperty(DBPROP_INIT_CATALOG, T2BSTR(_T("master")));
    rgPropertySet[0].AddProperty(DBPROP_INIT_DATASOURCE, T2BSTR(szServerName));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_USERID, T2BSTR(szUserId));
    rgPropertySet[0].AddProperty(DBPROP_AUTH_PASSWORD, T2BSTR(szPassword));

    CDataSource ds;
    HRESULT hr = ds.Open(_T("SQLOLEDB"), rgPropertySet, 1);
    if(FAILED(hr))
        return FALSE;

    CSession session;
    hr = session.Open(ds);
    if(FAILED(hr))
    {
        ds.Close();
        return FALSE;
    }

    CCommand<CNoAccessor, CNoRowset> cmd;
    CString strCommand;
    strCommand.Format(_T("CREATE DATABASE '%s';"), szDatabaseName);
    hr = cmd.Open(session, LPCTSTR(strCommand), NULL, NULL, DBGUID_DBSQL, false);
    if(FAILED(hr))
    {
        session.Close();
        ds.Close();
        return FALSE;
    }

    session.Close();
    ds.Close();

    return TRUE;
}

We skipped a few issues such as the Integrated Security property in case the database of MySQL or Microsoft SQL Server need SSPI authentication. It is as simple as adding a boolean property DBPROP_INIT_INTEGRATED to be able to open the mysql or master databases.

Summary

Now we can open a connection to many major types of databases and start a session to do the work. We have seen also examples of creating databases programmatically.