OLE DB data source

Title:oledb Data source
Date:2018-01-12 21:42:37
Tags: [OLE DB, database programming, VC + +, database]
Categories:windows Database Programming
Keywords:oledb, database programming, VC + +, database
---
The data source is the data provider in OLE DB, which can be easily understood as a database program. The data source object represents a connection to the database and is the first object that needs to be created. The data source object is primarily used to configure related properties of the database connection, such as the user name password to connect to the database, etc.

The main functions of the data source are as follows:

1. Perform database identity verification
2. Prepare the corresponding resources for each connection, such as the corresponding data buffer, network connection resources
3. Set the connection properties, what permissions to the visitor, set the timeout value for the connection, and so on, the object will open the corresponding interface according to the corresponding property. All of its settings are through attributes.

OLE DB properties and property settings

OLE DB is a COM-based set of interfaces, but it is a bit different from the standard COM interface, it is a major feature of its own property settings, some interfaces although the object exists but the call QueryInterface is not out of the query, only set the appropriate interface will open, Some interfaces can behave differently depending on the value of the attribute. For example, if the corresponding read-only attribute is set, the update interface is not allowed.
Each property has a value, type, description, and read-write property, and for a rowset object, there is an indicator that indicates whether it can be applied column-wise.
The property is uniquely identified by a GUID and an integer ID.
A property set is a set of properties that all have the same group GUID. Logically, they are all used for the same function, such as having property sets for setting data source connection properties, and for setting rowset properties. They are a set of properties that are applied on the same particular object. There are attributes in each of these attribute groups where each property belongs to one or more attribute groups.
The properties are defined as follows:

typedefstruct tagDBPROP {   DBPROPID        //属性GUID   DBPROPOPTIONS   //属性的操作方式   DBPROPSTATUS    //属性设置状态   DBID            //属性ID，一般给DB_NULLID   VARIANT         //属性值} DBPROP;

Dwoptions: There are 3 ways to manipulate properties, but generally only two of them are used: Dbpropoptions_required indicates that a success must be set, and if the setting fails, the operation to set the property fails, Dbpropoptions_optional, which represents an optional , even if the property setting fails, the operation to set the property returns successfully. Dbpropoptions_setifcheap indicates if the state of the property setting is returned in the Dwstatus parameter when the property operation is set, whether it succeeds, the reason for the failure, and so on.
The property set is defined as follows:

typedefstruct tagDBPROPSET {   DBPROP *   //属性数组的指针   ULONG      //属性数组中元素个数   GUID       //属性集的GUID} DBPROPSET;

The current attribute group includes initialization attribute group, data Source property Group, session attribute group, rowset attribute Group, table attribute group, column attribute group, and so on.
Setting properties typically consists of the following steps:

1. Assigning an array of attribute type DBPro, generally tending to allocate one more, the last array element is all 0, as the end
2. Determine the property GUID of each property, that is, what property of the object we need to set
3. Fill in the corresponding property value, property operation mode
4. Fills the corresponding property set Dbpropset structure. Sets the GUID of the property set
5. Call the corresponding Interface settings property

Data source Object Interface

The interface definition for the data source object is as follows:

CoType TDataSource {   [mandatory]   //创建回话对象   [mandatory]   //创建数据源连接对象   [mandatory]   ///创建数据源的属性操作对象   [mandatory]   interface IPersist;   [optional]    interface IConnectionPointContainer;   [optional]    interface IDBAsynchStatus;   [optional]    interface IDBDataSourceAdmin;   [optional]    interface IDBInfo;   [optional]    interface IObjectAccessControl;   [optional]    interface IPersistFile;   [optional]    interface ISecurityInfo;   [optional]    interface ISupportErrorInfo;   [optional]    interface ITrusteeAdmin;   [optional]    interface ITrusteeGroupAdmin;}

In the above code, mandatory represents the interface that the data source must provide, optional represents the interface provided by the optional, use the interface that must be implemented when creating the corresponding interface, and if you need to use an optional interface, be sure to determine whether the data source is supported. The most important or first three interfaces that must be provided in the data source object

Connecting to a database

Connecting to a data source generally uses the Initialize method of the IDBInitialize interface, but there are several different ways to generate IDBInitialize interfaces, listed below

Create IDBInitialize interface directly

In this way, the CoCreateInstance function is generally called to create, the following is the specific code

#include <tchar.h>#include <windows.h>#include <strsafe.h>#define COM_NO_WINDOWS_H//If you have already included Windows.h or do not use other Windows library functions#define Oledbver 0x0260version//msdac2.6#include <oledb.h>#include <oledberr.h>#define GRS_ALLOC (SZ) HeapAlloc (GetProcessHeap (), 0,sz)#define GRS_CALLOC (SZ) HeapAlloc (GetProcessHeap (), Heap_zero_memory,sz)#define GRS_SAFEFREE (P) if (NULL! = p) {HeapFree (GetProcessHeap (), 0,p);p =null;}#define GRS_USEPRINTF () TCHAR pbuf[1024] = {}//define output macros#define GRS_PRINTF (...)grs_useprintf ();stringcchprintf (pbuf,1024,__va_args__);Writeconsole (GetStdHandle (Std_output_handle), Pbuf,lstrlen (PBuf), null,null);//Safe release, in order to develop a good coding habits, special to make this macro definition#define GRS_SAFERELEASE (I)if (NULL! = (I))    {(I)->release ();(I) =null;    }//Detect if the previous operation was successful#define Grs_com_check (HR,...)if (FAILED (HR))    {grs_printf (__va_args__);goto clear_up;    }int_tmain (intARGC, tchar* argv[]) {CoInitialize (NULL);//Create OLE DB init interfaceIDBInitialize *pdbinit = NULL; IDBProperties *pidbproperties = NULL;//Set link PropertiesDbpropset dbpropset[1] = {0}; Dbprop dbprops[5] = {0}; CLSID Clsid_msdasql = {0};//sql Server's data source objectHRESULT hres = CLSIDFromProgID (_t ("SQLOLEDB"), &clsid_msdasql); Grs_com_check (hres, _t ("Get SQLOLEDB CLSID failed with error code: 0x%08x\n"), hres); hres = CoCreateInstance (Clsid_msdasql, NULL, Clsctx_inproc_server, Iid_idbinitialize, (void* *) &pdbinit); Grs_com_check (hres, _t ("Unable to create IDBInitialize interface, error code: 0x%08x\n"), hres);//Specify the database instance name, where the alias local is used, specifying the native default instancedbprops[0].dwpropertyid = Dbprop_init_datasource; dbprops[0].dwoptions = dbpropoptions_required; dbprops[0].VVALUE.VT = VT_BSTR; dbprops[0].vvalue.bstrval = SysAllocString (OleStr ("LIU-PC\\SQLEXPRESS ")); dbprops[0].colid = Db_nullid;//Specify Database library namedbprops[1].dwpropertyid = Dbprop_init_catalog; dbprops[1].dwoptions = dbpropoptions_required; dbprops[1].VVALUE.VT = VT_BSTR; dbprops[1].vvalue.bstrval = SysAllocString (OleStr ("Study")); dbprops[1].colid = Db_nullid;//Specify the user name of the linked databasedbprops[2].dwpropertyid = Dbprop_auth_userid; dbprops[2].VVALUE.VT = VT_BSTR; dbprops[2].vvalue.bstrval = SysAllocString (OleStr ("SA"));//Specify the user password for the linked databasedbprops[3].dwpropertyid = Dbprop_auth_password; dbprops[3].VVALUE.VT = VT_BSTR; dbprops[3].vvalue.bstrval = SysAllocString (OleStr ("123456"));//Set Propertieshres = Pdbinit->queryinterface (Iid_idbproperties, (void* *) &pidbproperties); Grs_com_check (hres, _t ("Query IDBProperties interface failed with error code:%08x\n"), hres);    Dbpropset->guidpropertyset = Dbpropset_dbinit; dbpropset[0].cproperties =4; dbpropset[0].rgproperties = Dbprops; hres = Pidbproperties->setproperties (1, Dbpropset); Grs_com_check (hres, _t ("Setting properties failed with error code:%08x\n"), hres);//LINK Databasehres = Pdbinit->initialize (); Grs_com_check (hres, _t ("link Database failed: Error code:%08x\n"), hres);//do somethingPdbinit->uninitialize (); grs_printf (_t ("The database operation succeeded!!!!!\ n"));    Clear_up:grs_safefree (Pdbinit);    Grs_safefree (pidbproperties); CoUninitialize ();return 0;}

This is a complete executable code, and subsequent sections will not be given for repeated code.
In the above code we first find the data source object corresponding to SQL Server based on the string SQLOLEDB, then query out the IDBProperties object based on the data source object, then allocate some space to set the property and property set. Call the SetProperties function of the IDBProperties interface to set the interface for the corresponding data source object. Finally, call the Initialize link data source of the IDBInitialize interface and call the Uninitialize function to disconnect the connection.
The GUID of the Properties collection for the generic data source object is Dbpropset_dbinit, and the following attributes are the most important:

1. DBPROP_INIT_DATASOURCE: Data connection instance (specific DBMS instance name)
2. Dbprop_init_catalog: Directory name (corresponds to a specific database name in SQL Server, no meaning for Oracle)
3. Dbprop_auth_userid: User Name
4. Dbprop_auth_password: Password

We also notice that the string above calling SysAllocString's BSTR type does not call the corresponding function to release, will there be a memory leak? There's no need to worry that OLE DB has helped us free up this space when disconnected.

Using the IDBPromptInitialize interface to create a data source object

The above method is based on standard COM, although also successfully created data source connection, but not on the standard COM to do more initialization operations, resulting in some specific advanced features can not be used, So in practice is often used in the use of idbpromptinitialize and IDataInitialize way more.
IDBPromptInitialize creates a dialog box that pops up a selection of data sources for the user to select the relevant configuration information (data source/username/password, etc.) and automatically generates the Connection object based on these configurations.
Here is an example of a popup Data Source dialog box:

voidConnectsqlserverbydialog ()//link the SQL Server database by a popup dialog box{Declare_buffer ();    Declare_oledb_interface (IDBPromptInitialize);    Declare_oledb_interface (IDBInitialize);    HWND hdesktop = GetDesktopWindow (); HRESULT hres = CoCreateInstance (Clsid_datalinks, NULL, Clsctx_inproc_server, Iid_idbpromptinitialize, (void* *) &pidbpromptinitialize); Com_check_success (hres, _t ("Failed to create IDBPromptInitialize interface:%08x"), hres);//Call this function to pop up the data Source dialog boxhres = Pidbpromptinitialize->promptdatasource (NULL, Hdesktop, Dbpromptoptions_propertysheet,0, NULL, NULL, Iid_idbinitialize, (iunknown**) &pidbinitialize); Com_check_success (hres, _t ("The pop-up data Source dialog box failed:%08x\n"), hres);    hres = Pidbinitialize->initialize (); Com_check_success (hres, _t ("link Database failed:%08x\n"), hres); com_printf (_t ("Linked database succeeded\ n"));    hres = Pidbinitialize->uninitialize (); __clean_up:safe_release (pidbpromptinitialize); Safe_release (pidbinitialize);}

In addition to this approach, he can also directly create the IDBInitialize interface, using the way to set properties before connecting to the database, here is a demonstration example:

    HRESULT hRes = CoCreateInstance(CLSID_MSDAINITIALIZE, NULL, CLSCTX_INPROC_SERVER, IID_IDataInitialize, (void**)&pIDataInitialize);    COM_CHECK_SUCCESS(hRes, _T("创建接口IDBInitialize失败：%08x\n"), hRes);    hRes = CLSIDFromProgID(_T("SQLOLEDB"), &clsid);    COM_CHECK_SUCCESS(hRes, _T("查询SQLOLEDB CLSID 失败:%08x\n"), hRes);    hRes = pIDataInitialize->CreateDBInstance(clsid, NULL,        CLSCTX_INPROC_SERVER, NULL, IID_IDBInitialize,        (IUnknown**)&pIDBInitialize);    COM_CHECK_SUCCESS(hRes, _T("创建IDBInitialize接口失败:%08x\n"), hRes);    //后续的代码就是我们之前写的那段定义属性，设置属性，连接数据库的代码

Using the IDataInitialize interface to create a data source object

Using the IDataInitialize interface, you can concatenate directly to the database using the connection Word, which is an example of using the connection string:

voidConnectsqlserverbyconnstr ()//Connect to database via connection string{declare_oledb_interface (idatainitialize);    Declare_oledb_interface (IDBInitialize);    Declare_buffer (); HRESULT hres = CoCreateInstance (Clsid_msdainitialize, NULL, Clsctx_inproc_server, Iid_idatainitialize, (void* *) &pidatainitialize); Com_check_success (hres, _t ("Failed to create IDataInitialize interface:%08x!\ n"), hres); hres = Pidatainitialize->getdatasource (NULL, Clsctx_inproc_server, OleStr ("Provider=SQLOLEDB.1; Persist Security Info=false; User Id=sa; Password = 123456;initial Catalog=study;data source=liu-pc\\SQLEXPRESS; "), Iid_idbinitialize, (iunknown**) &pidbinitialize); Com_check_success (hres, _t ("Get IDBInitialize interface failed:%08x!\ n"), hres);    hres = Pidbinitialize->initialize (); Com_check_success (hres, _t ("Connection to database failed:%08x!\ n"), hres); com_printf (_t ("Connection to the database succeeded\ n"));    Pidbinitialize->uninitialize (); __clean_up:safe_release (pidatainitialize); Safe_release (pidbinitialize);}

Get connection string

In fact, in addition to the above method of directly creating the IDataInitialize interface, you can also use the IDBPromptInitialize interface to query out a idatainitialize interface, and then set the connection word concatenated to the database.
In OLE DB, it can be thought that the concatenated string is eventually translated into corresponding properties, that is to say, OLEDDB holds the corresponding properties of the connection, we can obtain different types of properties in different ways, such as using the IDBProperties interface to obtain the corresponding link properties, Or use IDataInitialize's getinitializationstring function to get a linked string of connections.
Now that it holds the corresponding properties for each connection, is it possible to save the user's actions on the Data Source dialog box as a data connection string, and the answer is yes. The following ideas are implemented:

2. Call the Promptdatasourc method of the IDBPromptInitialize interface to bring up the Data Source dialog box for the user to manipulate
4. Query out IDataInitialize interface according to IDBPromptInitialize interface

5. Call the getinitializationstring of the IDataInitialize interface to get the connection string
   The following is the implementation-specific code:

   voidGetconnectstring () {declare_oledb_interface (idbpromptinitialize);D eclare_oledb_interface (IDataInitialize);D Eclare_oledb_interface (idbinitialize);D eclare_buffer (); Lpolestr pconnstr = NULL; HWND hdesktop = GetDesktopWindow (); HRESULT hres = CoCreateInstance (Clsid_datalinks, NULL, Clsctx_inproc_server, Iid_idbpromptinitialize, (void* *) &pidbpromptinitialize); Com_check_success (HRes, _t ("Failed to create IDBPromptInitialize interface:%08x!\ n"), hres); hres = Pidbpromptinitialize->promptdatasource (NULL, Hdesktop, Dbpromptoptions_propertysheet,0, NULL, NULL, Iid_idbinitialize, (iunknown**) &pidbinitialize); Com_check_success (HRes, _t ("The pop-up data Source dialog box failed:%08x\n"), hres); hres= pidbpromptinitialize->queryinterface (Iid_idatainitialize, (void* *) &pidatainitialize); Com_check_success (HRes, _t ("Failed to create IDataInitialize interface:%08x!\ n"), hres); hres = Pidatainitialize->getinitializationstring (Pidbinitialize, TRUE, &pconnstr); COM_CHECK_SUCCESS (HRes, _t ("Failed to get connection string:%08x\n"), hres); com_printf (_t ("Connection string:%s"), PCONNSTR); SysAllocString (PCONNSTR); __clean_up:safe_release (pidatainitialize); Safe_release (pidbinitialize); Safe_release (pidbpromptinitialize);}

   In order to save space, these notes will only enumerate some of the key code, to the complete code I will slowly upload to the GitHub project with the progress of the blog content, and at the end of the blog to give the corresponding file address
   This Code address 1
   This Code address 2

OLE DB data source