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.

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

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)

Tip

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)

Note

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

Note

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.

Tip

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:

Note

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