Recordset class: Difference between revisions

From m204wiki
Jump to navigation Jump to search
mNo edit summary
m (remove excessive blanks from paste of example)
 
(31 intermediate revisions by 6 users not shown)
Line 1: Line 1:
<!-- Recordset class -->
<!-- Recordset class -->
The Recordset class provides an object-oriented equivalent of <var class="product">Model 204</var> found sets.
The <var>Recordset</var> class provides an object-oriented equivalent of <var class="product">Model 204</var> found sets.
 
<div id="infind"></div>  
<div id="infind"></div>
Consider the following example of using a <var>Recordset</var> object:
Consider the following example of using a <var>Recordset</var> object:
<p class="code">%suspendedCustomers is object Recordset in group customer
<p class="code">%suspendedCustomers is object recordset in group customer
   ...
   ...
find records to %suspendedCustomers
find records to %suspendedCustomers
Line 19: Line 19:
<li>The declaration of <code>%suspendedCustomers</code> indicates
<li>The declaration of <code>%suspendedCustomers</code> indicates
the group to which it applies.
the group to which it applies.
 
<li>The <var>Find</var> statement does not require a label, though one is
<li>The <var>Find</var> statement does not require a label, though one is
allowed.
allowed.
 
<li>The <var>Find</var> statement does not require an <var>In</var> clause before
<li>The <var>Find</var> statement does not require an <var>In</var> clause before
the <code>Find</code> to indicate the name of the file or group to which it applies.
the <var>Find</var> to indicate the name of the file or group to which it applies.
The compiler can determine the file or group context
The compiler can determine the file or group context
from the object variable.
from the object variable.
Line 30: Line 30:
on that In clause must match the file or group context on the file
on that In clause must match the file or group context on the file
object declaration.
object declaration.
 
<li>The <code>%suspendedCustomers</code> object establishes the file/group
<li>The <code>%suspendedCustomers</code> object establishes the file/group
context for both the <var>Find</var> and <var>For Each Record</var> blocks, and thereby, the
context for both the <var>Find</var> and <var>For Each Record</var> blocks, and thereby, the
field names that can be used in the blocks.
field names that can be used in the blocks.
</ul>
</ul>
 
==Creating Recordset objects==
==Creating Recordset objects==
Before <var class="product">Sirius Mods</var> version 6.6, a <var>Recordset</var> object could not be the target
Before <var class="product">Sirius Mods</var> version 6.6, a <var>Recordset</var> object could not be the target
of a <var>New</var> constructor.
of a <var>New</var> constructor.
In <var class="product">Sirius Mods</var> 6.6 and later, however, an empty (but not null) Recordset
In <var class="product">Sirius Mods</var> 6.6 and later, however, an empty (but not null) <var>Recordset</var>
object can be created with the <var>[[New (Recordset constructor)|New]]</var> constructor.
object can be created with the <var>[[New (Recordset constructor)|New]]</var> constructor.
 
Making a declared <var>Recordset</var> object variable the target of a <var>Find</var> statement
Making a declared <var>Recordset</var> object variable the target of a <var>Find</var> statement
is another way to instantiate a <var>Recordset</var> object.
is another way to instantiate a <var>Recordset</var> object.
Line 47: Line 47:
with a <var>Recordset</var> object target can be
with a <var>Recordset</var> object target can be
thought of as a shared method in the <var>Recordset</var> class:
thought of as a shared method in the <var>Recordset</var> class:
<p class="code">Find All Records To %recordSet
<p class="code">find all records to %recordSet
</p>
</p>
'''Note:'''
<p class="note"><b>Note:</b>
Only a declared <var>Recordset</var> object variable may be the target of such a <var>Find</var> statement: that is, a <var>Recordset</var> member invocation is not allowed to be the target.
Only a declared <var>Recordset</var> object variable may be the target of such a <var>Find</var> statement: that is, a <var>Recordset</var> member invocation is not allowed
For example, <code>Find To %recset:Copy</code>, for the <var>Recordset</var> object
to be the target.
For example, <code>find to %recset:copy</code>, for the <var>Recordset</var> object
variable <code>%recset</code> and the <var>Recordset</var> method <code>Copy</code>
variable <code>%recset</code> and the <var>Recordset</var> method <code>Copy</code>
(or any system or user class method or class variable) is '''not''' allowed.
(or any system or user class method or class variable) is '''not''' allowed.
 
</p>
===Find statement formats===
===Find statement formats===
The <var class="product">User Language</var> <var>Find</var> statement has a variety of optional clauses and keywords.
The User Language <var>Find</var> statement has a variety of optional clauses and keywords.
The following list shows the formats of many of the <var>Find</var> statement
The following list shows the formats of many of the <var>Find</var> statement
variations you may use to instantiate a <var>Recordset</var> object:
variations you may use to instantiate a <var>Recordset</var> object:
<p class="code">FIND ALL RECORDS FOR WHICH field=value
<p class="code">FIND ALL RECORDS FOR WHICH field=value
   Find All Records '''To %recordSet''' For Which field=value
   Find All Records '''To %recordSet''' For Which field=value
 
FIND RECORDS field IS PRESENT
FIND RECORDS field IS PRESENT
   Find Records '''To %recordSet''' field Is Present
   Find Records '''To %recordSet''' field Is Present
 
IN FILE CLIENTS FD
IN FILE CLIENTS FD
   In File Clients Fd '''To %recordSet'''
   In File Clients Fd '''To %recordSet'''
 
FIND AND PRINT COUNT WITH field=value
FIND AND PRINT COUNT WITH field=value
   Find And Print Count '''To %recordSet''' With field=value
   Find And Print Count '''To %recordSet''' With field=value
 
FIND AND RESERVE ALL RECORDS
FIND AND RESERVE ALL RECORDS
   Find And Reserve All Records '''To %recordSet'''
   Find And Reserve All Records '''To %recordSet'''
 
FDR field=value
FDR field=value
   Fdr '''To %recordSet''' field=value
   Fdr '''To %recordSet''' field=value
 
FIND WITHOUT LOCKS ALL RECORDS
FIND WITHOUT LOCKS ALL RECORDS
   Find Without Locks All Records '''To %recordSet'''
   Find Without Locks All Records '''To %recordSet'''
 
IN GROUP ACCOUNTS FDWOL field IS PRESENT
IN GROUP ACCOUNTS FDWOL field IS PRESENT
   In Group Accounts Fdwol '''To %recordSet''' field Is Present
   In Group Accounts Fdwol '''To %recordSet''' field Is Present
</p>
</p>
'''Note:'''
<blockquote class="note">
As shown in the [[#infind|introductory example]], the <var>Recordset</var> object declaration specifies
<p><b>Note:</b>
the file or group context to which the <var>Find</var> statements apply.
As shown in the [[#infind|introductory example]], the <var>Recordset</var> object declaration specifies the file or group context to which the <var>Find</var> statements apply.
The <code>In File</code> and <code>In Group</code> clauses in the statements above '''must'''
The <var>In File</var> and <var>In Group</var> clauses in the statements above '''must'''
agree with their corresponding object declarations.
agree with their corresponding object declarations.
For an example where an <var>In Group</var> clause is more meaningful,
For an example where an <var>In Group</var> clause is more meaningful,
see [[#Selecting group members|"Selecting group members"]].
see [[#Selecting group members|"Selecting group members"]]. </p>
 
<p>
If there is no <var>In File</var> or <var>In Group</var> clause, there must be a default file context.
If there is no <var>In File</var> or <var>In Group</var> clause, there must be a default file context.</p>
</blockquote>


===Locking for Recordset objects===
===Locking for Recordset objects===
Find statements that create a <var>Recordset</var> object also determine
<var>Find</var> statements that create a <var>Recordset</var> object also determine
the strength of the lock on the object.
the strength of the lock on the object.
The locking strengths match those in standard <var class="product">Model 204</var> record locking:
The locking strengths match those in standard <var class="product">Model 204</var> record locking:
none, shared, and exclusive.
<var>None</var>, <var>Share</var>, and <var>Exclusive</var>.
 
The following statements instantiate <var>Recordset</var> objects with
The following statements instantiate <var>Recordset</var> objects with
locking level <var>None</var>, then with level <var>Share</var>, then with level <var>Exclusive</var>.
locking level <var>None</var>, then with level <var>Share</var>, then with level <var>Exclusive</var>.  
<p class="code">find without locks records to %recordSet
<p class="code">find without locks records to %recordSet
  ...
  ...
Line 107: Line 110:
find and reserve records to %recordSet
find and reserve records to %recordSet
</p>
</p>
 
The <var>Find</var> statement types and the locking strength of the objects they instantiate are:
The <var>Find</var> statement types and the locking strength of the objects they instantiate are:
<dl>
<dl>
<dt>None
<dt>None
<dd>Find Without Locks, Fdwol
<dd><var>Find Without Locks</var>, <var>Fdwol</var>
<dt>Share
<dt>Share
<dd>Find All Records, Fd, Find And Print Count, Fpc
<dd><var>Find All Records</var>, <var>Fd</var>, <var>Find And Print Count</var>, <var>Fpc</var>
<dt>Exclusive
<dt>Exclusive
<dd>Find And Reserve, Fdr
<dd><var>Find And Reserve</var>, <var>Fdr</var>
</dl>
</dl>
 
Other non-<var>Find</var> methods that create <var>Recordset</var> objects also lock the
Other non-<var>Find</var> methods that create <var>Recordset</var> objects also lock the
<var>Recordset</var> object at a particular <var>LockStrength</var>.
<var>Recordset</var> object at a particular <var>LockStrength</var>.
These methods include <var>New</var>, <var>Copy</var>, <var>DeepCopy</var>, and many others.
These methods include <var>New</var>, <var>Copy</var>, <var>DeepCopy</var>, and many others.
 
The non-settable property <var>LockStrength</var> returns a
The non-settable property <var>LockStrength</var> returns a
<var>LockStrength</var> enumeration that indicates the locking of the <var>Recordset</var> object.
<var>LockStrength</var> enumeration that indicates the locking of the <var>Recordset</var> object.
For more information about enumerations, see [[Enumerations|"Enumerations"]].
For more information about enumerations, see [[Enumerations|"Enumerations"]].
 
Each instance of a <var>Recordset</var> object instance holds space in the <var class="product">Model 204</var> record
Each instance of a <var>Recordset</var> object instance holds space in the <var class="product">Model 204</var> record
locking table, and it holds a CCATEMP bitmap page for any segment
locking table, and it holds a CCATEMP bitmap page for any segment
Line 131: Line 134:
A <var>Recordset</var> object instance also holds whatever record locks were obtained
A <var>Recordset</var> object instance also holds whatever record locks were obtained
by the <var>Find</var> statement or the method that created it.
by the <var>Find</var> statement or the method that created it.
 
Record locking table space, CCATEMP bitmap pages, and record locks are held
Record locking table space, CCATEMP bitmap pages, and record locks are held
by a <var>Recordset</var> object instance until the <var>Recordset</var> object is either
by a <var>Recordset</var> object instance until the <var>Recordset</var> object is either
Line 139: Line 142:
In addition to the <var>LockStrength</var> property, in <var class="product">Sirius Mods</var> 7.0 and later,
In addition to the <var>LockStrength</var> property, in <var class="product">Sirius Mods</var> 7.0 and later,
all <var>Recordset</var> objects also have a <var>LoopLockStrength</var> property.
all <var>Recordset</var> objects also have a <var>LoopLockStrength</var> property.
Like the LockStrength property, the LoopLockStrength property's values
Like the <var>LockStrength</var> property, the <var>LoopLockStrength</var> property's values
are of the LockStrength enumeration.
are of the <var>LockStrength</var> enumeration.
 
The LoopLockStrength property indicates the minimum lock strength for
The <var>LoopLockStrength</var> property indicates the minimum lock strength for
the record being processed in an iteration of a loop (For Each Record or
the record being processed in an iteration of a loop (<var>For Each Record</var> or
For <n> Records) on a <var>Recordset</var> object.
<var>For <i>n</i> Records</var>) on a <var>Recordset</var> object.
If the LoopLockStrength is the same as or weaker than the LockStrength
If the <var>LoopLockStrength</var> is the same as or weaker than the <var>LockStrength</var>
of a <var>Recordset</var> object, no action is required at each iteration of a
of a <var>Recordset</var> object, no action is required at each iteration of a
loop &mdash; the record in the iteration is known to be locked at the
loop &mdash; the record in the iteration is known to be locked at the
strength of the Recordset which is greater than the LoopLockStrength.
strength of the <var>Recordset</var> which is greater than the <var>LoopLockStrength</var>.
 
If, however, the LoopLockStrength is stronger than the LockStrength,
If, however, the <var>LoopLockStrength</var> is stronger than the <var>LockStrength</var>,
each iteration of a For loop on a <var>Recordset</var> object tries to obtain
each iteration of a <var>For</var> loop on a <var>Recordset</var> object tries to obtain
a LoopLockStrength level lock on the record in the iteration.
a <var>LoopLockStrength</var> level lock on the record in the iteration.
If successful, the iteration is processed and the lock is released
If successful, the iteration is processed and the lock is released
at the end of the iteration.
at the end of the iteration.
The default value of LoopLockStrength is <code>None</code>, which means that
The default value of <var>LoopLockStrength</var> is <code>None</code>, which means that
no additional locking is performed for the records in a Recordset
no additional locking is performed for the records in a <var>Recordset</var>
during loop processing.
during loop processing.
 
There are two common scenarios where LoopLockStrength might be useful:
There are two common scenarios where <var>LoopLockStrength</var> might be useful:
<ol>
<ol>
<li>An application doesn't want to hold a lock on a set of records
<li>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
(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
that any processing inside a <var>For</var> loop on those records sees a consistent
picture of the record being processed.
picture of the record being processed.
A LockStrength of None and a LoopLockStrength of Share would prevent other
A <var>LockStrength</var> of <code>None</code> and a <var>LoopLockStrength</var> of <code>Share</code> would prevent other
threads from updating a record while inside a For loop on the record, but
threads from updating a record while inside a <var>For</var> loop on the record, but
they would leave all other records in the Recordset unlocked.
they would leave all other records in the <var>Recordset</var> unlocked.
 
There is no guarantee, however, that a record processed in
There is no guarantee, however, that a record processed in
multiple For loops on a Recordset wouldn't be updated by another
multiple <var>For</var> loops on a <var>Recordset</var> wouldn't be updated by another
thread between the two For loops.
thread between the two <var>For</var> loops.
<li>An application knows that it will (or almost certainly will) update
<li>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
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.
each record until it is processed in a <var>For</var> loop on the <var>Recordset</var>.
A LoopLockStrength of Exclusive would get an exclusive lock on the record
A <var>LoopLockStrength</var> of <code>Exclusive</code> would get an exclusive lock on the record
at the start of any For loop, which ensures that the record is updateable
at the start of any <var>For</var> loop, which ensures that the record is updateable
before any processing is performed on the record.
before any processing is performed on the record.
 
Using a LoopLockStrength of Exclusive on a Recordset that has a LockStrength
Using a <var>LoopLockStrength</var> of <code>Exclusive</code> on a <var>Recordset</var> that has a <var>LockStrength</var>
of Share can produce ''deadly embraces'' between two threads.
of <code>Share</code> can produce ''deadly embraces'' between two threads.
This is also true of any updates performed on Recordsets locked in Share
This is also true of any updates performed on <var>Recordset</var>s locked in <code>Share</code>
mode.
mode.
This risk exists because if two threads get Share locks on a record in their
This risk exists because if two threads get <code>Share</code> locks on a record in their
respective Recordsets, and if both threads try to promote their locks on the record
respective <var>Recordset</var>s, and if both threads try to promote their locks on the record
to Exclusive for LoopLockStrength for a record update, they will each be
to <code>Exclusive</code> for <var>LoopLockStrength</var> for a record update, they will each be
blocked by the other's shared lock.
blocked by the other's <code>Share</code> lock.
 
This suggests that, as a general policy, upgrading a lock from Shared
This suggests that, as a general policy, upgrading a lock from <code>Share</code>
to Exclusive is a bad idea.
to <code>Exclusive</code> is a bad idea.
It is best to use no lock until it is
It is best to use no lock until it is
known that a record is likely to be updated, at which point an Exclusive
known that a record is likely to be updated, at which point an <code>Exclusive</code>
lock should be obtained.
lock should be obtained.
Note that if a LoopLockStrength of Exclusive is used to &ldquo;pave the
Note that if a <var>LoopLockStrength</var> of <code>Exclusive</code> is used to &ldquo;pave the
way&rdquo; for a record update in a For loop on a Recordset, one might
way&rdquo; for a record update in a <var>For</var> loop on a <var>Recordset</var>, one might
want to Commit the transaction inside the For loop.
want to <var>Commit</var> the transaction inside the <var>For</var> loop.
Otherwise, if an update had been done, the pending update would hold
Otherwise, if an update had been done, the pending update would hold
an exclusive lock on the record until the update is committed.
an exclusive lock on the record until the update is committed.
</ol>
</ol>
 
LoopLockStrength locking is somewhat unusual in that it can cause
<var>LoopLockStrength</var> locking is somewhat unusual in that it can cause
a record lock to be obtained at every iteration of a For loop on
a record lock to be obtained at every iteration of a <var>For</var> loop on
a Recordset.
a <var>Recordset</var>.
As such, it's possible to get a record locking conflict at each
As such, it's possible to get a record locking conflict at each
iteration of a For loop on a Recordset.
iteration of a <var>For</var> loop on a <var>Recordset</var>.
This, in turn, imparts special meaning to certain statements in
This, in turn, imparts special meaning to certain statements in
an On Record Locking conflict unit:
an On Record Locking conflict unit:
<dl>
<dl>
<dt>Bypass
<dt>Bypass
<dd>Causes the loop to continue at the next record in the Recordset.
<dd>Causes the loop to continue at the next record in the <var>Recordset</var>.
<dt>Retry
<dt>Retry
<dd>Causes the loop to stay on the current record in the Recordset
<dd>Causes the loop to stay on the current record in the <var>Recordset</var>
and to try again to lock the record at the LoopLockStrength level.
and to try again to lock the record at the <var>LoopLockStrength</var> level.
</dl>
</dl>


==Using Recordset objects==
==Using Recordset objects==
Because the same Recordset object can be used as the target of any
Because the same <var>Recordset</var> object can be used as the target of any
number of Find statements, you can forego one of the
number of <var>Find</var> statements, you can forego one of the
most common uses of Lists in User Language: holding the results of alternate
most common uses of Lists in User Language: holding the results of alternate <var>Find</var> criteria.
Find criteria.
 
For example, here is a case where a lookup
For example, here is a case where a lookup
is done based on an ID, if it is available; otherwise the lookup is done
is done based on an ID, if it is available; otherwise the lookup is done
on the basis of a surname and first name:
on the basis of a surname and first name:
<p class="code"> %custList  is object recordSet in group customer
<p class="code"> %custList  is object recordSet in group customer
 
  if %id ne '' then
  if %id ne &apos;&apos; then
     find records to %custList
     find records to %custList
       id eq %id
       id eq %id
Line 236: Line 238:
     end find
     end find
  end if
  end if
 
  for each record in %custList
  for each record in %custList
   ...
   ...
</p>
</p>
 
The flexibility of <var>Recordset</var> objects also allows the same Recordset
The flexibility of <var>Recordset</var> objects also allows the same <var>Recordset</var> to have different lock strengths for the same <var>For</var> loop:
to have different lock strengths for the same For loop:
<p class="code"> %order  is object recordSet in file orders
<p class="code"> %order  is object recordSet in file orders
 
  if %action eq 'Update' then
  if %action eq 'Update' then
     find and reserve records to %order
     find and reserve records to %order
Line 254: Line 255:
     end find
     end find
  end if
  end if
 
  for each record in %order
  for each record in %order
   ...
   ...
</p>
</p>
While very useful in many situations, this capability also
While very useful in many situations, this capability also
presents the possibility that a For Each Record loop might be
presents the possibility that a <var>For Each Record</var> loop might be
executed with different lock strengths in different situations.
executed with different lock strengths in different situations.
===Supported statement contexts===
===Supported statement contexts===
A Recordset object can be used in relatively few contexts:
A <var>Recordset</var> object can be used in relatively few contexts:
<ul>
<ul>
<li>Find statement
<li><var>Find</var> statement
 
In addition to the <var>Find</var> statement contexts already shown,
In addition to the <var>Find</var> statement contexts already shown,
a <var>Recordset</var> object can also be used in an In clause:
a <var>Recordset</var> object can also be used in an In clause:
<p class="code"> find records in %recordSet to %otherRecordset
<p class="code"> find records in %recordSet to %otherRecordset
</p>
</p>
This syntax can be used with a traditional Find to a label:
This syntax can be used with a traditional <var>Find</var> to a label:
<p class="code"> label: find records in %recordSet
<p class="code"> label: find records in %recordSet
</p>
</p>
Similarly, a traditional foundset on a label can be used in an In clause
Similarly, a traditional foundset on a label can be used in an In clause
for a Find to a <var>Recordset</var> object:
for a <var>Find</var> to a <var>Recordset</var> object:
<p class="code"> find records in label to %recordSet
<p class="code"> find records in label to %recordSet
</p>
</p>
<li>For Each Record statement
<li>Release Records statement
<li>Sort Records statement


The target of a Sort statement can be either a label or a <var>SortedRecordset</var> object.
<li><var>For Each Record</var> statement
 
<li><var>Release Records</var> statement
 
<li><var>Sort Records</var> statement
The target of a <var>Sort</var> statement can be either a label or a <var>[[SortedRecordset class]]</var> object.
Mirroring the <var>Find</var> statement examples above, statements like these are allowed:
Mirroring the <var>Find</var> statement examples above, statements like these are allowed:
<p class="code"> sort records in %recSet to %sortedRecordset by name
<p class="code"> sort records in %recSet to %sortedRecordset by name
Line 290: Line 295:
     ...
     ...
</p>
</p>
The following subsection has more detail on <var>Sort</var>.
</ul>
</ul>
====Sorting====
While you can reference a sort label, as shown above, to work with the output of a sort operation on a <var>Recordset</var> 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 <var>SortedRecordset</var> object, the code has access to the [[List of SortedRecordset methods|SortedRecordset methods]].
For the slight inconvenience of defining both the <var>Recordset</var> and <var>SortedRecordset</var> objects, you gain the ability to work fully in O-O mode, to open cursors into the sorted set of records, and more:
<p class="code">%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
...
</p>
====Counting====
====Counting====
Many statements that can be used with traditional, labeled found sets
Many statements that can be used with traditional, labeled found sets
are not supported for <var>Recordset</var> objects (see [[#Unsupported statement contexts|Unsupported statement contexts]]).
are not supported for <var>Recordset</var> objects (see [[#Unsupported statement contexts|Unsupported statement contexts]]).
Although the Count Records statement is ''not'' supported for objects,
Although the <var>Count Records</var> statement is ''not'' supported for objects,
it has a replacement: the Count method.
it has a replacement: the <var>Count</var> method.
For example:
For example:
<p class="code"> %custList is object Recordset in Group Customer
<p class="code">%custList is object recordset in Group Customer
  ...
...
find records to %custList
find records to %custList
    income gt %minIncome
  income gt %minIncome
end find
end find
  ...
...
print 'There are ' %custList:count ' customers ' -
print 'There are ' %custList:count ' customers ' -
      'with income greater than ' %minIncome
      'with income greater than ' %minIncome
</p>
</p>
The Count method has these advantages over the Count Records
The <var>Count</var> method has these advantages over the <var>Count Records</var>
statement:
statement:
<ul>
<ul>
<li>It does not require a label to hold the count.
<li>It does not require a label to hold the count.
<li>The processing to calculate the count is only performed when
<li>The processing to calculate the count is only performed when
the count is requested.
the count is requested.
With Count Records, the count is often calculated &ldquo;just in case.&rdquo;
With <var>Count Records</var>, the count is often calculated "just in case."
 
<li>The processing to calculate the count is only performed once
<li>The processing to calculate the count is only performed once
for any object.
for any object.
After that, the count is saved in the object, and the saved count is used
After that, the count is saved in the object, and the saved count is used
for subsequent Count method invocations.
for subsequent <var>Count</var> method invocations.
</ul>
</ul>
====Selecting group members====
====Selecting group members====
You can declare a <var>Recordset</var> object for a file that is
You can declare a <var>Recordset</var> object for a file that is
a member of a group, then use
a member of a group, then use
the In Group Member clause on the <var>Find</var> statement to specify a file.
the <var>In Group Member</var> clause on the <var>Find</var> statement to specify a file.
For example:
For example:
<p class="code"> ...
<p class="code"> ...
%recset is object recordSet in group div2
%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
...
</p>


%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
  ...
</p>
===Unsupported statement contexts===
===Unsupported statement contexts===
Many other found set label contexts are ''not'' currently supported for
Many other found set label contexts are ''not'' currently supported for
Recordset objects.
<var>Recordset</var> objects.
These include:
These include:
<ul>
<ul>
<li>Count Records In (but see [[#Counting|Counting]])
<li><var>Count Records In</var> (but see [[#Counting|Counting]])
<li>Delete Records In
<li><var>Delete Records In</var>
<li>File Records In
<li><var>File Records In</var>
<li>For Each Record In Order
<li><var>For Each Record In Order</var>
<li>Place Records In
<li><var>Place Records In</var>
<li>Remove Records In
<li><var>Remove Records In</var>
</ul>
</ul>
 
The need for the Place Records In and Remove Records In statements
The need for the <var>Place Records In</var> and <var>Remove Records In</var> statements
is largely obviated by the AddRecordset and RemoveRecordset
is largely obviated by the <var>AddRecordset</var> and <var>RemoveRecordset</var>
methods.
methods.
And it is possible to take advantage of many of the other statements
And it is possible to take advantage of many of the other statements
with <var>Recordset</var> objects by using the <code>Find Records In</code> syntax.
with <var>Recordset</var> objects by using the <var>Find Records In</var> syntax.
For example, the following fragment demonstrates how the For Each Record In
For example, the following fragment demonstrates how the <var>For Each Record In
Order statement can be
Order</var> statement can be
used indirectly on a <var>Recordset</var> object:
used indirectly on a <var>Recordset</var> object:
<p class="code"> %custList is object Recordset in Group Customer
<p class="code">%custList is object recordset in group customer
  ...
...
custTemp: find without locks records in %custList
custTemp: find without locks records in %custList
          end find
          end find
 
custSort: for each record in custTemp in order by income
custSort: for each record in custTemp in order by income
</p>
</p>
While this approach has most of the problems associated with using
While this approach has most of the problems associated with using
labeled found sets inside classes, it at least makes it possible
labeled found sets inside classes, it at least makes it possible
to take advantage of additional User Language statements within limited contexts.
to take advantage of additional <var class="product">User Language</var> statements within limited contexts.
 
===Example: Recordset object collection===
===Example: Recordset object collection===
One advantage of <var>Recordset</var> objects over traditional Find labels or Lists
One advantage of <var>Recordset</var> objects over traditional <var>Find</var> labels or Lists
is that one can maintain arrays or collections of <var>Recordset</var> objects.
is that one can maintain arrays or collections of <var>Recordset</var> objects.
The following example request builds an Arraylist of <var>Recordset</var> objects, then
The following example request builds an <var>Arraylist</var> of <var>Recordset</var> objects, then
processes them in a subsequent For loop.
processes them in a subsequent <var>For</var> loop.
<p class="code"> Begin
<p class="code">Begin
%r    is object Recordset in myfile
%r    is object recordset in myfile
%rset is collection arrayList of object Recordset in myfile
%rset is collection arrayList of object recordset in myfile
%k    is string len 10
%k    is string len 10
%key  is collection arrayList of string len 10
%key  is collection arrayList of string len 10
%i is fixed
%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
</p>


%key  = new
===Example: Counting rectypes===
  %rset = new
This example illustrates the power of <var>Recordset</var> 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. <var>Recordset</var> 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:


%key:add('MOE')
<p class="code">begin
%key:add('LARRY')
%key:add('SHEMP')


for %i from 1 to %key:count
%master        is object recordset in file vendors
    %k = %key(%i)
%final        is object recordset in file vendor
    find records to %r
%interim      is object recordset in file vendor
      name eq %k
%rectype      is string len 64
    end find
%recTracker    is namedArrayList of float
    %rset:add(%r)
 
end for
%final      = New 
  ...
%recTracker = New
for %i from 1 to %rset:count
 
    for each record in %rset(%i)
find all records to %master
      print 'Recset num ' %i ', name ' %key(%i)
end find
      print $currec
 
      pai
for each record in %master
    end for
  %rectype = rectype
end for
 
  %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


release all records
end
end
</p>
</p>


Line 412: Line 472:
<ul>
<ul>
<li>The <var>[[Object variables#Discarding objects|Discard]]</var> method
<li>The <var>[[Object variables#Discarding objects|Discard]]</var> method
 
<li>A <code>Release Records In ''object''</code> statement
<li>A <code>Release Records In ''object''</code> statement
 
<li>A <var>Release All Records</var> statement, which, in addition to its normal function,
<li>A <var>Release All Records</var> statement, which, in addition to its normal function,
will discard all file objects
will discard all file objects
</ul>
</ul>
 
==List of Recordset methods==
==List of Recordset methods==
The [[List of Recordset methods|"List of Recordset methods"]] shows all the class methods.
The [[List of Recordset methods]] shows all the class methods.
 
[[Category:System classes]]
[[Category:System classes]]

Latest revision as of 20:05, 5 October 2015

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.