MDAC 2.5 SDK - ADO


 

Shape Commands in General

See Also

Data shaping defines the columns of a shaped Recordset, the relationships between the entities represented by the columns, and the manner in which the Recordset is populated with data.

A shaped Recordset may consist of the following types of columns:

Column Type Description
data Fields from a Recordset returned by a query command to a data provider, table, or previously shaped Recordset.
chapter A reference to another Recordset, called a chapter. Chapter columns make it possible to define a parent-child relationship where the parent is the Recordset containing the chapter column and the child is the Recordset represented by the chapter.
aggregate The value of the column is derived by executing an aggregate function on all the rows, or a column of all the rows of a child Recordset. (See Aggregate Functions in the following table.)
calculated expression The value of the column is derived by calculating a Visual Basic for Applications expression on columns in the same row of the Recordset. The expression is the argument to the CALC function. (See Calculated Expression in the following table, and Visual Basic for Applications Functions.)
new Empty, fabricated fields, which may be populated with data at a later time. The column is defined with the NEW keyword. (See NEW Keyword in the following table.)

A shape command may contain a clause specifying a query command to an underlying data provider that will return a Recordset object. The query's syntax depends on the requirements of the underlying data provider. This will usually be Structured Query Language (SQL), although ADO doesn't require the use of any particular query language.

You could use an SQL JOIN clause to relate two tables; however, a hierarchical Recordset may represent the information more efficiently. Each row of a Recordset created by a JOIN repeats information redundantly from one of the tables. A hierarchical Recordset has only one parent Recordset for each of multiple child Recordset objects.

Shape commands can be issued by Recordset objects or by setting the CommandText property of the Command object and then calling the Execute method.

Shape commands can be nested. That is, the parent-command or child-command may itself be another shape command.

For information about navigating a hierarchical Recordset, see Accessing Rows in a Hierarchical Recordset.

For precise information about syntactically correct shape commands, see Formal Shape Grammar.

Aggregate Functions, the CALC Function, and the NEW Keyword

Data shaping supports the following functions. The name assigned to the chapter containing the column to be operated on is the chapter-alias.

A chapter-alias may be fully qualified, consisting of each chapter column name leading to the chapter containing the column-name, all separated by periods. For example, if the parent chapter, chap1, contains a child chapter, chap2, that has an amount column, amt, then the qualified name would be chap1.chap2.amt.

Aggregate Functions Description
SUM(chapter-alias.column-name) Calculates the sum of all values in the specified column.
AVG(chapter-alias.column-name) Calculates the average of all values in the specified column.
MAX(chapter-alias.column-name) Calculates the maximum value in the specified column.
MIN(chapter-alias.column-name) Calculates the minimum value in the specified column.
COUNT(chapter-alias[.column-name]) Counts the number of rows in the specified alias or column.
STDEV(chapter-alias.column-name) Calculates the standard deviation in the specified column.
ANY(chapter-alias.column-name) The value of a column (where the value of the column is the same for all rows).

Calculated Expression Description
CALC(expression) Calculates an arbitrary expression, but only on the row of the Recordset containing the CALC function. Any expression using these Visual Basic for Applications (VBA) Functions is allowed.

NEW Keyword Description
NEW field-type [(width | scale | precision | error [, scale | error])] Adds an empty column of the specified type to the Recordset.

The field-type passed with the NEW keyword can be any of the following data types.

OLE DB Data Types ADO Data Type Equivalent(s)
DBTYPE_BSTR adBSTR
DBTYPE_BOOL adBoolean
DBTYPE_DECIMAL adDecimal
DBTYPE_UI1 adUnsignedTinyInt
DBTYPE_I1 adTinyInt
DBTYPE_UI2 adUnsignedSmallInt
DBTYPE_UI4 adUnsignedInt
DBTYPE_I8 adBigInt
DBTYPE_UI8 adUnsignedBigInt
DBTYPE_GUID adGuid
DBTYPE_BYTES adBinary, AdVarBinary, adLongVarBinary
DBTYPE_STR adChar, adVarChar, adLongVarChar
DBTYPE_WSTR adWChar, adVarWChar, adLongVarWChar
DBTYPE_NUMERIC adNumeric
DBTYPE_DBDATE adDBDate
DBTYPE_DBTIME adDBTime
DBTYPE_DBTIMESTAMP adDBTimeStamp
DBTYPE_VARNUMERIC adVarNumeric
DBTYPE_FILETIME adFileTime
DBTYPE_ERROR adError

When the new field is of type decimal (in OLE DB, DBTYPE_DECIMAL or, in ADO, adDecimal), you must specify the precision and scale values.

Issuing Commands to the Underlying Data Provider

Any command that does not begin with SHAPE is passed through to the data provider. This is equivalent to issuing a shape command of the form "SHAPE {provider command}". These commands do not have to produce a Recordset. For instance, "SHAPE {DROP TABLE MyTable} is a perfectly valid shape command, assuming the data provider supports DROP TABLE.

This capability allows both normal provider commands and shape commands to share the same connection and transaction.