PC: Standalone RowSet

STANDALONE ROWSET

In PeopleCode a standalone rowset is an independent rowset object not associated with the component buffer. They allow you to work with data outside of the buffer by getting whatever additional data you need from the database. In this sense they replace the functionality of derived records which were once used as place holders to store data not directly associated with the component. Because a standalone rowset is standalone, there is no automatic action by the component processor on it. This means that if a standalone rowset is used to manipulate data (inserts/updates), code will need to be added to manually save the changes. 

Code to create a standalone rowset object
Local Rowset &rsExample; 
&rsExample = CreateRowset (Record.REC1);

Filling a standalone RowSet:

The Fill method in the Rowset class is used to populate the rowset. This parameters to the fill method area Where clause and bind values. &rExample.Fill("where FIELD1 = :1", REC2.FIELD2);


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();

Out Put for the above program
CC8001 03/01/199802/15/1998011200 USDConference 00001 
CC8001 03/01/199802/16/19980220000 JPYConference 00001

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();