PeopleCode Examples Library >

Traversing a Rowset in PeopleCode .

/* Assume we have a Level 1 rowset called &Level1 */
Local Rowset &Level1;
Local Row &Row;
Local Rowset &Level2;
Local integer &i, &j;

/* Get the Level 1 rowset from the component buffer */
&Level1 = GetLevel0()(1).GetRowset(Scroll.LEVEL1_RECORD);

/* Loop through each row in Level 1 */
For &i = 1 To &Level1.ActiveRowCount
   &Row = &Level1(&i);

   /* Access a field in Level 1 */
   MessageBox(0, "", 0, 0, "Level 1 Field Value: " | &Row.LEVEL1_RECORD.FIELDNAME.Value);

   /* Now get the Level 2 rowset under this row */
   &Level2 = &Row.GetRowset(Scroll.LEVEL2_RECORD);

   /* Loop through Level 2 rows */
   For &j = 1 To &Level2.ActiveRowCount
      MessageBox(0, "", 0, 0, "Level 2 Field Value: " | &Level2(&j).LEVEL2_RECORD.FIELDNAME.Value);
   End-For;
End-For;

Final validations before save; stop save with an error if needed.

/* SavePreChange: Validate required business rules */
Local boolean &ok = True;

If All(REC.START_DT) And All(REC.END_DT) Then
   If REC.END_DT < REC.START_DT Then
      &ok = False;
      Error ("End Date cannot be before Start Date.");
   End-If;
Else
   &ok = False;
   Error ("Start Date and End Date are required.");
End-If;

/* Example: duplicate check */
Local SQL &sql = CreateSQL("SELECT 1 FROM PS_REC WHERE KEY1 = :1 AND KEY2 = :2",
                           REC.KEY1, REC.KEY2);
If &sql.Fetch() Then
   &ok = False;
   Error ("Duplicate key exists for this record.");
End-If;

Batch logic with SQLExec and rowset processing.

/* AE Step: Load and process */
Local Rowset &rs = CreateRowset(Record.REC);
&rs.Fill("WHERE PROCESS_FLG = 'N'");

Local number &i;
For &i = 1 To &rs.ActiveRowCount
   Local Row &r = &rs(&i);
   Local string &key = &r.REC.KEY.Value;

   SQLExec("UPDATE PS_REC SET PROCESS_FLG = 'Y' WHERE KEY = :1", &key);

   /* Log message */
   MessageBox(0, "", 0, 0, "Processed row %1", &key);
End-For;

Quick selects and updates; use bind variables for safety.

Local number &cnt;
SQLExec("SELECT COUNT(*) FROM PS_REC WHERE STATUS = :1", "OPEN", &cnt);

If &cnt > 0 Then
   SQLExec("UPDATE PS_REC SET FLAG = 'Y' WHERE STATUS = :1", "OPEN");
End-If;

Programmatic data entry using CI properties and methods.

Local ApiObject &ci = GetCI("CI_REC");
&ci.KEY1 = "A123";
&ci.KEY2 = 10;

&ci.FIELD_A = "HELLO";
&ci.FIELD_B = %Date;

If &ci.Save() Then
   MessageBox(0, "", 0, 0, "Saved CI for %1", &ci.KEY1);
Else
   Error ("CI save failed: " | &ci.ErrorText);
End-If;