Using Standalone Rowsets
This section provides an overview of standalone rowsets and discusses how to:
Use the Fill rowset method.
Use the CopyTo rowset method.
Add child rowsets.
Use standalone rowsets to write a file.
Use standalone rowsets to read a file.
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.
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.
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);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)));The following is an example of using standalone rowsets along with a file layout to write a file:
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", "UTF8", %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 00001The 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", "UTF8", %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();