Fill method: Rowset class

Syntax

Fill([wherestring [, bindvalue] .  .  .])

Description

The Fill method flushes the rowset then reads records from the database into successive rows. The records are read from the database tables corresponding to the primary database record of the scroll into that record. The records are selected by the optional wherestring SQL clause, in which the optional bindvalues are substituted, using the usual bind placeholders (:n).

In general, use this method only with rowsets that were created using the CreateRowset function.

Note:

Because Flush always leaves one row in the scroll, there will be one row in the scroll even if you don’t read any records.

The actual number of records read into the rowset is an optional return of this method.

Note:

This method does not work with Application Engine state records. Also, you cannot use this method in dynamic views.

When this method executes, unlike the Select method, it does not cause any associated PeopleCode to run as part of reading data into the rowset.

Note:

Fill reads only the primary database record. It does not read any related records, nor any subordinate rowset records.

For every record read with the Fill method, if the set language is not the base language and the record has related language records, the Fill method tries to read the related language record and does related language processing.

The Fill method uses a correlation ID of FILL for the table it reads. You must use the correlation ID if you want to refer to the rowset table name as part of the wherestring. You receive a runtime error if you use the table name as a column prefix instead of the correlation ID.

Parameters

Parameter Description

wherestring

Specify a SQL WHERE clause to use for selecting records to fill the rowset. This can be a string or a SQL definition.

bindvalue

Specify optional bind variables to be used with the WHERE clause.

Returns

The number of records read into the rowset.

Example

The following example reads all of the QA_MYRECORD records into a rowset, and returns the number of rows read:

&RS = CreateRowset(RECORD.QA_MYRECORD);
&NUM_READ = &RS.Fill();

The following example reads all of the QA_MYRECORD records that have a MYRECORD field equal to the value of &UVAL into a rowset, and returns the number of rows read:

&NUM_READ = &RS.Fill("where MYRECORD = :1", &UVAL);

To re-use a WHERE clause for the wherestring you can use the SQL repository, and a SQL object. 

&NUM_READ = &RS.Fill(SQL.MYWHERE, &UVAL);

The following example gets all the SET_CNTRL_REC rows related to the row on the page, then updates SETID with the value from the page. Fill is used with a rowset that was created from a message that was just created, that is, a rowset that was unpopulated.

If FieldChanged(SETID) Then
   &MSG = CreateMessage(OPERATION.SET_CNTRL_REC);
   &MSG_ROWSET = &MSG.GetRowset();
   &MSG_ROWSET.Fill("where SETCNTRLVALUE =:1 and REC_GROUP_ID =:2", SETCNTRLVALUE,⇒
 REC_GROUP_ID);

For &I = 1 To &MSG_ROWSET.ActiveRowCount
   &MSG_ROWSET.GetRow(&I).SET_CNTRL_REC.SETID.Value = SETID;
   &MSG_ROWSET.GetRow(&I).PSCAMA.AUDIT_ACTN.Value = "C";
End-For;

&MSG.Publish();

End-If;

When using the Fill method, the IsChanged property of each field in a part rowset is not set to true. Because the fields appear to be unchanged, this can create a problem for publication of data from Message rowsets. A technique to avoid this problem is to create a second rowset and use the CopyTo method to copy the changes to the Message rowset as shown in the following example:

&a = CreateMessage(Operation.MY_ASYNC);
&rs = &a.GetPartRowset(1);

&trs = CreateRowset(Record.PSPMAGENT);
&trs.Fill("where PM_AGENTID >= 12345");

&trs.CopyTo(&rs);
%IntBroker.Publish(&a);

The following example uses a correlation ID for the table in the Fill SELECT, to enable the use of correlated subqueries in the WHERE clause, such as the usual effective-date subquery:

&RSOWNER_NAME = CreateRowset(RECORD.PERSONAL_D00);
&RSOWNER_NAME.Fill("where SETID=:1 AND EMPLID=:2 AND %EffDtCheck(PERSONAL_⇒
D00,FILL,:3)", &SETID, &EMPLID, &EFFDT);

The Fill method implicitly uses Fill as an alias for the Rowset record. This is helpful for complex Fill where clauses with subqueries. 

&oprclasscountries = CreateRowset(Record.SCRTY_TBL_GBL);

&oprclasscountries.Fill("WHERE FILL.OPRCLASS = :1 AND NOT EXISTS (SELECT 'X' FROM⇒
 PS_SCRTY_SEC_GBL GBL2 WHERE GBL2.OPRCLASS = FILL.OPRCLASS AND GBL2.COUNTRY =⇒
 FILL.COUNTRY AND GBL2.PNLNAME = :2)", &OPRCLASS, %Component);

In the following example, the necessary key field values are loaded into a rowset, then the following function is called, and the values are used as part of the Fill method. 

Function FillRS2();
   Local SQL &MySql;
   Local string &MySqlString;
   Local Record &ElemDefnRec;
   Local string &ElemDefnRecName, &fldname;
   Local Rowset &CompRec2RS; 
   /* Build the record object that is used for building the SQL and executing the⇒
 select */
   &ElemDefnRec = CreateRecord(@("Record." | &pkgRecName));
   /* Initialize the SQL string */
   &MySqlString = "%SelectByKey(:1 A)";
   /* Create a SQL to select a rows based on the key fields. */
   For &i = 1 To &ElemDefnRec.FieldCount
      If &ElemDefnRec.GetField(&i).IsKey Then
         &fldname = &ElemDefnRec.GetField(&i).Name;
         &ElemDefnRec.GetField(&i).Value = &CompRec2RS.GetRow(1).GetRecord(@⇒
("Record." | &pkgRecName)).GetField(@("Field." | &fldname)).Value;
      End-If;
   End-For;
   /* Create the SQL and execute the select */
   &MySql = CreateSQL(&MySqlString);
   &MySql.Execute(&ElemDefnRec);
   /* Copy each selected row into the rowset */
   While &MySql.Fetch(&ElemDefnRec)
      &ElemDefnRec.CopyFieldsTo(&CompRec2RS(&CompRec2RS.ActiveRowCount).GetRecord⇒
(1));
   End-While;
End-Function;

Sorting Considerations

Rows come unsorted from the database when using Fill. This is not a problem for SQL server, however, it can be a problem for DB2 for OS/390 and z/OS and Oracle.

See Sort method: Rowset class.

Related Topics