CÓMO: Utilizar el comando SHAPE de ADO

Exención de responsabilidades de contenido KB retirado

Este artículo se refiere a productos para los que Microsoft ya no ofrece soporte técnico. Por tanto, el presente artículo se ofrece "tal cual" y no será actualizado.

Resumen

En este artículo se describe la sintaxis del comando SHAPE de ADO para crear conjuntos de registros jerárquicos (recordsets) y se explica cómo recorrerlos. También se incluyen ejemplos de código en Visual Basic para Aplicaciones (VBA).

Más información

Puede utilizar los conjuntos de registros jerárquicos como alternativa a la sintaxis de JOIN y GROUP BY cuando necesite tener acceso a información de elementos primarios o secundarios, y de resumen.


Los conjuntos de registros jerárquicos se utilizan en muchos productos. Por ejemplo, los productos de Xbase utilizan el comando SET RELATION y Access usa "tablas virtuales segmentadas" de manera interna en los informes con niveles de agrupación. Las jerarquías le permiten crear uno o varios conjuntos de registros, definir agrupaciones y especificar cálculos totales en conjuntos de registros secundarios. Aunque podría implementar funciones similares mediante código, esta funcionalidad libera al programador de gran parte del trabajo más rutinario y lo traslada al sistema.


Los conjuntos de registros jerárquicos están disponibles a través del proveedor MSDataShape, que implementa el motor de cursores del cliente.


Los conjuntos de registros jerárquicos se diferencian de las instrucciones SQL JOIN y GROUP BY en que con una instrucción JOIN, tanto los campos de la tabla primaria como los de la secundaria se representan en el mismo conjunto de registros. En un conjunto de registros jerárquico, el conjunto de registros solamente contiene campos de la tabla primaria. Además, contiene un campo adicional que representa los datos secundarios relacionados, que puede asignar a una segunda variable de conjunto de registros y que se puede recorrer.


Si realiza funciones de agregado con GROUP BY y operadores de agregado, en el conjunto de registros sólo aparecerán valores de agregado. En los conjuntos de registros jerárquicos, los valores de agregado se representan en el conjunto de registros primario y los registros con los detalles se encuentran en el conjunto de registros secundario.


Puede crear tres tipos de instrucciones SHAPE, cada uno con sus propias ventajas e inconvenientes. Tiene que elegir el que más se ajuste a las necesidades de su aplicación y al entorno en el que se va a ejecutar. Éstos son los tipos de SHAPE:
  • Basado en relaciones
  • Basado en parámetros
  • Basado en grupos
Los dos primeros son similares porque ambos crean una jerarquía que, de otra manera, representaría una instrucción JOIN de SQL. Pero se diferencian en que todos los registros primarios y secundarios se leen en una caché local antes de que cualquier proceso continúe en la jerarquía basada en relaciones. Este tipo de jerarquía produce una sobrecarga de trabajo inicial muy elevada al recuperar los registros, pero disminuye después de esa operación.


Al principio, las jerarquías basadas en parámetros sólo leen los registros primarios y buscan los registros secundarios cuando se solicita. Aunque la sobrecarga inicial se reduce, debe emitir una nueva consulta secundaria por cada registro primario al que se tenga acceso y debe mantener la conexión con el origen de datos mientras el conjunto de registros esté abierto.


La jerarquía basada en grupos es equivalente a crear una instrucción SQL de agregado junto a una instrucción SQL detallada o a realizar funciones de agregado en datos sin normalizar. No puede actualizar las columnas de resumen ni las calculadas porque es posible que procedan de más de un registro. Al igual que las jerarquías basadas en relaciones, todos los registros se deben leer en primer lugar.


Para que los conjuntos de registros jerárquicos estén disponibles, hay que utilizar la cláusula SHAPE. Primero se explica la sintaxis simplificada y luego se incluyen ejemplos con diagramas. Dado que la sintaxis de SHAPE puede llegar a ser muy compleja, al final del artículo se incluye su gramática formal para que pueda ampliar los ejemplos. También puede usar el programa especificado al final de este artículo para probar sus propias instrucciones SHAPE. En los ejemplos se usan tablas de la base de datos de ejemplo Neptuno.

Sintaxis simplificada

 SHAPE {instrucción-primaria}
APPEND Agregado
| ({instrucción-secundaria} [As Alias]
RELATE campo-primario TO campo-secundario | marcador-parámetros
[, campo-primario TO campo-secundario | marcador-parámetros ...])
[, Agregado | ({instrucción secundaria})...]

SHAPE {instrucción-no-normalizada} [AS Alias]
COMPUTE Agregado
| Alias
| ({instrucción-secundaria} [As Alias] RELATE campo-primario TO
campo-secundario | marcador-parámetros)
[, Agregado | Alias | ({instrucción-secundaria}...)]
[BY campo-agrupación [, campo-agrupación]]

SHAPE {instrucción-no-normalizada} [AS Alias]
BY campo-agrupación [, campo-agrupación]

NOTAS:

  1. Si selecciona campos de diferentes tablas que tienen nombres idénticos, puede que tenga que crear alias para asegurarse de que el analizador sintáctico de SHAPE funciona.
  2. La instrucción SHAPE APPEND funciona de forma muy parecida a OUTER JOIN porque devuelve un registro primario, aunque no haya registros secundarios que dependan de él.
  3. Los elementos de agregado sólo funcionan en campos del elemento secundario inmediato del conjunto de registros. Para trabajar en los campos de elementos terciarios u otros que estén por debajo de éstos, debe crear elementos de agregado intermedios. Consulte el ejemplo Jerarquía de grupos con elemento agregado, que se encuentra al final de este artículo.
  4. Si utiliza una función de agregado con la sintaxis de SHAPE APPEND, el valor agregado ocupará un campo anexado al conjunto de resultados primario, que a su vez contiene los campos de la instrucción primaria. Por el contrario, las instrucciones SHAPE COMPUTE y SHAPE BY crean un nuevo nivel primario para los agregados y la instrucción no normalizada se convierte en el conjunto de registros secundario.
  5. El proveedor de la instrucción SHAPE necesita que se incluya un alias para la instrucción no normalizada en la cláusula COMPUTE cuando utilice SHAPE COMPUTE. Si no lo hace, aparecerá un mensaje que le indicará que no se permite usar esa funcionalidad, pero no se produce un error de sintaxis.

Ejemplos

Jerarquía de relación simple:


SHAPE {select * from clientes}
APPEND ({select * from pedidos} AS rsPedidos
RELATE idcliente TO idcliente)
Lo que da como resultado:

Clientes.*
rsPedidos
|
+----Pedidos.*
En el diagrama anterior, el conjunto de registros primario contiene todos los campos de la tabla Clientes y un campo llamado rsPedidos. Este campo proporciona una referencia al conjunto de registros secundario y contiene todos los campos de la tabla Pedidos. El resto de los ejemplos utiliza una notación similar.


Jerarquía con parámetros:


SHAPE {select * from clientes}
APPEND ({select * from pedidos where idcliente = ?} AS rsPedidos
RELATE idcliente TO PARAMETER 0)
Se produce la misma jerarquía que la de relación simple.

Jerarquía de relación compuesta:

Este ejemplo muestra una jerarquía de clientes, pedidos y detalles de pedidos de tres niveles:

SHAPE {SELECT * from clientes}
APPEND ((SHAPE {select * from pedidos}
APPEND ({select * from [detalles de pedido]} AS rsDetalles
RELATE idpedido TO idpedido)) AS rsPedidos
RELATE idcliente TO idcliente)
Lo que da como resultado:

Clientes.*
rsPedidos
|
+----Pedidos.*
rsDetalles
|
+----[Detalles de pedidos].*

Jerarquía de relación múltiple:

Este ejemplo muestra una jerarquía en la que participa un conjunto de registros primario y dos secundarios, uno de los cuales tiene parámetros:

SHAPE {SELECT * FROM clientes}
APPEND ({SELECT *
FROM pedidos
WHERE fechapedido < #1/1/1998# AND idcliente = ?}
RELATE idcliente TO PARAMETER 0) AS rsPedidosAnteriores,
({SELECT *
FROM pedidos
WHERE fechapedido >= #1/1/1998#}
RELATE idcliente TO idcliente) AS rsPedidosRecientes
Lo que da como resultado:

Clientes.*
rsPedidosAnteriores
|
+----Pedidos.*
rsPedidosRecientes
|
+----Pedidos.*

Jerarquía con elemento agregado:


SHAPE (select * from pedidos}
APPEND ({select dp.orderid, dp.UnitPrice * dp.quantity as PrecioExtendido
from [detalles de pedido] As dp}
RELATE idpedido TO idpedido) As rsDetalles,
SUM(PrecioExtendido) AS TotalPedido
Lo que da como resultado:

Pedidos.*
rsDetalles
|
+----idpedido
PrecioExtendido
TotalPedido

Jerarquía de grupo:


SHAPE {select clientes.idcliente AS id_cli, pedidos.*
from clientes inner join pedidos
on clientes.idcliente = pedidos.idcliente} AS rsPedidos
COMPUTE rsPedidos BY id_cli
Lo que da como resultado:

rsPedidos
|
+----id_cli
Pedidos.*
id_cli

Jerarquía de grupo con elemento agregado:

NOTA: la cláusula inner de SHAPE en este ejemplo es idéntica a la instrucción usada en el ejemplo de la jerarquía con elemento agregado.

SHAPE
(SHAPE {select clientes.*, pedidos.idpedido, pedidos.fechapedido
from clientes inner join pedidos
on clientes.idcliente = pedidos.idcliente}
APPEND ({select dp.idpedido,
dp.preciounidad * dp.cantidad as PrecioExtendido
from [detalles de pedido] as dp} AS rsDetalles
RELATE idpedido TO idpedido),
SUM(rsDetalles.PrecioExtendido) AS TotalPedido) AS rsPedidos
COMPUTE rsPedidos,
SUM(rsPedidos.TotalPedido) AS TotalCli,
ANY(rsPedidos.nombrecontacto) AS Contacto
BY idcliente
Lo que da como resultado:

rsPedidos
|
+----Clientes.*
idpedido
fechapedido
rsDetalles
|
+----idpedido
PrecioExtendido
TotalPedido
TotalCliente
Contacto
idcliente

Agrupaciones múltiples:


SHAPE
(SHAPE {select clientes.*,
dp.preciounidad * dp.cantidad as PrecioExtendido
from (clientes inner join pedidos
on clientes.idcliente = pedidos.idcliente} inner join
[detalles de pedido] as dp on pedidos.idpedido = dp.idpedido}
AS rsDetalle
COMPUTE ANY(rsDetalle.nombrecontacto) AS Contacto,
ANY(rsDetalle.region) AS Region,
SUM(rsDetalle.PrecioExtendido) AS TotalCli,
rsDetalle
BY idcliente) AS rsResumenCli
COMPUTE rsResumenCli
BY Region
Lo que da como resultado:

rsResumenCli
|
+-----Contacto
Region
TotalCli
rsDetalle
|
+----Clientes.*
PrecioExtendido
idcliente
Region

Total:


SHAPE
(SHAPE {select clientes.*,
dp.preciounidad * dp.cantidad as PrecioExtendido
from (clientes inner join pedidos
on clientes.idcliente = pedidos.idcliente} inner join
[detalles de pedido] as dp on pedidos.idpedido = dp.idpedido}
AS rsDetalle
COMPUTE ANY(rsDetalle.nombrecontacto) AS Contacto,
SUM(rsDetalle.PrecioExtendido) AS TotalCli,
rsDetalle
BY idcliente) AS rsResumenCli
COMPUTE SUM(rsResumenCli.TotalCli) As Total,
rsResumenCli
Observe que falta la cláusula BY en el resumen externo. Así se determina el total, ya que el conjunto de filas (rowset) primario contiene un solo registro con el total y un puntero al conjunto de registros secundario.

Total
rsResumenCli
|
+-----Contacto
TotalCli
rsDetalle
|
+----Clientes.*
PrecioExtendido
idcliente

Jerarquía compleja:

Este ejemplo ilustra una jerarquía que contiene un conjunto de filas primario, dos secundarios, uno de los cuales tiene parámetros, y un detalle de grupo.

SHAPE {select clientes.* from clientes} AS rsDetalle
COMPUTE rsDetalle,
ANY(rsDetalle.compania) AS Compania,
({select * from pedidos where idcliente = ?}
RELATE idcliente TO PARAMETER 0) AS rsPedidos,
COUNT(rsPedidos.idpedido) AS NumeroPedidos
BY idcliente
Lo que da como resultado:

rsDetalle
|
+----Clientes.*
Compañía
rsPedidos
|
+----Pedidos.*
NúmeroPedidos
idcliente

Elemento primario agrupado relacionado con elemento secundario agrupado:


SHAPE
(SHAPE {select * from clientes}
APPEND ((SHAPE {select pedidos.*, year(fechapedido) as AñoPedido,
month(fechapedido) as MesPedido
from pedidos} AS rsPedidos
COMPUTE rsPedidos
BY idcliente, AñoPedido, MesPedido)
RELATE idcliente TO idcliente) AS rsPedidoPorMes )
AS rsClientes
COMPUTE rsClientes
BY region
Lo que da como resultado:

rsClientes
|
+-----clientes.*
rsPedidoPorMes
|
+-----rsPedidos
|
+----Pedidos.*
idcliente
AñoPedido
MesPedido
región

Gramática formal de la cláusula SHAPE


<comandoShape> ::= SHAPE <expTabla> [AS <alias>]
[<acciónShape>]

<acciónShape> ::= APPEND <listaCamposAlias>
| COMPUTE <listaCamposAlias>
[BY <listaCampos>]
| BY <listaCampos>

<expTabla> ::= {<instrucciónSqlNativa>}
| ( <comandoShape> )

<listaCamposAlias> ::= <campoAlias> [, <campoAlias...]

<campoAlias> ::= <expCampo> [AS <alias>]

<expCampo> ::= ( <expRelación> ) | <expCálculo>

<expRelación> ::= <expTabla> [AS <alias>] RELATE
<listaCondRelación>

<listaCondRelación> ::= <condRelación> [, <condRelación>...]

<condRelación> ::= <nombreCampo> TO <refSecundaria>

<refSecundaria> ::= <nombreCampo> | PARAMETER <refParam>

<refParam> ::= <nombre> | <número>

<listaCampos> ::= <nombreCampo [, <nombreArchivado>]

<expCálculo> ::= SUM (<nombreCampoCalificado>)
| AVG (<nombreCampoCalificado>)
| MIN (<nombreCampoCalificado>)
| MAX (<nombreCampoCalificado>)
| COUNT (<alias>)
| SDEV (<nombreCampoCalificado>)
| ANY (<nombreCampoCalificado>)
| CALC (<expresión>)

<nombreCampoCalificado>::= <alias>.<nombreCampo> | <nombreCampo>

<alias> ::= <nombreCitado>

<nombreCampo> ::= <nombreCitado>

<nombreCitado> ::= "<cadena>" | '<cadena>' | <nombre>

<nombre> ::= alfabético [ alfabético | dígito | _ | # ...]

<número> ::= dígito [dígito...]

<cadena> ::= carácterUnicode [carácterUnicode...]

<expresión> ::= una expresión reconocida por el servicio
de expresiones de Jet, cuyos operandos son
otras columnas no calculadas de la misma fila.

Programa de prueba de SHAPE en VBA

El código del siguiente programa de VBA le permite escribir su propio comando SHAPE y ver la jerarquía de campos o indicar la ubicación de un error de sintaxis.


ADVERTENCIA: EL USO DEL CÓDIGO PROPORCIONADO EN ESTE ARTÍCULO QUEDA BAJO SU RESPONSABLIDAD. Microsoft proporciona este código "tal cual", sin ninguna garantía tanto expresa como implícita, incluyendo pero sin limitarse a, las garantías implícitas de comerciabilidad y/o idoneidad para un fin determinado.
  1. En el Administrador de orígenes de datos ODBC del Panel de control, agregue un DSN para el controlador ODBC de Microsoft Access 97 llamado OLE_DB_NWIND_JET, que apunte a la base de datos Neptuno (o NWIND).
  2. Cree un proyecto nuevo. Agregue dos cuadros de texto (Text1 y Text2) y un botón de comando (Command1).
  3. Procure que los cuadros de texto sean lo suficientemente grandes como para mostrar varias líneas de texto y luego defina las propiedades siguientes:

    Multiline: True (sólo en Visual Basic)
    Scrollbars: Vertical
    Font: Courier New 10 Point
  4. En el menú Proyecto, seleccione Referencias y agregue una referencia a la biblioteca Microsoft ActiveX Data Objects (ADO).
  5. Escriba el código siguiente:
     Private Sub Command1_Click()
    Dim cn As ADODB.Connection, rs As ADODB.Recordset
    Me!Text2.Text = ""
    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    cn.Provider = "MSDataShape"
    cn.Open "dsn=OLE_DB_NWIND_JET"
    On Error Resume Next
    rs.Open Me!Text1.Text, cn, adOpenStatic, adLockReadOnly, adCmdText
    If Err Then MsgBox Error
    ListChapteredFields rs, 0
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
    End Sub

    Private Sub LogText(ByVal sLine As String)
    If Me!Text2.Text = "" Then
    Me!Text2.Text = sLine
    Else
    Me!Text2.Text = Me!Text2.Text & vbCrLf & sLine
    End If
    End Sub

    Private Sub ListChapteredFields(ByVal rs As ADODB.Recordset, _
    ByVal Level As Long)
    Dim I As Long
    For I = 0 To rs.Fields.Count - 1
    LogText Space$(Level * 3) & rs(I).Name
    If rs(I).Type = adChapter Then
    ListChapteredFields rs(I).Value, Level + 1
    End If
    Next I
    End Sub
  6. Ejecute el proyecto. Escriba el comando SHAPE en el cuadro de texto Text1, haga clic en el botón de comando y la jerarquía aparecerá en el cuadro de texto Text2.
NOTA: el siguiente texto es un ejemplo de cómo utilizar el proveedor OLEDB de Microsoft Jet con la instrucción SHAPE:
 cn.Provider = "MSDataShape"
cn.Open "Data Provider=Microsoft.Jet.OLEDB.4.0"
NOTA: si se equivoca al escribir los nombres de las tablas o de los campos cuando esté usando el controlador ODBC o los proveedores JOLT de Access 97, aparecerá el siguiente mensaje:
Parámetros insuficientes. Se esperaba n.
Con otros proveedores puede obtener otros mensajes.

Referencias

Especificación de cursores jerárquicos de ADO 2.0


Para obtener más información acerca de la sintaxis de SHAPE APPEND y cómo recorrer conjuntos de registros jerárquicos, consulte el siguiente artículo en Microsoft Knowledge Base:
185425 ADO Hierarchical Recordsets via SHAPE APPEND via C++/VBA/Java
Propiedades

Id. de artículo: 189657 - Última revisión: 08/19/2003 - Revisión: 1

Comentarios