|
WTSupported in traditional Synergy on Windows
|
WNSupported in Synergy .NET on Windows
|
USupported on UNIX
|
VSupported on OpenVMS
|
namespace Synergex.SynergyDE.Select public sealed class Select
The Select class is a group of classes and class members that support SQL-like data selection. Its targeted use is as an enumerator accessed via the FOREACH statement, but individual access is also allowed.
The Select class object requires a From object, which describes the file to use and the record layout. It can also contain optional Where, GroupBy/NoClassGroupBy, OrderBy/NoCaseOrderBy, and Sparse class objects. If a Where class object is specified, it defines the selection criteria. If it is not specified or has no record scrutiny, all records will be selected. If a GroupBy or NoCaseGroupBy class object is specified, it groups records by like values and returns only the first identical value. If an OrderBy or NoCaseOrderByclass object is specified, it defines the order by which the records will be presented upon retrieval. If a Sparse class object is specified, only a partial record is returned. In addition, Select Join functionality enables you to combine records from two or more Synergy DBMS sources. (See Joining data from multiple sources for more information.)
|
Once created, any object used by the Select (From, Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse) must remain in contact with the same record(s) specified in the From object(s) as well as fields referenced in the Where, NoCaseWhere, GroupBy, NoCaseGroupBy, On, OrderBy, NoCaseOrderBy, and Sparse objects that are contained in the record(s). In other words, you can’t create one of these objects and pass it to another routine unless that routine has direct access to the original record. If this is not the case, an InvalidOperationException or $ERR_INVOPER error (“Where operator requires at least one field reference”) will result when creating the Select object. On the other hand, all data references outside a From object’s record are copied during the object’s creation and are not required to remain in contact with the original data. |
The Select class object that is created represents the complete selection criteria to use when elements are enumerated. It does not retrieve or perform I/O on the selected file; creating and iterating on an enumerator performs the actual I/O.
public Select(from) public Select(from, where) public Select(from, where, groupby) public Select(from, where, orderby) public Select(from, groupby) public Select(from, orderby) public Select(sparse, from) public Select(sparse, from, where) public Select(sparse, from, where, groupby) public Select(sparse, from, groupby) public Select(sparse, from, orderby) public Select(sparse, from, where, orderby)
The Select constructor generates or initializes a Select object. Each parameter, except the required From object, is optional, which gives you the possible parameter combinations above.
The From class object. (@From)
The Where class object. (@Where)
The GroupBy or NoCaseGroupBy class object. (@GroupBy or @NoCaseGroupBy)
The OrderBy or NoCaseOrderBy class object. (@OrderBy or @NoCaseOrderBy)
The Sparse class object. (@Sparse)
Select selects records from a file (From), where certain conditions apply (Where), arranged into groups (GroupBy) in the specified order upon retrieval (OrderBy). Sparse improves network performance.
- If the where parameter is specified, selection criteria are applied to filter the return set; otherwise, no filter occurs and all records are returned.
- If the groupby parameter is specified, the return set is arranged into groups based on identical values (case-sensitive for GroupBy or case-insensitive for NoCaseGroupBy) and ordered as specified; otherwise, no grouping occurs.
- If the orderby parameter is specified, it defines the order in which the return set will be presented (case-sensitive for OrderBy or case-insensitive for NoCaseOrderBy); otherwise, the order is defined by the access method (i.e., is undefined and depends on optimization).
- If the sparse parameter is specified, the fields in the return set are filled according to the Sparse object for optimal transfer when using xfServer; otherwise, all fields are filled. When using the sparse parameter, the Select.SparseRecord() method is not supported.
With Join functionality, the from parameter does more than just tell the Select what file to use and the record layout. It can include one or more Joins, with each Join adding more Froms, etc.
See also
- Synergex.SynergyDE.Select.From class
- Synergex.SynergyDE.Select.Where class
- Synergex.SynergyDE.Select.GroupBy class
- Synergex.SynergyDE.Select.NoCaseGroupBy class
- Synergex.SynergyDE.Select.OrderBy class
- Synergex.SynergyDE.Select.NoCaseOrderBy class
- Synergex.SynergyDE.Select.Sparse class
- Joining data from multiple sources
public IsOptimized
Implements a get method that returns optimization information as one of the following values: (int)
0 = Selection is not optimized.
1 = Selection employs head optimization.
2 = Selection employs tail optimization.
3 = Selection employs both head and tail optimization.
See Optimization types for more information about head and tail optimization.
public IsOrderedBy
Implements a get method that returns sorting information as one of the following values: (int)
-1 = Selection will be ordered by file order.
0 = Selection will be ordered by the primary key.
numbered_key = Selection will be ordered by an alternate key.
public QueryString
Returns a string representing the resulting query. (string)
public Delete(), int
Deletes a selected group of records without retrieving records individually and returns the total number of records deleted.
public Dispose(), void
Releases the Select context from the file and, if being managed by the From object, closes the file. (Synergy .NET only)
|
Because Select (or From) has what . NET considers to be heavy-weight objects with unmanaged resources, we recommend that you always explicitly dispose of Select and From objects rather than waiting for garbage collection. |
public static GetEnum(), @RestrictedAlphaEnumerator
Retrieves a RestrictedAlphaEnumerator from within the nearest FOREACH context (see Synergex.SynergyDE.Select.AlphaEnumerator).
or
public static GetEnum(record) @RestrictedAlphaEnumerator
(traditional Synergy only) Retrieves a RestrictedAlphaEnumerator from within the first FOREACH context using the specified record (a) as the control variable. A ^NULL handle is returned if an appropriate FOREACH context is not found.
public GetEnumerator(), @AlphaEnumerator
Creates and returns an AlphaEnumerator object and establishes the selection context. (See Synergex.SynergyDE.Select.AlphaEnumerator.)
public Join(), @JoinSelect
Converts a Select object to a JoinSelect object. This method certifies that the Select object contains Join constructs and returns a JoinSelect object (see Synergex.SynergyDE.Select.JoinSelect).
public RegisterEvent(event), @Select
Registers a specific event that may be activated due to some condition during the select process. An event is considered a user-defined class object (@Event) that extends the Event class (see Synergex.SynergyDE.Select.Event).
public varargs SparseRecord(field1, ...), @Select
Defines a partial record to be returned by the enumerator. Field1 is one or more field specifications that must be part of the record specified in the From class object. (n)
Discussion
|
|
The FOREACH statement hides the complexity of enumerators. Therefore, we recommend using FOREACH rather than directly manipulating the enumerator. (Note, however, that a System.ObjectDisposedException may occur when using FOREACH in .NET. See the note in the Discussion for FOREACH for more information.) |
After the GetEnumerator() method is called, the selection context is established on the specified channel. The AlphaEnumerator methods and properties will then operate in that context. A READ, GET, FIND, relative WRITE, or another call to GetEnumerator() will break out of any selection context established on the specified channel. Performing any of the following operations on a channel with an established selection context cause an “Invalid operation: Select context is active” error ($ERR_INVOPER): READS, GETS, ACCEPT, DELETE, or ISAM WRITE. Doing a STORE of a record on a channel with a selection context will store the new record without changing the selection context.
Only one enumerator is allowed on a channel at one time. GetEnumerator() (called explicitly or implicitly through FOREACH) generates a new enumerator and invalidates any prior enumerator generated on the same Select object or on another Select object using the same From channel.
The SparseRecord() method is so named because it specifies a partial, or sparse, record. Once retrieved, a sparse record may not be written back to the file using the Current property of the AlphaEnumerator/RestrictedAlphaEnumerator class. Instead, use the SparseUpdate() method in the AlphaEnumerator/RestrictedAlphaEnumerator class.
When a sparse record is a subset of the record, the entire record will be read, but only the specified fields will be returned; all other fields will be blanked out. Using SparseRecord() is most effective when you’re using xfServer and only parts of a record are needed. Only those fields specified are transferred across the network, optimizing performance. Combining SparseRecord() with the use of SparseUpdate() further enhances network performance. See the SparseUpdate() Calls table for more information about SparseUpdate().
|
You can also return partial records using the Synergex.SynergyDE.Select.Sparse class, which enables you to dynamically construct a list of fields at runtime. Using Select with a Sparse record without xfServer (i.e., to a local file) provides no real benefit and can negatively affect performance. We recommend using Sparse records only when accessing remote files via xfServer. |
The Delete() method reads and locks all matching records and deletes them immediately. The total number of records deleted is returned. This functionality is useful for deleting records in a remote file without the overhead of retrieving each record individually. For example,
from = new From(ch, rec) sobj = new Select(from, wobj) delcnt = sobj.Delete()
If you’re using Delete() and attempt to hook either the pre-delete or post-delete I/O hooks, an “Invalid operation: Delete not allowed on I/O Hook channel” error ($ERR_ INVOPER) will be generated.
You can use RegisterEvent() to handle locked records encountered during the deletion:
sobj.RegisterEvent(new lockEvent())
If a lock is encountered, an event is thrown, and an opportunity is given to either wait on the lock, skip the lock and not delete the record, or terminate. An Event.onLock() method that changes the lock parameter to Q_NO_DELETE (or Q_NO_LOCK) before returning true causes the locked record to be skipped and the remaining selection to be deleted. See Synergex.SynergyDE.Select.Event for more information.
Prefetching records to improve performance with xfServer
To improve record selection performance when using xfServer, a prefetch feature is available that enables the client to prefetch (or buffer) selected records for files of any type that are open in input mode. Prefetching is off by default unless you’re using Join. To turn prefetching on, set the SCSPREFETCH environment variable to a value between 1 and 32, which specifies the size of the prefetch buffer in kilobytes. To turn prefetching off, set SCSPREFETCH to 0. You can enable or disable prefetching on a file-by-file basis by using the SETLOG routine to set or clear SCSPREFETCH before opening the channel specified in the From. The value of SCSPREFETCH is checked on every remote file open.
Joining data from multiple sources
You can combine data from multiple Synergy DBMS sources by using the Select Join feature. The Select Join feature identifies records from two or more tables/files to join, providing functionality similar to SQL92’s high-speed inner joins and left outer joins.
|
|
The SQL terms “table,” “row,” and “column” are equivalent to the Synergy terms “file,” “record,” and “field,” respectively. In this document we will use the latter terminology. |
For example, say you wanted a list of all customers in the customer file and whether each customer had any orders during a particular period, defined by start and end dates. Here’s how you could do that:
| 1. | Each source file must contain one or more matching fields for which a join can be made. The matching fields must be of the same type and size, unless both field and key type are numeric. For example, |
fromOrders = new From("orders.ism", orders)
fromOrderdetails = new From("orderdetails.ism", orderdetails)
fromCustomers = new From("customers.ism", customers)
fromProducts = new From("products.ism", products)
| 2. | Match up the Orders file with the Orderdetails file. For each order there are one or more details, but you want to see only the details that are covered in the Orders file. The Orders file and the Orderdetails file both contain the matching field OrderID. |
joinObj = new Select( fromOrders.InnerJoin( fromOrderdetails, & (On)(orders.OrderID == orderdetails.OrderID) ) ).Join()
| 3. | Here are some ways to link up customers and orders: |
- Match up the Customers and Products files. Again, for each order there is one customer and for each order detail there is one product. The Orders and Customers files both contain the matching field CustomerID, and both the Orderdetails and Products files contain the matching field ProductID. (Because the Select line is too long to fit on one line, it is split up into several lines and grouped similarly to how SQL typically breaks up lines.)
joinObj = new Select( fromOrders & .InnerJoin( fromOrderdetails, & (On)(orders.OrderID == orderdetails.OrderID)) & .InnerJoin( fromCustomers, & (On)(orders.CustomerID == customers.CustomerID)) & .InnerJoin( fromProducts, & (On)(orderdetails.ProductID == products.ProductID)))).Join()
- If you don’t want to keep From class object handles and want your files closed when iteration is complete, provide the From instantiation in-line to the Select.
foreach rows in new Select(new From("orders.ism", order)
& .InnerJoin(new From("orderdetails.ism", orderdetails),
& (On)(orders.OrderID == orderdetails.OrderID))
& .InnerJoin(new From("customers.ism", customers),
& (On)(orders.CustomerID == customers.CustomerID))
& .InnerJoin(new From("products.ism", products),
& (On)(orderdetails.ProductID == products.ProductID)))).Join()
- If you want to join customers in the Customers file with matching orders made in a particular time period (defined by start and end dates), use the From.InnerJoin() method and add an additional filter condition to the on parameter. This returns only the customers that had orders during the specified period. This method is usually more efficient than that in the next bullet.
foreach rows in new Select( fromCustomers & .InnerJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to join customers in the Customers file with matching orders made in a certain period, use the From.InnerJoin() method, but instead of using the on parameter, use the where parameter. When you use this method, everything referenced in the where has to be in the first file listed (Orders, in our example). This method can be more efficient than the above bullet, depending on the optimization of where.
foreach rows in new Select( fromOrders & .InnerJoin( fromCustomers, & (On)(customers.CustomerID == orders.CustomerID)), & (Where)(orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to join all customers in the Customers file and indicate which had orders during the time period, use the From.LeftJoin() method. This returns all customers, as opposed to the two bullets immediately above, where we returned a subset of customers.
foreach rows in new Select( fromCustomers & .LeftJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
- If you want to perform a right join, which Synergy DBL doesn’t support, you can swap the order of the From objects to effectively perform a right join using From.LeftJoin(). This is the opposite of the bullet immediately above: It will return list of all orders, indicating the customers associated with those that were during the specified period.
foreach rows in new Select( fromOrders & .LeftJoin( fromCustomers, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
| 4. | Iterate over the Join class object to retrieve the record set that matches. On the completion of each iteration, the Orders record and Orderdetails record are embedded in the Rows object. Use the Rows.Fill() method to extract them to their appropriate data records. |
foreach rows in joinObj
begin
rows.fill(orders)
rows.fill(orderdetails)
.
.
.
end
Examples
foreach rec in new Select(new From(ch, record), (Where)fld1.ge.10)
begin
writes(TTCHN, rec)
Select.GetEnum().CurrentRFA(rfa)
end
or
fobj = new From(ch, Q_NO_LOCK, Q_NOWAIT, rec)
wobj = Where.Between(custno, 1, 1000)
sobj = new Select(fobj, wobj)
enuma = sobj.GetEnumerator()
while (enuma.MoveNext())
begin
writes(TTCHN, enuma.Current)
enuma.CurrentRFA(rfa)
end
or
disposable data mysel, @select, new Select(new From(ch, record),
& (Where)fld1.ge.10)
foreach rec in mysel
begin
writes(TTCHN, rec)
.
.
.
