Synergex.SynergyDE.Select.Select

Primary class that supports SQL-like data selection

WSupported on Windows

USupported on Unix

VSupported on OpenVMS

NSupported in Synergy .NET

namespace Synergex.SynergyDE.Select
public sealed class Select

The Select class supports 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 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.

See Understanding the Synergy Select API for more information.

Constructors

Select

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, 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.

from

The From class object. (@From)

where

The Where class object. (@Where)

groupby

The GroupBy or NoCaseGroupBy class object. (@GroupBy or @NoCaseGroupBy)

orderby

The OrderBy or NoCaseOrderBy class object. (@OrderBy or @NoCaseOrderBy)

sparse

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

Properties

IsOptimized

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.

IsOrderedBy

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.

QueryString

public QueryString

Returns a string representing the resulting query. (string)

You can set the SYNSEL_SUPPRESS_QUERYSTRING environment variable to stop Select from building QueryString if you want to improve performance for simple queries.

Methods

Delete

public Delete(), int

Deletes a selected group of records without retrieving records individually and returns the total number of records deleted.

Dispose

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.

GetEnum

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.

GetEnumerator

public GetEnumerator(), @AlphaEnumerator

Creates and returns an AlphaEnumerator object and establishes the selection context. (See Synergex.SynergyDE.Select.AlphaEnumerator.)

Join

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).

RegisterEvent

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).

SparseRecord

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().

When Select.SparseRecord is used in conjunction with IOHooks, Sparse is ignored or disabled until I/O hooks on the channel have been disabled or removed. More specifically, when either read or write I/O hooks have been implemented and are part of the IOHooks creation mask and either the Select has specified a Sparse object on creation or SparseRecord has been issued, sparse data functionality is disabled. To re-enable sparse functionality, both the read and write I/O hooks must be removed.

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.

Understanding static cursors

A static cursor provides a snapshot of records in a result set, generated when an enumerator is created (for example, at the start of a FOREACH loop iterating over the results in a Select or JoinSelect).

By default, iterating over a result set from Select or JoinSelect will read one or more records directly from the data file(s). But some new features take a different approach and retrieve records from a static cursor instead. When a query uses a static cursor, all file I/O occurs up front, when the enumerator is created or reset. All matching records are then kept in memory (potentially backed by a temp file on disk) and are accessed from memory without any need of additional I/O.

Currently, static cursors are used by all queries that use a GroupBy object and all queries that use JoinSelect with OrderBy.Ascending() and/or Descending(). (Static cursors were not implemented in xfServer 12.1, so any GroupBy queries to that version of xfServer will not use static cursors.)

This approach has several distinctive features, which may or may not be advantageous:

• Results reflect the state of the underlying file(s) at the time the static cursor was created. Any file I/O after that point (for example, records added, modified, or deleted) will have no effect on the result set.

• Records cannot be locked. If a static cursor is used with a file opened in update mode, locks must be disabled. Enumerator members such as DeleteCurrent, SparseUpdate, and GetCTInfo will not work.

• Creating an enumerator will take more time with a static cursor (due to the I/O required to assemble the result set), while iterating over a result set will take less time (as rows can be accessed directly from memory without additional I/O). Specifics will vary for different queries, but a static cursor may significantly reduce the overall time needed for a query that requires a lot of sorting before returning results. If a result set is already sorted in the file on disk, the time difference could be negligible.

• Since all results are kept in memory, a static cursor may use significantly more memory than an equivalent query without a static cursor. To reduce memory usage and improve performance, all queries that use a static cursor require the use of a Sparse object. We recommend that you use this object to specify only fields that are essential to the query, so that irrelevant fields don’t take up space in memory (although you can specify "*" if you really want to include all fields in the query). You can also set the environment variable SYNSEL_CURSORMEM to the maximum memory that any static cursor can use. An $ERR_INVO error will occur if the result set for a query would cause the static cursor to exceed this value.

Change tracking operations are not supported in Select objects that use static cursors.

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)
    .
    .
    .