Export (0) Print
Expand All

db_command

Creates an OLE DB command.

[ db_command( 
   db_command, 
   name, 
   source_name, 
   hresult, 
   bindings, 
   bulk_fetch ) 
   {command string}
]

Parameters

db_command
A command string containing the text of an OLE DB command. There are two ways to specify the command.

The first way, appropriate for simpler commands, is to specify the command string as the value for the db_command argument:

[ db_command ( command = "Select * from Products" ) ]

The second way, appropriate for lengthy commands, or for commands that use binding parameters, is to specify the command string in braces after the arguments. See the bindings parameter for this usage.

name (optional)
The name of the handle you use to work with the rowset. If you specify name, db_command generates a class with the specified name, which can be used to traverse the rowset or to execute multiple action queries. If you do not specify name, it will not be possible to return more than one row of results to the user.
source_name (optional)
The CSession variable or instance of a class that has the db_source attribute applied to it on which the command executes. See db_source.
hresult (optional)
Identifies the variable that will receive the HRESULT of this database command. If the variable does not exist, it will be automatically injected by the attribute.
bindings (optional)
Allows you to separate the binding parameters from the OLE DB command.

If you specify a value for bindings, db_command will parse the associated value and will not parse the [bindtype] parameter. This usage allows you to use OLE DB provider syntax. To disable parsing, without binding parameters, specify Bindings="".

If you do not specify a value for bindings, db_command will parse the binding parameter block, looking for '(', followed by [bindtype] in brackets, followed by one or more previously declared C++ member variables, followed by ')'. All text between the parentheses will be stripped from the resulting command, and these parameters will be used to construct column and parameter bindings for this command.

bulk_fetch (optional)
An integer value that specifies the number of rows to fetch.

The default value is 1, which specifies single row fetching (the rowset will be of type CRowset).

A value greater than 1 specifies bulk row fetching. Bulk row fetching refers to the ability of bulk rowsets to fetch multiple row handles (the rowset will be of type CBulkRowset and will call SetRows with the specified number of rows).

If bulk_fetch is less than one, SetRows will return zero.

command string
The command string is enclosed in braces '{ }' and the syntax is as follows:
binding parameter block 1
   OLE DB command
binding parameter block 2
   continuation of OLE DB command
binding parameter block 3
...

A binding parameter block is defined as follows:

([bindtype] szVar1 [, szVar2 [, nVar3 [, ...]]] )

where:

( marks the start of the data binding block.

[bindtype] is one of the following case-insensitive strings:

  • [db_column] binds each of the member variables to a column in a rowset.
  • [bindto] (same as [db_column]).
  • [in] binds member variables as input parameters.
  • [out] binds member variables as output parameters.
  • [in,out] binds member variables as input/output parameters.

SzVarX resolves to a member variable within the current scope.

) marks the end of the data binding block.

If the command string contains one or more specifiers such as [in], [out], or [in/out], db_command builds a parameter map.

If the command string contains one or more parameters such as [db_column] or [bindto], db_command generates a rowset and an accessor map to service these bound variables. See db_accessor for more information.

Note   [bindtype] syntax and the bindings parameter are not valid when using db_command at the class level.

Here are some examples of binding parameter blocks. The following example binds the m_au_fname and m_au_lname data members to the au_fname and au_lname columns, respectively, of the authors table in the pubs database:

TCHAR m_au_fname[21];
TCHAR m_au_lname[41];
TCHAR m_state[3] = 'CA';

[db_command {
   SELECT au_fname([bindto]m_au_fname), au_lname([bindto]m_au_lname)
   FROM dbo.authors
   WHERE state = ?([in]m_state)
}]

This example does essentially the same binding as the previous code, but with a different syntax:

TCHAR m_au_fname[21];
TCHAR m_au_lname[41];
TCHAR m_state[3] = 'CA';

[db_command (bindings="([bindto]m_au_fname, m_au_lname); ([in]m_state)")
{ 
   SELECT au_fname, au_lname
   FROM dbo.authors
   WHERE state = ?
}]

Attribute Context

Applies to class, struct, member, method, local
Repeatable No
Required attributes None
Invalid attributes None

For more information about the attribute contexts, see Attribute Contexts.

Remarks

db_command creates a CCommand object, which is used by an OLE DB consumer to execute a command.

You can use db_command with either class or function scope; the main difference is the scope of the CCommand object. With function scope, data such as bindings terminate at function end. Both class and function scope usages involve the OLE DB Consumer Template class CCommand<>, but the template arguments differ for the function and class cases. In the function case, bindings will be made to an Accessor that comprises local variables, while the class usage will infer a CAccessor-derived class as the argument. When used as a class attribute, db_command works in conjunction with db_column.

You can use multiple commands with the same data source; see the last two examples in the Examples section.

db_command can be used to execute commands that do not return a result set.

Examples

Example 1 is code that can be copied into a Visual C++ .NET project, built, and run. Examples 2 and 3 are partial code examples that demonstrate usage and are not intended to be copied as is into an application.

Example 1 defines a command that selects the first and last names from a table where the state column matches 'CA'. db_command creates and reads a rowset on which you can call wizard-generated functions such as OpenAll and CloseAll, as well as CRowset member functions such as MoveNext.

To create an application using the code in Example 1, create a Win32 Project; on the Application Settings page, select Console application and check Add support for ATL. You will need to add the following preprocessor directives to support attributes:

  • #define _ATL_ATTRIBUTES in stdafx.h, before #include <atlbase.h>.
  • #include <atldbcli.h> in stdafx.h, after #include <atlbase.h>.

Add a header file called Authors.h to the project, and copy and paste the following code, which declares the command class CAuthors.

Note that this code requires you to provide your own connection string that connects to the pubs database. A convenient way to obtain a connection string is to right-click on Data Connections in Server Explorer and select Add Connection from the drop-down menu; fill out the Data Link Properties dialog and click OK. Left-click on the new data connection when it appears (as a subnode on Data Connections); in the Properties window, copy the connection string from the ConnectString field (you can double-click to highlight the entire string).

/* authors.h : Declaration of the CAuthors class */
#pragma once

[
   db_source(L"your connection string"),   // Provide a connection to the pubs database
   db_command(L" \
      SELECT au_lname, au_fname \
      FROM dbo.authors \
      WHERE state = 'CA'")
]

class CAuthors
{
public:

   // In order to fix several issues with some providers, the code below may bind
   // columns in a different order than reported by the provider

   [ db_column(L"au_lname", status=m_dwau_lnameStatus, length=m_dwau_lnameLength) ] TCHAR m_au_lname[41];
   [ db_column(L"au_fname", status=m_dwau_fnameStatus, length=m_dwau_fnameLength) ] TCHAR m_au_fname[21];
   [ db_param(7, DBPARAMIO_INPUT) ] TCHAR m_state[3];
   
   DBSTATUS m_dwau_lnameStatus;
   DBSTATUS m_dwau_fnameStatus;

   DBLENGTH m_dwau_lnameLength;
   DBLENGTH m_dwau_fnameLength;

   void GetRowsetProperties(CDBPropSet* pPropSet)
   {
      pPropSet->AddProperty(DBPROP_CANFETCHBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
      pPropSet->AddProperty(DBPROP_CANSCROLLBACKWARDS, true, DBPROPOPTIONS_OPTIONAL);
   }
};

Next, copy and paste the code indicated as follows into the project's .cpp file:

#include "stdafx.h"
#include "Authors.h"

int _tmain(int argc, _TCHAR* argv[])
{
   HRESULT hr = CoInitialize(NULL);

   // Instantiate rowset
   CAuthors rs;

   // Open rowset and move to first row
   _tcscpy(rs.m_state, _T("CA"));
   hr = rs.OpenAll();
   hr = rs.MoveFirst();
   
   // Iterate through the rowset
   while( SUCCEEDED(hr) && hr != DB_S_ENDOFROWSET )
   {
      // Print out the column information for each row
      printf("First Name: %s, Last Name: %s\n",
            rs.m_au_fname, rs.m_au_lname);
      hr = rs.MoveNext();
   }
   
   rs.CloseAll();
   
   CoUninitialize();

   return 0;
}

Example 2 uses db_source on a data source class CMySource, and db_command on command classes CCommand1 and CCommand2:

// Example 4: class usage for both db_source and db_command
[db_source(...)]
class CMySource
{...};
[db_command(command = "SELECT * FROM Products")]
class CCommand1
{...};
[db_command(command = "SELECT FNAME, LNAME FROM Customers")]
class CCommand2
{...};
...
// Usage:
CMySource s;
HRESULT hr = s.OpenDataSource();
if (SUCCEEDED(hr))
{
   CCommand1 c1;
   hr = c1.Open(s);
   ...
   CCommand2 c2;
   hr = c2.Open(s);
   ...
}
s.CloseDataSource();

Example 3 uses db_source on a data source class CMySource, and db_command inline to create two separate commands:

// Example 5: class usage for db_source and inline usage for db_command
[db_source(...)]
class CMySource
{...};
...
// Usage:
HRESULT SomeFunc()
{
   CMySource s;
   HRESULT hr = s.OpenDataSource();
   if (SUCCEEDED(hr))
   {
      ...
      [ db_command(command = "SELECT * FROM Products",...,source_name="s",...) ];
      [ db_command(command = " SELECT FNAME, LNAME from Customers",...,source_name="s",...) ];
      ...
   }
   s.CloseDataSource();
   ...
}

For other examples, see the MantaWeb Sample and the OnlineAddressBook Sample.

See Also

OLE DB Consumer Attributes | Stand-Alone Attributes | Attributes Samples

Show:
© 2014 Microsoft