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:
- We have to call
CoInitialize
before using these classes, probably at InitInstance
, and then similarly call CoUninitialize
later at the program in ExitInstance
.
- 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))
{
USES_CONVERSION;
while(oProviders.MoveNext( ) == S_OK)
{
#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.
Description |
ProgID |
ClassID |
Database |
IBM OLE DB Provider for DB2 |
IBMDADB2.1 |
{1E29B6C3-8EC6-11D2-AF46-000629B3CD56} |
DB2 |
Microsoft Jet 4.0 OLE DB Provider |
Microsoft.Jet.OLEDB.4.0 |
{DEE35070-506B-11CF-B1AA-00AA00B8DE95} |
Microsoft Access |
Microsoft OLE DB Provider for SQL Server |
SQLOLEDB |
{0C7FF16C-38E3-11d0-97AB-00C04FC2AD98} |
Microsoft SQL Server |
MySQL.OLEDB Provider |
MySQLProv |
{C86FB69E-3664-11D2-A112-00104BD15372} |
MySQL |
Oracle Provider for OLE DB |
OraOLEDB.Oracle |
{3F63C36E-51A3-11D2-BB7D-00C04FA30080} |
Oracle |
Microsoft OLE DB Provider for Oracle |
MSDAORA |
{E8CC4CBE-FDFF-11D0-B865-00A0C9081C1D} |
Oracle |
Please refer to ProvDefs.h on the accompanying disk for typedef
s 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 DBPROP
s which resemble the desired properties. To use the structure, we first create an array of DBPROP
s 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:
USES_CONVERSION;
DBPROP rgProperties[3];
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"));
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"));
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"));
DBPROPSET rgPropertySet[1];
rgPropertySet[0].cProperties = 3;
rgPropertySet[0].guidPropertySet = DBRGPROPERTIESET_DBINIT;
rgPropertySet[0].rgProperties = rgProperties;
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:
USES_CONVERSION;
DBPROP rgProperties1[3];
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"));
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"));
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"));
DBPROP rgProperties2[1];
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"));
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;
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.
Property |
Property Set |
Type |
Applicable to |
DBPROP_INIT_DATASOURCE |
DBPROPSET_DBINIT |
VT_BSTR |
- Microsoft.Jet.OLEDB.4.0
- MSDAORA
- MySQLProv
- OraOLEDB.Oracle
- SQLOLEDB
- IBMDADB2.1
|
DBPROP_INIT_CATALOG |
DBPROPSET_DBINIT |
VT_BSTR |
|
DBPROP_AUTH_USERID |
DBPROPSET_DBINIT |
VT_BSTR |
- Microsoft.Jet.OLEDB.4.0
- MSDAORA
- MySQLProv
- OraOLEDB.Oracle
- SQLOLEDB
- IBMDADB2.1
|
DBPROP_AUTH_PASSWORD |
DBPROPSET_DBINIT |
VT_BSTR |
- Microsoft.Jet.OLEDB.4.0
- MSDAORA
- MySQLProv
- OraOLEDB.Oracle
- SQLOLEDB
- IBMDADB2.1
|
DBPROP_AUTH_INTEGRATED |
DBPROPSET_DBINIT |
VT_BSTR |
|
DBPROP_AUTH_MASK_PASSWORD |
DBPROPSET_DBINIT |
VT_BOOL |
|
DBPROP_AU-TH_ENCRYPT_PASSWORD |
DBPROPSET_DBINIT |
VT_BOOL |
|
DBPROP_INIT_ASYNCH |
DBPROPSET_DBINIT |
VT_I4 |
|
DBPROP_INIT_GENERALTIMEOUT |
DBPROPSET_DBINIT |
VT_I4 |
|
DBPROP_INIT_LOCATION |
DBPROPSET_DBINIT |
VT_BSTR |
|
DBPROP_INIT_MODE |
DBPROPSET_DBINIT |
VT_I4 |
- MySQLProv
- Microsoft.Jet.OLEDB.4.0
- IBMDADB2.1
|
DBPROP_INIT_HWND |
DBPROPSET_DBINIT |
VT_I8 |
- Microsoft.Jet.OLEDB.4.0
- MSDAORA
- MySQLProv
- OraOLEDB.Oracle
- SQLOLEDB
|
DBPROP_INIT_PROMPT |
DBPROPSET_DBINIT |
VT_I2 |
- Microsoft.Jet.OLEDB.4.0
- MSDAORA
- MySQLProv
- OraOLEDB.Oracle
- SQLOLEDB
|
DBPROP_INIT_TIMEOUT |
DBPROPSET_DBINIT |
VT_I4 |
|
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);
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);
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);
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);
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);
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);
hr = m_ds.Open("IBMDADB2.1", "TOOLSDB", "dbadmin", "dbadmin");
HRESULT Open(const CEnumerator& enumerator, DBPROPSET* pPropSet = NULL, ULONG nPropertySets=1);
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))
{
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);
CEnumerator oProviders;
hr = oProviders.Open( );
if(SUCCEEDED(hr))
{
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 ID |
Property Keyword |
Provider ProgID |
�Provider � |
DBPROP_INIT_DATASOURCE |
�Data Source � |
DBPROP_INIT_LOCATION |
�Location � |
DBPROP_INIT_CATALOG |
�Initial Catalog � |
DBPROP_AUTH_USERID |
�User Id � |
DBPROP_AUTH_PASSWORD |
�Password � |
DBPROP_AUTH_INTEGRATED |
�Integrated Security � |
DBPROP_INIT_HWND |
�Window Handle � |
DBPROP_INIT_PROMPT |
�Prompt � |
DBPROP_INIT_TIMEOUT |
�Connect 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))
{
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 HRESULT
s. 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))
{
}
else
{
}
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:
- 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.
- 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.