RunToFile method: Query class

Syntax

RunToFile(&PromptRecord, Destination, OutputFormat, MaxRows)

Description

Use the RunToFile method to execute the Query and return the result to the file specified with Destination. 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. You can use the PromptRecord property to obtain this object.

Destination must include the absolute path name of where the file is to be created.

If the specified subdirectory does not exist, this method does not automatically create them for you, and you receive an error.

If you specify HTML as the output format, the PeopleSoft Query style sheet PSQUERYSTYLEDEF is used for formatting the output.

Parameters

Parameter Description

&PromptRecord

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

Destination

Specify the absolute path name of where the files are to be created.

OutputFormat

Specify the format of the data being written to the file. You can use either a constant or a numeric value for this parameter. See below.

MaxRows

Specify the maximum number of rows to be fetched. This parameter takes a numeric value.

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.

The values for OutputFormat can be as follows:

Numeric Value Constant Value Description

2

%Query_PDF

The output is in PDF format.

5

%Query_HTML

The output is in HTML format.

8

%Query_XLS

The output for Excel in HTML format.

14

%Query_TXT

The output for txt in CSV format.

17

%Query_XML_WebRowset

The output is in web rowset XML format.

20

%Query_XML_XmlP

The output is in XMLP format.

Returns

Returns 0 if successful.

Example

To run a query using the query API RunToFile method:

  1. Open the query.

    &aRunQry = %Session.GetQuery();

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

  2. Obtain 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 runtime values, the query can be run, as follows:

    &aRowSet = &aRunQry.RunToFile(&aQryPromptRec, "c:\temp\QueryOutput.html", ⇒
%Query_PDF, 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); 
   End-If; 
   If (&aRunQry.RunToFile(&aQryPromptRec, "c:\temp\" | ⇒
&aRunQry.Name, 3, 0) = 0) Then 
      &strHTML = "Resultset saved into file successfully."; 
   Else 
      &strHTML = "Failed to save Resultset into file."; 
   End-If; 
End-If;

Related Topics