Click here to Skip to main content
15,867,594 members
Articles / Desktop Programming / ATL
Article

Database Development using Visual C++ and OLE DB: Establishing the connection

Rate me:
Please Sign up or sign in to vote.
4.98/5 (50 votes)
28 Apr 200517 min read 288.3K   139   37
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.

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( );

Image 1

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.

DescriptionProgIDClassIDDatabase
IBM OLE DB Provider for DB2IBMDADB2.1{1E29B6C3-8EC6-11D2-AF46-000629B3CD56}DB2
Microsoft Jet 4.0 OLE DB ProviderMicrosoft.Jet.OLEDB.4.0{DEE35070-506B-11CF-B1AA-00AA00B8DE95}Microsoft Access
Microsoft OLE DB Provider for SQL ServerSQLOLEDB{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98}Microsoft SQL Server
MySQL.OLEDB ProviderMySQLProv{C86FB69E-3664-11D2-A112-00104BD15372}MySQL
Oracle Provider for OLE DBOraOLEDB.Oracle{3F63C36E-51A3-11D2-BB7D-00C04FA30080}Oracle
Microsoft OLE DB Provider for OracleMSDAORA{E8CC4CBE-FDFF-11D0-B865-00A0C9081C1D}Oracle

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:

// 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:

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

PropertyProperty SetTypeApplicable to
DBPROP_INIT_DATASOURCEDBPROPSET_DBINITVT_BSTR
  • Microsoft.Jet.OLEDB.4.0
  • MSDAORA
  • MySQLProv
  • OraOLEDB.Oracle
  • SQLOLEDB
  • IBMDADB2.1
DBPROP_INIT_CATALOGDBPROPSET_DBINITVT_BSTR
  • SQLOLEDB
DBPROP_AUTH_USERIDDBPROPSET_DBINITVT_BSTR
  • Microsoft.Jet.OLEDB.4.0
  • MSDAORA
  • MySQLProv
  • OraOLEDB.Oracle
  • SQLOLEDB
  • IBMDADB2.1
DBPROP_AUTH_PASSWORDDBPROPSET_DBINITVT_BSTR
  • Microsoft.Jet.OLEDB.4.0
  • MSDAORA
  • MySQLProv
  • OraOLEDB.Oracle
  • SQLOLEDB
  • IBMDADB2.1
DBPROP_AUTH_INTEGRATEDDBPROPSET_DBINITVT_BSTR
  • MySQLProv
  • SQLOLEDB
DBPROP_AUTH_MASK_PASSWORDDBPROPSET_DBINITVT_BOOL
  • MySQLProv
  • SQLOLEDB
DBPROP_AU-TH_ENCRYPT_PASSWORDDBPROPSET_DBINITVT_BOOL
  • MySQLProv
  • SQLOLEDB
DBPROP_INIT_ASYNCHDBPROPSET_DBINITVT_I4
  • SQLOLEDB
DBPROP_INIT_GENERALTIMEOUTDBPROPSET_DBINITVT_I4
  • SQLOLEDB
DBPROP_INIT_LOCATIONDBPROPSET_DBINITVT_BSTR
  • MySQLProv
DBPROP_INIT_MODEDBPROPSET_DBINITVT_I4
  • MySQLProv
  • Microsoft.Jet.OLEDB.4.0
  • IBMDADB2.1
DBPROP_INIT_HWNDDBPROPSET_DBINITVT_I8
  • Microsoft.Jet.OLEDB.4.0
  • MSDAORA
  • MySQLProv
  • OraOLEDB.Oracle
  • SQLOLEDB
DBPROP_INIT_PROMPTDBPROPSET_DBINITVT_I2
  • Microsoft.Jet.OLEDB.4.0
  • MSDAORA
  • MySQLProv
  • OraOLEDB.Oracle
  • SQLOLEDB
DBPROP_INIT_TIMEOUTDBPROPSET_DBINITVT_I4
  • MySQLProv
  • SQLOLEDB
  • 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);
    // 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:

Property IDProperty Keyword
Provider ProgIDProvider
DBPROP_INIT_DATASOURCEData Source
DBPROP_INIT_LOCATIONLocation
DBPROP_INIT_CATALOGInitial Catalog
DBPROP_AUTH_USERIDUser Id
DBPROP_AUTH_PASSWORDPassword
DBPROP_AUTH_INTEGRATEDIntegrated Security
DBPROP_INIT_HWNDWindow Handle
DBPROP_INIT_PROMPTPrompt
DBPROP_INIT_TIMEOUTConnect Timeout

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:

Image 2

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

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:

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.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here


Written By
Software Developer (Senior) CppMax
Canada Canada
Check out our light-weight, easy to use and powerful Microsoft .net reporting tool www.cppmax.ca

Comments and Discussions

 
AnswerRe: How to continue? Pin
emadns2-Jul-06 4:47
emadns2-Jul-06 4:47 
Thanks for the kind words.

Yes this article is just for establishing the connection to the database

ATL OLE DB classes are very flexible and have near complete implementation. DEFINE_COMMAND(_EX) for fixed queries but there are many methods for variable and run-time queries.

I have built full large-scale database applications using ATL OLE DB classes, and I have not seen anything missing that I personally needed.

Concerning a followup on this article, I am currently extending this article into a book and I am half-through done. I decided to do this after feeling the strong need and good feedback. Another article will just not be enough

I appreciate your feedback

Emad

GeneralRe: How to continue? [modified] Pin
Jay Tee11-Jul-06 1:55
Jay Tee11-Jul-06 1:55 
GeneralRe: How to continue? Pin
emadns16-Jul-06 22:48
emadns16-Jul-06 22:48 
QuestionProblem How to handle empty database exception? Pin
Sujitmatrix25-May-06 2:15
Sujitmatrix25-May-06 2:15 
AnswerRe: Problem How to handle empty database exception? Pin
emadns25-May-06 3:56
emadns25-May-06 3:56 
QuestionCan't use W2A marco Pin
ty cheng24-Nov-05 19:13
ty cheng24-Nov-05 19:13 
AnswerRe: Can't use W2A marco Pin
emadns15-Dec-05 0:15
emadns15-Dec-05 0:15 
GeneralDb2OleDb.dll Pin
Cocchetto3-Oct-05 23:01
Cocchetto3-Oct-05 23:01 
GeneralEstablishing MySQL connection via MyOLEDB3.0 Pin
Michello10-Jul-05 22:58
Michello10-Jul-05 22:58 
GeneralRe: Establishing MySQL connection via MyOLEDB3.0 Pin
emadns10-Jul-05 23:13
emadns10-Jul-05 23:13 
GeneralRe: Establishing MySQL connection via MyOLEDB3.0 Pin
emadns10-Jul-05 23:15
emadns10-Jul-05 23:15 
GeneralRe: Establishing MySQL connection via MyOLEDB3.0 Pin
Michello11-Jul-05 3:28
Michello11-Jul-05 3:28 
GeneralRe: Establishing MySQL connection via MyOLEDB3.0 Pin
emadns11-Jul-05 3:37
emadns11-Jul-05 3:37 
GeneralRe: Establishing MySQL connection via MyOLEDB3.0 Pin
Michello11-Jul-05 3:37
Michello11-Jul-05 3:37 
GeneralGood article! Pin
Anonymous2-May-05 4:44
Anonymous2-May-05 4:44 
GeneralRe: Good article! Pin
emadns2-May-05 7:10
emadns2-May-05 7:10 
GeneralRe: Good article! Pin
Anonymous3-May-05 0:30
Anonymous3-May-05 0:30 
GeneralI wish... Pin
peterchen2-May-05 3:30
peterchen2-May-05 3:30 
GeneralRe: I wish... Pin
emadns2-May-05 3:38
emadns2-May-05 3:38 
GeneralRe: I wish... Pin
emadns2-May-05 7:13
emadns2-May-05 7:13 
GeneralNice article Pin
Vladimir Svrkota2-May-05 1:43
professionalVladimir Svrkota2-May-05 1:43 
GeneralRe: Nice article Pin
emadns2-May-05 3:36
emadns2-May-05 3:36 
GeneralWhere is the code Pin
KarstenK28-Apr-05 22:08
mveKarstenK28-Apr-05 22:08 
GeneralRe: Where is the code Pin
Nemanja Trifunovic29-Apr-05 2:30
Nemanja Trifunovic29-Apr-05 2:30 
GeneralRe: Where is the code Pin
emadns1-May-05 6:22
emadns1-May-05 6:22 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.