SQL statement execution vs. result set acquisition

Title:sql statement execution vs. result set acquisition
Tags: [OLE DB, database programming, VC + +, database]
Date:2018-01-28 09:22:10
Categories:windows Database Programming
Keywords:oledb, database programming, VC + +, database, execute SQL, get result set
---
The last time I talked about command objects is to execute SQL statements. The data source returns a result set object after the SQL statement is executed, returns the results of the SQL execution to the result set object, and after the application executes the SQL statement, resolves the results in the result set object, and the main content of this time is how to parse the result set object and get the values in it.

How to execute SQL statements

The general steps to execute the SQL statement are as follows:

1. Create the ICommandText interface.
2. To set the SQL command using the SetCommandText method of the ICommandText interface
3. Executes the SQL statement using the Excute method of the ICommandText interface and accepts the returned result set object, which is generally irowset.

In fact, OLE DB does not have to pass in the SQL statement, he can pass in simple commands, as long as the data source can be identified, that is, we can according to the different data sources to the specific data source will be supported by the command, has achieved the simplification of operations or the purpose of implementing some specific functions.
For some SQL statements, we are not so concerned about it returning the data, such as the DELETE statement, insert statement, for this case we can set the corresponding return result set parameters to NULL, such as the following

pICommandText->Execute(NULL, IID_NULL, NULL, NULL, NULL)

It is clear that the data source program does not need to return a result set, so that the data source does not prepare the result set, reduces the data source's related operations, and in some way alleviates the burden of the data source.

Set the Command object's properties

Unlike the properties of the previous data source object and the Session object, the command object's properties are on the returned data source object, such as when we do not set the corresponding Update property, and the data source does not allow us to use the result set to update the data. These properties must be defined before the execution of the SQL statement to get the result set operation. Because the data source has set the corresponding property when it obtains the result set returned by the data source.
The property set ID of the command object is Propset_rowset. There are many properties in this property set that can affect the result set object.
Here is an example of an Execute SQL statement:

Lpolestr lpsql = OLESTR ("SELECT * from Aa26");    Createdbsession (Piopenrowset); HRESULT hres = Piopenrowset->queryinterface (Iid_idbcreatecommand, (void* *) &pidbcreatecommand); Com_success (hres, _t ("Query interface IDBCreateCommand failed with error code:%08x\n"), hres);    hres = Pidbcreatecommand->createcommand (NULL, Iid_icommandtext, (iunknown**) &picommandtext); Com_success (hres, _t ("Create interface IDBCreateCommand failed with error code:%08x\n"), hres);    hres = Picommandtext->setcommandtext (Dbguid_default, lpsql); Com_success (hres, _t ("failed to set SQL statement, error code:%08x\n"), hres); Dbprop dbprop[ -] = {0}; Dbpropset dbpropset[1] = {0};//Set result set can be used for pruning operationsdbprop[0].colid = Db_nullid; dbprop[0].dwoptions = dbpropoptions_required; dbprop[0].dwpropertyid = dbprop_updatability; dbprop[0].VVALUE.VT = VT_I4; dbprop[0].vvalue.lval = Dbpropval_up_delete | Dbpropval_up_change | Dbpropval_up_insert;//Request to open bookmark functiondbprop[1].colid = Db_nullid; dbprop[1].dwoptions = dbpropoptions_optional; dbprop[1].dwpropertyid = Dbprop_bookmarks; dbprop[1].VVALUE.VT = Vt_bool; dbprop[1].vvalue.boolval = VARIANT_TRUE;//apply to open Line lookup functiondbprop[2].colid = Db_nullid; dbprop[2].dwoptions = dbpropoptions_optional; dbprop[2].dwpropertyid = Dbprop_irowsetfind; dbprop[2].VVALUE.VT = Vt_bool; dbprop[2].vvalue.boolval = VARIANT_TRUE;//apply to open row indexdbprop[3].colid = Db_nullid; dbprop[3].dwoptions = dbpropoptions_optional; dbprop[3].dwpropertyid = Dbprop_irowsetindex; dbprop[3].VVALUE.VT = Vt_bool; dbprop[3].vvalue.boolval = VARIANT_TRUE;//apply to open line positioning functiondbprop[4].colid = Db_nullid; dbprop[4].dwoptions = dbpropoptions_optional; dbprop[4].dwpropertyid = DBPROP_IRowsetLocate; dbprop[4].VVALUE.VT = Vt_bool; dbprop[4].vvalue.boolval = VARIANT_TRUE;//apply to open line scrolling functiondbprop[5].colid = Db_nullid; dbprop[5].dwoptions = dbpropoptions_optional; dbprop[5].dwpropertyid = Dbprop_irowsetscroll; dbprop[5].VVALUE.VT = Vt_bool; dbprop[5].vvalue.boolval = VARIANT_TRUE;//apply to open the rowset view featuredbprop[6].colid = Db_nullid; dbprop[6].dwoptions = dbpropoptions_optional; dbprop[6].dwpropertyid = Dbprop_irowsetview; dbprop[6].VVALUE.VT = Vt_bool; dbprop[6].vvalue.boolval = VARIANT_TRUE;//Apply to open rowset Refresh featuredbprop[7].colid = Db_nullid; dbprop[7].dwoptions = dbpropoptions_optional; dbprop[7].dwpropertyid = Dbprop_irowsetrefresh; dbprop[7].VVALUE.VT = Vt_bool; dbprop[7].vvalue.boolval = VARIANT_TRUE;//Request to open the column information extension interfacedbprop[8].colid = Db_nullid; dbprop[8].dwoptions = dbpropoptions_optional; dbprop[8].dwpropertyid = Dbprop_icolumnsinfo2; dbprop[8].VVALUE.VT = Vt_bool; dbprop[8].vvalue.boolval = VARIANT_TRUE;//Request to open the Database synchronization state interfacedbprop[9].colid = Db_nullid; dbprop[9].dwoptions = dbpropoptions_optional; dbprop[9].dwpropertyid = Dbprop_idbasynchstatus; dbprop[9].VVALUE.VT = Vt_bool; dbprop[9].vvalue.boolval = VARIANT_TRUE;//Apply open Rowset Chapter functiondbprop[Ten].colid = Db_nullid; dbprop[Ten].dwoptions = dbpropoptions_optional; dbprop[Ten].dwpropertyid = Dbprop_ichapteredrowset; dbprop[Ten].VVALUE.VT = Vt_bool; dbprop[Ten].vvalue.boolval = VARIANT_TRUE; dbprop[ One].colid = Db_nullid; dbprop[ One].dwoptions = dbpropoptions_optional; dbprop[ One].dwpropertyid = Dbprop_irowsetcurrentindex; dbprop[ One].VVALUE.VT = Vt_bool; dbprop[ One].vvalue.boolval = VARIANT_TRUE; dbprop[ A].colid = Db_nullid; dbprop[ A].dwoptions = dbpropoptions_optional; dbprop[ A].dwpropertyid = Dbprop_igetrow; dbprop[ A].VVALUE.VT = Vt_bool; dbprop[ A].vvalue.boolval = VARIANT_TRUE;//apply to open Rowset Update featuredbprop[ -].colid = Db_nullid; dbprop[ -].dwoptions = dbpropoptions_optional; dbprop[ -].dwpropertyid = DBPROP_IRowsetUpdate; dbprop[ -].VVALUE.VT = Vt_bool; dbprop[ -].vvalue.boolval = VARIANT_TRUE; dbprop[ -].colid = Db_nullid; dbprop[ -].dwoptions = dbpropoptions_optional; dbprop[ -].dwpropertyid = Dbprop_iconnectionpointcontainer; dbprop[ -].VVALUE.VT = Vt_bool; dbprop[ -].vvalue.boolval = VARIANT_TRUE; dbpropset[0].cproperties = the; dbpropset[0].guidpropertyset = Dbpropset_rowset; dbpropset[0].rgproperties = Dbprop; hres = Picommandtext->queryinterface (Iid_icommandproperties, (void* *) &picommandproperties); Com_success (hres, _t ("Query interface ICommandProperties failed with error code:%08x\n"), hres); hres = Picommandproperties->setproperties (1, Dbpropset); Com_success (hres, _t ("Setting properties failed with error code:%08x\n"), hres);    hres = Picommandtext->execute (null, iid_irowset, NULL, NULL, (iunknown**) &pirowset); Com_success (hres, _t ("Execute SQL statement failed with error code:%08x\n"), hres);

This code shows in detail how to execute the SQL statement to get the result set and set the properties of the Commandui object.

Result set Object

The result set is typically an object that is returned after the SQL statement is executed to represent a two-dimensional structured array. This structured object can be understood as a struct that is the same as a data table definition. And the result set holds the pointer to the struct.
The following is a detailed definition of the result set object

CoType TRowset {[mandatory] interface IAccessor;   [Mandatory] interface IColumnsInfo;   [Mandatory] interface IConvertType;   [Mandatory] interface IRowset;   [Mandatory] interface IRowsetInfo;   [optional] interface ichapteredrowset;   [optional] interface IColumnsInfo2;   [optional] interface IColumnsRowset;   [optional] interface IConnectionPointContainer;   [optional] interface idbasynchstatus;   [optional] interface Igetrow;   [optional] interface IRowsetChange;   [optional] interface irowsetchaptermember;   [optional] interface Irowsetcurrentindex;   [optional] interface irowsetfind;   [optional] interface irowsetidentity;   [optional] interface IRowsetIndex;   [optional] interface irowsetlocate;   [optional] interface Irowsetrefresh;   [optional] interface IRowsetScroll;   [optional] interface irowsetupdate;   [optional] interface Irowsetview;   [optional] interface ISupportErrorInfo; [Optional] Interface IRowsetbookmark;} 

General usage of result set objects

After the result set is obtained, the following steps are generally used:

1. First, query out the IColumnsInfo interface
2. Get detailed information about the result set's columns by calling the IColumnsInfo::GetColumnInfo method an array of dbcolumninfo structures, including: column ordinal, column name, type, byte length, precision, scale, etc.
   3. Through the array of structures, prepare a corresponding array of dbbinding structures, calculate the buffer size that is actually needed for each row of data, and populate the structure dbbinding. This process is generally called binding
   4. Create a data accessor using the dbbinding array and the IAccessor::CreateAccessor method and get a handle haccessor
3. Call Irowset::getnextrow to iterate through the row pointer to the next line, the first call is to point to the first row, and get the row handle Hrow, the row handle indicates that we are currently accessing the result of the current row, the general value of which is a sequential increment of the integer
4. Call IRowset::GetData to pass in the prepared row buffer memory pointer, as well as the previously created accessor haccessor handle and hrow handle. The final row data is placed in the specified buffer. Loops call GetNextRow and GetData to traverse the entire two-dimensional result set.

Access to column information

When the result set object is obtained, the immediate operation is to get the structure information of the result set, that is, to get the column information of the result set (called field information in some materials) to get the column information, we need to QueryInterface out the IColumnsInfo interface of the result set object. and call the IColumnsInfo::GetColumnInfo method to get an array called dbcolumninfo struct that reflects the logical structure information (abstract data type) and the physical structure information (memory requirement size, etc.) of the column in the struct
The function getcolumninfo is defined as follows:

HRESULT GetColumnInfo (   DBORDINAL     *pcColumns,   DBCOLUMNINFO **prgInfo,   OLECHAR      **ppStringsBuffer);

The first parameter represents the total number of columns, the second argument is a dbcolumninfo, returns an array pointer to the column information, and the third parameter returns a string pointer that holds the name of a column, separated by \0\0 for each name. But we generally do not use it to get the column names, we generally use the pwszname members of the DBCOLUMNINFO structure.
Dbcolumninfo is defined as follows:

typedefstruct tagDBCOLUMNINFO {   LPOLESTR        //列名   ITypeInfo      //列的类型信息   DBORDINAL       //列序号   DBCOLUMNFLAGS   //列的相关标识   DBLENGTH        //列最大可能的大小，对于字符串来说，它表示的是字符个数   DBTYPE          //列类型   BYTE            //精度(它表示小数点后面的位数)   BYTE            //表示该列的比例，目前没有用处，一般给的是0   DBID            //列信息在数据字典表中存储的ID} DBCOLUMNINFO;

For ColumnID members, DBMS systems typically have multiple system tables that represent a multitude of information, such as user information, database information, data table information, and so on, where the DBMS system uses a specific system table to store information about the columns in each table. This ColumnID value is used when querying this system table for column information.

Data binding

The general binding takes two steps, 1 is the DBCOLUMNINFO structure that gets the column information, and then populates the dbbinding data structure based on the column information.
Sometimes it may feel bound trouble ah, it is better to directly return a buffer, all the results into the inside, the application according to the needs of their own to parse it, so it is not more convenient. There is one reason why binding is required:

1. Not all data types can be supported by the application, such as the number type in the database cannot find the corresponding data structure to support VC + +.
2. Sometimes a row of data is not fully read into memory, for example, we do not give enough buffer or the data in the database itself is relatively large, such as the storage of a video file and so on.
3. Not all accessors in the program are meant to read the data, and it's too simple to use the way to return all the results, like I just want a column of data that might take up less than 1K of memory, but a column of data in a database table is particularly large and may consume more than one g of memory. It's a waste of memory if it's all back. So in the binding time can be flexibly specified to return the data, the length of the return data, for particularly large data, we can specify that it only returns the part, such as only the previous 1K
4. Use bindings to flexibly arrange the return data in memory.

The binding structure is defined as follows:

typedefstruct tagDBBINDING{    //列号    DBBYTEOFFSET obValue;    DBBYTEOFFSET obLength;    DBBYTEOFFSET obStatus;    ITypeInfo *pTypeInfo;     DBOBJECT *pObject;    DBBINDEXT *pBindExt;    DBPART dwPart;    DBMEMOWNER dwMemOwner;    DBPARAMIO eParamIO;    //数据的最大长度，一般给我们为这个列的数据准备的缓冲的长度    DWORD dwFlags;    //将该列转化为何种数据类型展示    BYTE bPrecision;    BYTE bScale;}DBBINDING;

Detailed description of the parameter:

2. Obvalue, Oblength, Obstatus: When the data source returns the result, it usually returns the three data, data length, data state, data value of the column information. The data state indicates that the data source provides a state information for the data, such as when the column information is empty, it returns a DBSTATUS_S_ISNULL, the column data is longer, and the supplied data may not be sufficient, and this time returns a status indicating that a truncation has occurred. The length of the data indicates the length of the returned result. This value is the byte length corresponding to the data returned, note that this needs to be distinguished from the previous ulcolumnsize. The three data are placed in memory in the following order:
   
   Each column of data is arranged according to the status length value structure, and the data of different columns is discharged sequentially, in order, and the layout of the memory is somewhat like the layout of the structure array in memory. While the Obvalue, Oblength, and obstatus in the binding structure specify the offset of the three in a memory buffer, note that the beginning of the next column is at the end of the previous column and not all data starts at 0.
4. Dwpart: There are 3 parts in the data source return result, but we can specify which parts of the 3 parts The data source returns, and its value is a number of flags that determine what data needs to be returned and what data is not returned. Its main values are: Dbpart_length, Dbpart_status, Dbpart_value;
6. Dwmemowner, this value indicates what memory buffers will be used to save the results returned by the data source, and we generally use dbmemowner_clientowned, which indicates the way in which user-defined memory is used, that is, the buffer needs to be prepared on its own.
8. Eparamio: We will return the value for what purpose, dbparamio_notparam means not to do special purposes, dbparamio_input, as input values, generally in the need to update column data when the use of this flag, Dbparamio_output, As the output value, this output is relative to the data source, representing the output to the application program buffer, as a display.

10. Wtype: What kind of conversion is made to the original data in the data source, such as 123456 of the integer stored in the original database, and this value is DBTYPE_WSTR, the result in the data source is converted to the "123456" of the string and put into the buffer.

    Comparison between dbbinding and dbcolumnsinfo structure They have many data members that are the same, and the meanings of the representations are basically the same, but they also have significant differences:
12. Dbcolumninfo is the data provider to the user information, it is fixed, for the same query, the column is always the same, so the data provider returns the The dbcolumninfo array is also fixed. Whereas dbbinding is an array of structures that are given to the data provider after the data consumer has been created, its contents are fully controlled by the caller, and through this structure you can specify that the data provider will eventually place the data in the format specified by the caller and make the specified data type conversions. For the same Query we can specify a different dbbindings structure.

13. Dbcolumninfo reflects the original column structure information of the two-dimensional result set, and dbbinding reflects the fact that the two-dimensional result set data is eventually placed in memory as required.
    The following is an allusion to a binding:
    "' CPP
    Execsql (Piopenrowset, Pirowset);

    Creating the IColumnsInfo Interface
    HRESULT hres = Pirowset->queryinterface (Iid_icolumnsinfo, (void**) &picolumnsinfo);
    Com_success (hres, _t ("Query interface Icomclumnsinfo, error code:%08x\n"), hres);

    Get more information about a result set
    hres = Picolumnsinfo->getcolumninfo (&cclumns, &rgcolumninfo, &lpclumnsname);
    Com_success (hres, _t ("Get column information failed, error code:%08x\n"), hres);

    //bind
    Pdbbindings = (dbbinding ) MALLOC (sizeof (dbbinding) cclumns);
    for (int iRow = 0; IRow < cclumns; irow++)
    {
    Pdbbindings[irow].bprecision = rgcolumninfo[irow].bprecision;
    Pdbbindings[irow].bscale = Rgcolumninfo[irow].bscale;
    Pdbbindings[irow].cbmaxlen = rgcolumninfo[irow].ulcolumnsize * sizeof (WCHAR);
    if (Rgcolumninfo[irow].wtype = = DBTYPE_I4)
    {
    //The length of the administrative unit in the database is 6 bits maximum
    Pdbbindings[irow].cbmaxlen = 7 * sizeof ( WCHAR);
    }
    Pdbbindings[irow].dwmemowner = dbmemowner_clientowned;
    Pdbbindings[irow].dwpart = Dbpart_length | Dbpart_status | Dbpart_value;
    Pdbbindings[irow].eparamio = Dbparamio_notparam;
    Pdbbindings[irow].iordinal = rgcolumninfo[irow].iordinal;
    Pdbbindings[irow].obstatus = DwOffset;
    Pdbbindings[irow].oblength = dwOffset + sizeof (dbstatus);
    Pdbbindings[irow].obvalue = dwOffset + sizeof (dbstatus) + sizeof (ULONG);
    Pdbbindings[irow].wtype = dbtype_wstr;

    dwOffset = dwOffset + sizeof(DBSTATUS) + sizeof(ULONG) + pDBBindings[iRow].cbMaxLen * sizeof(WCHAR);dwOffset = COM_ROUNDUP(dwOffset); //进行内存对齐

    }
    Creating accessors
    hres = Pirowset->queryinterface (Iid_iaccessor, (void**) &piaccessor);
    Com_success (hres, _t ("Query IAccessor interface failure error code:%08x\n"), hres);
    hres = Piaccessor->createaccessor (Dbaccessor_rowdata, Cclumns, pdbbindings, 0, &haccessor, NULL);
    Com_success (hres, _t ("Create Accessor failure error code:%08x\n"), hres);

    Output Column name information
    Displaycolumnname (Rgcolumninfo, cclumns);
    allocating the corresponding memory
    PData = MALLOC (DwOffset * cRows);
    while (TRUE)
    {
    hres = pirowset->getnextrows (db_null_hchapter, 0, CRows, &urowsobtained, &phrows);
    if (hres! = S_OK && urowsobtained! = 0)
    {
    Break
    }
    ZeroMemory (PData, DwOffset * cRows);
    Show data
    for (int i = 0; i < urowsobtained; i++)
    {
    Pcurrdata = (BYTE) PData + dwOffset i;
    Pirowset->getdata (Phrows[i], haccessor, pcurrdata);
    Displaydata (Pdbbindings, Cclumns, Pcurrdata);
    }

    //清理hRowspIRowset->ReleaseRows(uRowsObtained, phRows, NULL, NULL, NULL);CoTaskMemFree(phRows);phRows = NULL;

    }

Show Column names
void Displaycolumnname (Dbcolumninfo *pdbcolumninfo, Dbcountitem idbcount)
{
Com_declare_buffer ();
for (int iColumn = 0; iColumn < idbcount; icolumn++)
{
Com_clear_buffer ();
TCHAR wszcolumnname[max_display_size + 1] =_t ("");
size_t dwsize = 0;
Stringcchlength (Pdbcolumninfo[icolumn].pwszname, Max_display_size, &dwsize);
dwsize = min (dwsize, max_display_size);
StringCchCopy (Wszcolumnname, Max_display_size, pdbcolumninfo[icolumn].pwszname);

    COM_PRINTF(wszColumnName);    COM_PRINTF(_T("\t"))}COM_PRINTF(_T("\n"));

}

Show data
void Displaydata (dbbinding pdbbindings, Dbcountitem idbcolcnt, void pData)
{
Com_declare_buffer ();
for (int i = 0; i < idbcolcnt; i++)
{
Com_clear_buffer ();
Dbstatus status = (Dbstatus) ((pbyte) PData + pdbbindings[i].obstatus);
ULONG usize = ((ULONG) ((pbyte) PData + pdbbindings[i].oblength)/sizeof (WCHAR);
Pwstr Pcurr = (pwstr) ((pbyte) PData + pdbbindings[i].obvalue);
Switch (status)
{
Case DBSTATUS_S_OK:
Case dbstatus_s_truncated:
com_printf (_t ("%s\t"), Pcurr);
Break
Case DBSTATUS_S_ISNULL:
com_printf (_t ("%s\t"), _t ("(null)"));
Break
Default
Break
}
}

COM_PRINTF(_T("\n"));

}
```
After using the previous Sake method to set the corresponding property and execute the SQL statement, get a result set, then call the corresponding query method, get a Picolumnsinfo interface, and then call the GetColumnsInfo method of the interface, get the concrete information of the structure.
The most important thing to note is the binding part of the code, depending on the number of columns returned, we define a corresponding binding structure of the array, assign each assignment, assign value when a DWOFFSET structure is defined to record the current use of memory, so that each time after the loop execution, Its position is always at the end of the last column information buffer, so that we can easily offset the calculation.
After the binding is complete, the value of this dwoffset is the total amount of memory used by all columns, so it is later used to allocate a corresponding length of memory. It then loops through the GetNextRows, GetData methods to get the data for each row and column in turn. Finally, the corresponding function is called to display, so that the data read operation is completed.

Finally, I found that the code snippet on the code cloud is simply for saving the usual example code, so the code behind it will no longer be updated on GitHub and switched to the code cloud.
Source Code View

SQL statement execution vs. result set acquisition