Recordset class

The Recordset class provides an object-oriented equivalent of Model 204 found sets.

Consider the following example of using a Recordset object:

%suspendedCustomers is object recordset in group customer ... find records to %suspendedCustomers status = 'SUS' end find ... for each record in %suspendedCustomers print 'Customer: ' custid ... end for

There are several things worth noting in this example:

• The declaration of %suspendedCustomers indicates the group to which it applies.
• The Find statement does not require a label, though one is allowed.
• The Find statement does not require an In clause before the Find to indicate the name of the file or group to which it applies. The compiler can determine the file or group context from the object variable. Although an In clause is allowed, the file or group specified on that In clause must match the file or group context on the file object declaration.
• The %suspendedCustomers object establishes the file/group context for both the Find and For Each Record blocks, and thereby, the field names that can be used in the blocks.

Creating Recordset objects

Before Sirius Mods version 6.6, a Recordset object could not be the target of a New constructor. In Sirius Mods 6.6 and later, however, an empty (but not null) Recordset object can be created with the New constructor.

Making a declared Recordset object variable the target of a Find statement is another way to instantiate a Recordset object. In fact, a Find statement like the following with a Recordset object target can be thought of as a shared method in the Recordset class:

find all records to %recordSet

Note: Only a declared Recordset object variable may be the target of such a Find statement: that is, a Recordset member invocation is not allowed to be the target. For example, find to %recset:copy, for the Recordset object variable %recset and the Recordset method Copy (or any system or user class method or class variable) is not allowed.

Find statement formats

The User Language Find statement has a variety of optional clauses and keywords. The following list shows the formats of many of the Find statement variations you may use to instantiate a Recordset object:

FIND ALL RECORDS FOR WHICH field=value Find All Records To %recordSet For Which field=value FIND RECORDS field IS PRESENT Find Records To %recordSet field Is Present IN FILE CLIENTS FD In File Clients Fd To %recordSet FIND AND PRINT COUNT WITH field=value Find And Print Count To %recordSet With field=value FIND AND RESERVE ALL RECORDS Find And Reserve All Records To %recordSet FDR field=value Fdr To %recordSet field=value FIND WITHOUT LOCKS ALL RECORDS Find Without Locks All Records To %recordSet IN GROUP ACCOUNTS FDWOL field IS PRESENT In Group Accounts Fdwol To %recordSet field Is Present

Note: As shown in the introductory example, the Recordset object declaration specifies the file or group context to which the Find statements apply. The In File and In Group clauses in the statements above must agree with their corresponding object declarations. For an example where an In Group clause is more meaningful, see "Selecting group members".

If there is no In File or In Group clause, there must be a default file context.

Locking for Recordset objects

Find statements that create a Recordset object also determine the strength of the lock on the object. The locking strengths match those in standard Model 204 record locking: None, Share, and Exclusive.

The following statements instantiate Recordset objects with locking level None, then with level Share, then with level Exclusive.

find without locks records to %recordSet ... find records to %recordSet ... find and reserve records to %recordSet

The Find statement types and the locking strength of the objects they instantiate are:

None

Find Without Locks, Fdwol

Share

Find All Records, Fd, Find And Print Count, Fpc

Exclusive

Find And Reserve, Fdr

Other non-Find methods that create Recordset objects also lock the Recordset object at a particular LockStrength. These methods include New, Copy, DeepCopy, and many others.

The non-settable property LockStrength returns a LockStrength enumeration that indicates the locking of the Recordset object. For more information about enumerations, see "Enumerations".

Each instance of a Recordset object instance holds space in the Model 204 record locking table, and it holds a CCATEMP bitmap page for any segment with some records. A Recordset object instance also holds whatever record locks were obtained by the Find statement or the method that created it.

Record locking table space, CCATEMP bitmap pages, and record locks are held by a Recordset object instance until the Recordset object is either implicitly or explicitly discarded.

LoopLockStrength for Recordsets

In addition to the LockStrength property, in Sirius Mods 7.0 and later, all Recordset objects also have a LoopLockStrength property. Like the LockStrength property, the LoopLockStrength property's values are of the LockStrength enumeration.

The LoopLockStrength property indicates the minimum lock strength for the record being processed in an iteration of a loop (For Each Record or For n Records) on a Recordset object. If the LoopLockStrength is the same as or weaker than the LockStrength of a Recordset object, no action is required at each iteration of a loop — the record in the iteration is known to be locked at the strength of the Recordset which is greater than the LoopLockStrength.

If, however, the LoopLockStrength is stronger than the LockStrength, each iteration of a For loop on a Recordset object tries to obtain a LoopLockStrength level lock on the record in the iteration. If successful, the iteration is processed and the lock is released at the end of the iteration. The default value of LoopLockStrength is None, which means that no additional locking is performed for the records in a Recordset during loop processing.

There are two common scenarios where LoopLockStrength might be useful:

1. An application doesn't want to hold a lock on a set of records (or a single record foundset) for a long time, but it wants to ensure that any processing inside a For loop on those records sees a consistent picture of the record being processed. A LockStrength of None and a LoopLockStrength of Share would prevent other threads from updating a record while inside a For loop on the record, but they would leave all other records in the Recordset unlocked. There is no guarantee, however, that a record processed in multiple For loops on a Recordset wouldn't be updated by another thread between the two For loops.
2. An application knows that it will (or almost certainly will) update a set of records, but it doesn't want to get an exclusive lock on each record until it is processed in a For loop on the Recordset. A LoopLockStrength of Exclusive would get an exclusive lock on the record at the start of any For loop, which ensures that the record is updateable before any processing is performed on the record. Using a LoopLockStrength of Exclusive on a Recordset that has a LockStrength of Share can produce deadly embraces between two threads. This is also true of any updates performed on Recordsets locked in Share mode. This risk exists because if two threads get Share locks on a record in their respective Recordsets, and if both threads try to promote their locks on the record to Exclusive for LoopLockStrength for a record update, they will each be blocked by the other's Share lock. This suggests that, as a general policy, upgrading a lock from Share to Exclusive is a bad idea. It is best to use no lock until it is known that a record is likely to be updated, at which point an Exclusive lock should be obtained. Note that if a LoopLockStrength of Exclusive is used to “pave the way” for a record update in a For loop on a Recordset, one might want to Commit the transaction inside the For loop. Otherwise, if an update had been done, the pending update would hold an exclusive lock on the record until the update is committed.

LoopLockStrength locking is somewhat unusual in that it can cause a record lock to be obtained at every iteration of a For loop on a Recordset. As such, it's possible to get a record locking conflict at each iteration of a For loop on a Recordset. This, in turn, imparts special meaning to certain statements in an On Record Locking conflict unit:

Bypass

Causes the loop to continue at the next record in the Recordset.

Retry

Causes the loop to stay on the current record in the Recordset and to try again to lock the record at the LoopLockStrength level.

Using Recordset objects

Because the same Recordset object can be used as the target of any number of Find statements, you can forego one of the most common uses of Lists in User Language: holding the results of alternate Find criteria.

For example, here is a case where a lookup is done based on an ID, if it is available; otherwise the lookup is done on the basis of a surname and first name:

%custList is object recordSet in group customer if %id ne '' then find records to %custList id eq %id end find else find records to %custList surname eq %surname firstname eq %firstname end find end if for each record in %custList ...

The flexibility of Recordset objects also allows the same Recordset to have different lock strengths for the same For loop:

%order is object recordSet in file orders if %action eq 'Update' then find and reserve records to %order orderNum eq %orderNum end find else find records to %order orderNum eq %orderNum end find end if for each record in %order ...

While very useful in many situations, this capability also presents the possibility that a For Each Record loop might be executed with different lock strengths in different situations.

Supported statement contexts

A Recordset object can be used in relatively few contexts:

• Find statement In addition to the Find statement contexts already shown, a Recordset object can also be used in an In clause:

  find records in %recordSet to %otherRecordset

  This syntax can be used with a traditional Find to a label:

  label: find records in %recordSet

  Similarly, a traditional foundset on a label can be used in an In clause for a Find to a Recordset object:

  find records in label to %recordSet

• For Each Record statement
• Release Records statement
• Sort Records statement The target of a Sort statement can be either a label or a SortedRecordset class object. Mirroring the Find statement examples above, statements like these are allowed:

  sort records in %recSet to %sortedRecordset by name sort record keys in %recSet to %sortedRecordset by name ... label: sort records in %recSet by name label: sort record keys in %recSet by name ...

  The following subsection has more detail on Sort.

Sorting

While you can reference a sort label, as shown above, to work with the output of a sort operation on a Recordset object, it's generally more useful to use a SortedRecordset class object as the target of a sort. By routing the sort output to a SortedRecordset object, the code has access to the SortedRecordset methods. For the slight inconvenience of defining both the Recordset and SortedRecordset objects, you gain the ability to work fully in O-O mode, to open cursors into the sorted set of records, and more:

%rs is object Recordset in file albums %srs is object SortedRecordset in file albums ... in albums find records to %rs lastName="Cave" firstName="Nick" end find sort records in %rs to %srs by albumName ...

Counting

Many statements that can be used with traditional, labeled found sets are not supported for Recordset objects (see Unsupported statement contexts). Although the Count Records statement is not supported for objects, it has a replacement: the Count method. For example:

%custList is object recordset in Group Customer ... find records to %custList income gt %minIncome end find ... print 'There are ' %custList:count ' customers ' - 'with income greater than ' %minIncome

The Count method has these advantages over the Count Records statement:

• It does not require a label to hold the count.
• The processing to calculate the count is only performed when the count is requested. With Count Records, the count is often calculated "just in case."
• The processing to calculate the count is only performed once for any object. After that, the count is saved in the object, and the saved count is used for subsequent Count method invocations.

Selecting group members

You can declare a Recordset object for a file that is a member of a group, then use the In Group Member clause on the Find statement to specify a file. For example:

... %recset is object recordSet in group div2 %a = 'accts' in group div2 member %a fdwol to %recset actvy.id eq 10 end find for each record in %recset print $curfile pai print '****' end for release records in %recset ...

Unsupported statement contexts

Many other found set label contexts are not currently supported for Recordset objects. These include:

• Count Records In (but see Counting)
• Delete Records In
• File Records In
• For Each Record In Order
• Place Records In
• Remove Records In

The need for the Place Records In and Remove Records In statements is largely obviated by the AddRecordset and RemoveRecordset methods. And it is possible to take advantage of many of the other statements with Recordset objects by using the Find Records In syntax. For example, the following fragment demonstrates how the For Each Record In Order statement can be used indirectly on a Recordset object:

%custList is object recordset in group customer ... custTemp: find without locks records in %custList end find custSort: for each record in custTemp in order by income

While this approach has most of the problems associated with using labeled found sets inside classes, it at least makes it possible to take advantage of additional User Language statements within limited contexts.

Example: Recordset object collection

One advantage of Recordset objects over traditional Find labels or Lists is that one can maintain arrays or collections of Recordset objects. The following example request builds an Arraylist of Recordset objects, then processes them in a subsequent For loop.

Begin %r is object recordset in myfile %rset is collection arrayList of object recordset in myfile %k is string len 10 %key is collection arrayList of string len 10 %i is fixed %key = new %rset = new %key:add('MOE') %key:add('LARRY') %key:add('SHEMP') for %i from 1 to %key:count %k = %key(%i) find records to %r name eq %k end find %rset:add(%r) end for ... for %i from 1 to %rset:count for each record in %rset(%i) print 'Recset num ' %i ', name ' %key(%i) print $currec pai end for end for release all records end

Example: Counting rectypes

This example illustrates the power of Recordset objects compared to record lists. The following utility is one that many people have written, and the logic is often difficult to follow when lists are employed. Recordset objects are a very efficient way to investigate a record structure and report on a file's record structure. As the code shows, the logic being performed is also very easy to understand:

begin %master is object recordset in file vendors %final is object recordset in file vendor %interim is object recordset in file vendor %rectype is string len 64 %recTracker is namedArrayList of float %final = New %recTracker = New find all records to %master end find for each record in %master %rectype = rectype %final:addRecord(currentRecord) find all records in %master to %interim rectype = %rectype end find %recTracker(%rectype) = %interim:Count %master:removeRecordset(%interim) end for %recTracker:print end

Discarding Recordset objects

There are three ways to explicitly discard a Recordset object:

• The Discard method
• A Release Records In object statement
• A Release All Records statement, which, in addition to its normal function, will discard all file objects

List of Recordset methods

The List of Recordset methods shows all the class methods.