RunToRowset method: Query class

Syntax

RunToRowset(&PromptRecord, MaxRows)

Description

Use the RunToRowset method to execute the Query and return the result to a rowset. The query should be an existing query in the database, or it should have been saved using the Save method.

Because a Query may have runtime prompts (that is, criteria defined using Prompt), this method requires those values to be passed in when you execute this method. PromptRecord is a PeopleCode record object containing the prompt values as fields in the record. The PromptRecord property can be used to obtain this object.

Parameters

Parameter Description

&PromptRecord

Specify an instance of a PeopleCode record that contains the runtime prompts and values required for running the query.

MaxRows

Specify the maximum number of rows to be fetched. This parameter takes a numeric value. The total number of rows fetched is the minimum value of this parameter's value, and the application server configuration parameter Max Fetch Size (which is specified in KB.)

The values are:

  • -1 returns all rows regardless of the setting on the Query security profile (MaxRowsFetched).

  • 0 returns the maximum number of rows allowed by the Query security profile.

  • >0 is the limit on the number of rows.

Returns

If successful, the query result is returned as a populated PeopleCode rowset. If the query wasn't successful, the method returns NULL. If unsuccessful, check the PSMessages collection for errors.

Example

To run a query using the query API RunToRowset method:

  1. Open the Query.

    &aRunQry = %Session.GetQuery();

&aRunQry.Open(&sQryName, False, False);

  2. Get the PromptRecord for the Query.

    &aQryPromptRec = &aRunQry.PromptRecord;

    This instance of the PromptRecord can be passed to the PeopleCode Prompt function to prompt the user for the runtime values, as follows:

    &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec);

  3. Run the query.

    Now that you have the prompt values, you can run the query:

    &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0);

  4. Access the data of the rowset.

    You can now manipulate the rowset using the data buffer access methods and properties:

    &aRowSet(&i).GetRecord(1).GetField(&j).Value

The following is a complete sample code example:

   Local Rowset &aRowSet; 
   Local Row &aRow; 
   Local Record &aQryPromptRec; 
   Local Record &aRec; 
   Local ApiObject &aRunQry; 
   &strHTML = ""; 
   &aRunQry = %Session.GetQuery(); 
   If (&aRunQry.Open(&sQryName, False, False) <> 0) Then 
      &strHTML = "Error in opening query"; 
   Else 
      &aQryPromptRec = &aRunQry.PromptRecord; 
      &strQryName = &aRunQry.Name; 
   If &aQryPromptRec <> Null Then 
      &nResult = Prompt(&strQryName | " Prompts", "", &aQryPromptRec); 
      &aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0); 
         If &aRowSet <> Null Then 
               &strHTML = &strHTML | "<table cellpadding='2' ⇒
cellspacing='0' width='90%'>"; 
               &aRec = &aRowSet(1).GetRecord(1); 
               &QrySelOutputFldCol = &aRunQry.QuerySelect.QueryOutputFields; 
               If &QrySelOutputFldCol <> Null Then 
                  &strHTML = &strHTML | "<TR><TD>"; 
                  &strHTML = &strHTML | "<table border='1' cellpadding='2'⇒
 cellspacing='0' width='100%'>"; 
                  &strHTML = &strHTML | "<TR><TH>&nbsp;</TH>"; 
                  For &j = 1 To &QrySelOutputFldCol.count 
                     &strHTML = &strHTML | "<TH><B>" | &QrySelOutputFldCol.Item⇒
(&j).LongName | "</B></TH>"; 
                  End-For; 
                  &strHTML = &strHTML | "</TR>"; 
                  &strHTML = &strHTML | "</TD></TR>"; 
               End-If; 
               &strHTML = &strHTML | "<TR><TD>"; 
               If &aRowSet.RowCount > 0 Then 
                  For &i = 1 To &aRowSet.RowCount 
                     &aRow = &aRowSet(&i); 
                     &strHTML = &strHTML | "<TR>" | "<TD>" | &i | "</TD>"; 
                     For &j = 1 To &aRow.GetRecord(1).FieldCount 
                        &strHTML = &strHTML | "<TD>" | &aRow.GetRecord(1).GetField⇒
(&j).Value | "</TD>"; 
                     End-For; 
                     &strHTML = &strHTML | "</TR>"; 
                  End-For; 
               Else 
                  &strHTML = &strHTML | "No records retrieved."; 
               End-If; 
               &strHTML = &strHTML | "</TD></TR>"; 
               &strHTML = &strHTML | "</TABLE>"; 
               &strHTML = &strHTML | "</TABLE>"; 
            Else 
               &strHTML = "Failed to retrieve result set."; 
            End-If; 
      End-If; 
   End-If;

Related Topics