Shape Commands in General
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.