Open topic with navigation
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 and OrderBy 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 an OrderBy class object is specified, it defines the order by which the records will be presented upon retrieval. In addition, Select Join functionality enables you to combine records from two or more SDBMS sources. (See Joining data from multiple sources for more information.)
Once created, any object used by the Select (From, Where, NoCaseWhere, On, OrderBy, 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, On, OrderBy, 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, orderby) public Select(from, orderby) public Select(sparse, from) public Select(sparse, from, where) 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 OrderBy class object. (@OrderBy)
The Sparse class object. (@Sparse)
Select selects records from a file (From), where certain conditions apply (Where), in the specified order upon retrieval (OrderBy). Sparse improves network performance.
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.
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.
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.
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.
public static GetEnum(), @RestrictedAlphaEnumerator
Retrieves a RestrictedAlphaEnumerator from within the nearest FOREACH context (see Synergex.SynergyDE.Select.AlphaEnumerator).
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)
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:
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.
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.
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. 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:|
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()
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()
foreach rows in new Select( fromCustomers & .InnerJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
foreach rows in new Select( fromOrders & .InnerJoin( fromCustomers, & (On)(customers.CustomerID == orders.CustomerID)), & (Where)(orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
foreach rows in new Select( fromCustomers & .LeftJoin( fromOrders, & (On)(customers.CustomerID == orders.CustomerID .AND. & (orders.OrderDate .GE. StartDate .AND. & orders.OrderDate .LE. EndDate)).Join()
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
foreach rec in new Select(new From(ch, record), (Where)fld1.ge.10) begin writes(TTCHN, rec) Select.GetEnum().CurrentRFA(rfa) end
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
disposable data mysel, @select, new Select(new From(ch, record), & (Where)fld1.ge.10) foreach rec in mysel begin writes(TTCHN, rec) . . .