Skip to Main Content
Return to Navigation

Using Standalone Rowsets

This section provides an overview of standalone rowsets and discusses how to:

Understanding Standalone Rowsets

Standalone rowsets are not associated with a component or page. Use them to work on data that is not associated with a component or page buffer.In earlier releases, this was done using derived work records. You still must build work pages.

Note: Standalone rowsets are not connected to the Component Processor, so there are no database updates when they are manipulated. Delete and insert actions on these types of rowsets are not automatically applied at save time.

As with any PeopleTools object, the scope of standalone rowsets can be Local, Global, or Component. Consider the following code:

Local Rowset &MYRS; 
 
&MYRS = CreateRowset(RECORD.SOMEREC);

This code creates a rowset with SOMEREC as the level zero record. The rowset is unpopulated. Functionally, it is the same as an array of rows.

Using the Fill Method

The Fill method fills the rowset by reading records from the database, by first flushing out all the contents of the rowset. A Where clause must be provided to get all the relevant rows.

Local Rowset &MYRS; 
Local String &EMPLID; 
 
&MYRS = CreateRowset(RECORD.SOMEREC); 
&EMPLID = '8001'; 
 
&MYRS.Fill("where EMPLID = :1", &EMPLID); 
 

Use the Fill method with standalone rowsets, created using the CreateRowset function. Do not use Fill with component buffer rowsets.

Using the CopyTo Method

The CopyTo method copies like-named fields from a source rowset to a destination rowset. To perform the copy, it uses like-named records for matching, unless specified. It works on any rowset except the Application Engine state records. The following is an example:

Local Rowset &MYRS1, MYRS2; 
Local String &EMPLID; 
 
&MYRS1 = CreateRowset(RECORD.SOMEREC); 
&MYRS2 = CreateRowset(RECORD.SOMEREC); 
 
&EMPLID = '8001'; 
 
&MYRS1.Fill("where EMPLID = :1", &EMPLID); 
&MYRS1.CopyTo(&MYRS2);

After running the previous code segment, &MYRS2 contains that same data as &MYRS1. Both &MYRS1 and &MYRS2 were built using like-named records.

To use the CopyTo method where there are no like-named records, you must specify the source and destination records. The following code copies only like-named fields:

Local Rowset &MYRS1, MYRS2; 
Local String &EMPLID; 
 
&MYRS1 = CreateRowset(RECORD.SOMEREC1); 
&MYRS2 = CreateRowset(RECORD.SOMEREC2); 
 
&EMPLID = '8001'; 
 
&MYRS1.Fill("where EMPLID = :1", &EMPLID); 
&MYRS1.CopyTo(&MYRS2, RECORD.SOMEREC1, RECORD.SOMEREC2);

Adding Child Rowsets

The first parameter of the CreateRowset method determines the top-level structure. If you pass the name of the record as the first parameter, the rowset is based on a record. You can also base the structure on a different rowset. In the following example, &MYRS2 inherits the structure of &MYRS1:

Local Rowset &MYRS1, MYRS2; 
 
&MYRS1 = CreateRowset(RECORD.SOMEREC1); 
&MYRS2 = CreateRowset(&MYRS1);

To add a child rowset, suppose the following records describe a relationship. The structure is made up of three records:

  • PERSONAL_DATA

  • BUS_EXPENSE_PER

  • BUS_EXPENSE_DTL

To build rowsets with child rowsets, use code like the following:

Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl; 
 
&rsBusExpDtl = CreateRowset(Record.BUS_EXPENSE_DTL); 
&rsBusExpPer = CreateRowset(Record.BUS_EXPENSE_PER, &rsBusExpDtl); 
&rsBusExp = CreateRowset(Record.PERSONAL_DATA, &rsBusExpPer);

Another variation is

&rsBusExp = CreateRowset(Record.PERSONAL_DATA, 
CreateRowset(Record.BUS_EXPENSE_PER, 
CreateRowset(Record.BUS_EXPENSE_DTL)));

Using Standalone Rowsets to Write a File

Image: File layout example

The following is an example of using standalone rowsets along with a file layout to write a file:

File layout example

The following example writes a file using a file layout that contains parent-child records:

Local File &MYFILE; 
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl; 
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl; 
Local SQL &SQL1, &SQL2, &SQL3; 
 
&rBusExp = CreateRecord(Record.PERSONAL_DATA); 
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER); 
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL); 
 
&rsBusExp = CreateRowset(Record.PERSONAL_DATA, 
CreateRowset(Record.BUS_EXPENSE_PER, 
CreateRowset(Record.BUS_EXPENSE_DTL))); 
&rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1); 
 
&MYFILE = GetFile("c:\temp\BUS_EXP.out", "W", %FilePath_Absolute); 
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT); 
 
&EMPLID = "8001"; 
 
&SQL1 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExp, &EMPLID); 
&SQL2 = CreateSQL("%selectall(:1) where EMPLID = :2", &rBusExpPer, &EMPLID); 
 
While &SQL1.Fetch(&rBusExp) 
   &rBusExp.CopyFieldsTo(&rsBusExp.GetRow(1).PERSONAL_DATA); 
   &I = 1; 
   While &SQL2.Fetch(&rBusExpPer) 
      &rBusExpPer.CopyFieldsTo(&rsBusExpPer(&I).BUS_EXPENSE_PER); 
      &J = 1; 
      &SQL3 = CreateSQL("%selectall(:1) where EMPLID = :2 
and EXPENSE_PERIOD_DT = :3", &rBusExpDtl, &EMPLID, 
&rsBusExpPer(&I).BUS_EXPENSE_PER.EXPENSE_PERIOD_DT.Value); 
      &rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1); 
      While &SQL3.Fetch(&rBusExpDtl) 
         &rBusExpDtl.CopyFieldsTo(&rsBusExpDtl(&J).BUS_EXPENSE_DTL); 
         &rsBusExpDtl.InsertRow(&J); 
         &J = &J + 1; 
      End-While; 
 
      &rsBusExpPer.InsertRow(&I); 
      &I = &I + 1; 
   End-While; 
   &MYFILE.WriteRowset(&rsBusExp); 
End-While; 
&MYFILE.Close();

The previous code generates the following output file.

AA8001       Schumacher,Simon 
BB8001       06/11/1989YNA0        Customer Go-Live Celebration 
CC8001       06/11/1989
             06/01/198908226.83              USDEntertain Clients   10100 
BB8001       08/31/1989YNA0        Customer Focus Group Meeting 
CC8001       08/31/198908/11/1989012401.58             USDCustomer Visit      10100 
CC8001       08/31/198908/12/198904250.48              USDCustomer Visit      10100 
CC8001       08/31/198908/12/198902498.34              USDCustomer Visit      10100 
BB8001       03/01/1998YYP0        Attend Asia/Pacific Conference 
CC8001       03/01/199802/15/1998011200                USDConference          00001 
CC8001       03/01/199802/16/19980220000               JPYConference          00001 
BB8001       05/29/1998NNP0        Annual Subscription 
CC8001       05/29/199805/29/199814125.93              USDSoftware, Inc.      10100 
BB8001       08/22/1998NNP0        Regional Users Group Meeting 
CC8001       08/22/199808/22/19981045.69               USDDrive to Meeting    10100 
CC8001       08/22/199808/22/19980912.44               USDCity Parking        10100 
BB8001       12/12/1998NNP0        Customer Visit: Nevco 
CC8001       12/12/199812/02/199801945.67              USDCustomer Feedback   00001 
CC8001       12/12/199812/02/19981010.54               USDTo Airport          00001 
CC8001       12/12/199812/03/19980610                  USDAirport Tax         00001 
CC8001       12/12/199812/03/199804149.58              USDCustomer Feedback   00001 
CC8001       12/12/199812/04/1998055.65                USDCheck Voicemail     00001 
CC8001       12/12/199812/04/19980988                  USDAirport Parking     00001 
CC8001       12/12/199812/04/199802246.95              USDCustomer Feedback   00001 
CC8001       12/12/199812/04/199803135.69              USDCustomer Feedback   00001

Using Standalone Rowsets to Read a File

The following code shows an example of reading in a file and inserting the rows into the database:

Local File &MYFILE; 
Local Rowset &rsBusExp, &rsBusExpPer, &rsBusExpDtl; 
Local Record &rBusExp, &rBusExpPer, &rBusExpDtl; 
Local SQL &SQL1; 
 
&rBusExp = CreateRecord(Record.PERSONAL_DATA); 
&rBusExpPer = CreateRecord(Record.BUS_EXPENSE_PER); 
&rBusExpDtl = CreateRecord(Record.BUS_EXPENSE_DTL); 
 
&rsBusExp = CreateRowset(Record.PERSONAL_DATA, 
CreateRowset(Record.BUS_EXPENSE_PER, 
CreateRowset(Record.BUS_EXPENSE_DTL))); 
 
&MYFILE = GetFile("c:\temp\BUS_EXP.out", "R", %FilePath_Absolute); 
&MYFILE.SetFileLayout(FileLayout.BUS_EXP_OUT); 
 
&SQL1 = CreateSQL("%Insert(:1)"); 
 
&rsBusExp = &MYFILE.ReadRowset(); 
While &rsBusExp <> Null; 
   &rsBusExp.GetRow(1).PERSONAL_DATA.CopyFieldsTo(&rBusExp); 
   &rsBusExpPer = &rsBusExp.GetRow(1).GetRowset(1); 
   For &I = 1 To &rsBusExpPer.ActiveRowCount 
      &rsBusExpPer(&I).BUS_EXPENSE_PER.CopyFieldsTo(&rBusExpPer); 
      &rBusExpPer.ExecuteEdits(%Edit_Required); 
      If &rBusExpPer.IsEditError Then 
         For &K = 1 To &rBusExpPer.FieldCount 
            &MYFIELD = &rBusExpPer.GetField(&K); 
            If &MYFIELD.EditError Then 
               &MSGNUM = &MYFIELD.MessageNumber; 
               &MSGSET = &MYFIELD.MessageSetNumber; 
            End-If; 
         End-For; 
      Else 
         &SQL1.Execute(&rBusExpPer); 
         &rsBusExpDtl = &rsBusExpPer.GetRow(&I).GetRowset(1); 
         For &J = 1 To &rsBusExpDtl.ActiveRowCount 
            &rsBusExpDtl(&J).BUS_EXPENSE_DTL.CopyFieldsTo(&rBusExpDtl); 
            &rBusExpDtl.ExecuteEdits(%Edit_Required); 
            If &rBusExpDtl.IsEditError Then 
               For &K = 1 To &rBusExpDtl.FieldCount 
                  &MYFIELD = &rBusExpDtl.GetField(&K); 
                  If &MYFIELD.EditError Then 
                     &MSGNUM = &MYFIELD.MessageNumber; 
                     &MSGSET = &MYFIELD.MessageSetNumber; 
                  End-If; 
               End-For; 
            Else 
               &SQL1.Execute(&rBusExpDtl); 
            End-If; 
         End-For; 
      End-If; 
   End-For; 
   &rsBusExp = &MYFILE.ReadRowset(); 
End-While; 
&MYFILE.Close();