Omron CX-Supervisor, User Manual

Page: 198 / 334

Datashaping SECTION 12 Databases
197
12-9 Datashaping
The ADO SHAPE command can be used to produce hierarchical recordsets
i.e. where a value within the current record is used to access another
Recordset. For example, a record showing Customer Details might contain a
field for Customer ID. Every time the record position is changed, a child
Recordset showing Orders for that Customer ID could be recreated.
Hierarchical recordsets present an alternative to using JOIN syntax when
accessing parent-child data. Hierarchical recordsets differ from a JOIN in that
with a JOIN, both the parent table fields and child table fields are represented
in the same recordset. With a hierarchical recordset, the recordset contains
only fields from the parent table. In addition, the recordset contains an extra
field that represents the related child data, which you can assign to a second
recordset variable and traverse.
Hierarchical recordsets are made available via the MSDataShape provider,
which is implemented by the client cursor engine.
A new clause, SHAPE, is provided to relate SELECT statements in a
hierarchical fashion. The syntax is summarized below:
SHAPE {parent-command} [[AS] name]
APPEND ({child-command} [[AS] name] RELATE parent-
field TO child-field)
[,({child2-command} …)]
Creating a Datashape
connection.
1. Create DSN file specifying the required database as the Data Source
2. Configure a Connection to the DSN file. In the connection string type
Provider=MSDataShape;
FILEDSN=<your file name>.dsn
3. Configure a Recordset as SQL Text and enter the required shape
command as the Source. (See Datashape Source examples).
4. After successfully adding a Datashape Recordset it is now possible to add
a Child Recordset to the existing Recordset by selecting the right menu
option 'Add Recordset' which will now be enabled. If the Connection is
'Live' a list of valid child recordset names will be entered in the Source
ComboBox
5. Field associations can be added to Child recordsets in the normal manner.
Child recordsets can be accessed via Script command in a similar manner
to normal recordsets
bResult = DBState(
"DataShape.Customers.Orders.Details", "Open" )
Note: By default, the child recordsets in the parent recordset will be called Chapter 1,
Chapter 2, etc., unless you use the optional [[AS] name] clause to name the
child recordset.
Note: You can nest the SHAPE command. The {parent-command} and/or {child-
command} can contain another SHAPE statement.
Note: The {parent-command} and {child-command} do not have to be SQL SELECT
statements. They can use whatever syntax is supported by data provider.
Note: A child recordset will be automatically opened/closed whenever its Parent
recordset is opened/closed. A child recordset is effectively a field of its parent
recordset therefore whenever a new record is selected in the parent a new
child recordset will be generated.
Note: Child recordsets are not supported in the Database function dialog box