RunToString method: Query class

Syntax

RunToString(&PromptRecord, ChunkSize, OutputFormat, MaxRows)

Description

Use the RunToString method to execute the query and return the result as a formatted string.

When “chunking” is active, RunToString is intended to be called in a recursive fashion until the result set has been completely traversed. A Boolean property, MoreRowsAvailable, is included in the Query class to control recursive execution of this method.

See MoreRowsAvailable property: Query class.

Parameters

Parameter Description

&PromptRecord

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

ChunkSize

Specify the desired chunking size in characters as a number. 0 means no chunking.

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 absolute limit on the number of rows.

The values for Output_Format 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 text 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

A string containing the formatted output. If an error occurs, an empty string will be returned.

If there are no errors, but no data is in the query result set, the string will have system-defined header and footer information, but no rows will be present.

Example

The following example runs a query without chunking.

&queryname = "XRFWIN";

rem &querytype = %Query_XLS;
rem &querytype = %Query_PDF;
rem &querytype = %Query_HTML;
rem &querytype = %Query_TXT;
&querytype = %Query_XML_XmlP;
rem &querytype = %Query_XML_WebRowset;
&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(&queryname, False, False) <> 0) Then
   MessageBox(0, "", 0, 0, "Error opening query");
Else
   &aQryPromptRec = &aRunQry.PromptRecord;
   &chunksize = 0;
   &filename = "C:\QueryWork850\output\" | &querytype | "_runtostring.xml";
   &resultString = &aRunQry.RunToString(&aQryPromptRec, &chunksize, &querytype, 0);
   &myfile = GetFile(&filename, "w", "UTF8", %FilePath_Absolute);
   &myfile.writestring(&resultString);
   &myfile.close();
   MessageBox(0, "", 0, 0, "Success!");
End-If;

The following example runs a query with chunking.

&queryname = "XRFWIN";
rem &querytype = %Query_XLS;
rem &querytype = %Query_PDF;
rem &querytype = %Query_HTML;
rem &querytype = %Query_TXT;
&querytype = %Query_XML_XmlP;
rem &querytype = %Query_XML_WebRowset;
&aRunQry = %Session.GetQuery();
If (&aRunQry.Open(&queryname, False, False) <> 0) Then
   MessageBox(0, "", 0, 0, "Error opening query");
Else
   &aQryPromptRec = &aRunQry.PromptRecord;
   &counter = 0;
   &chunksize = 1000;
   Repeat
      &counter = &counter + 1;
      &filename = "C:\QueryWork850\output\" | &querytype | &counter | ⇒
"_runtostring.xml";

      &resultString = &aRunQry.RunToString(&aQryPromptRec, &chunksize, ⇒
&querytype, 0);      
      If (Len(&resultString) > 0) Then
         &myfile = GetFile(&filename, "w", "UTF8", %FilePath_Absolute);
         &myfile.writestring(&resultString);
         &myfile.close();
      Else
         /* edge condition; if there are no more rows AND the */
         /* returned string is empty this is not an error.    */
         If (&aRunQry.MoreRowsAvailable) Then
            /* we have an error... Yikes! */
            MessageBox(0, "", 0, 0, "Something bad has happened!");
         Else
            /* no worries, just ignore it */
         End-If;
      End-If
   Until (Not &aRunQry.MoreRowsAvailable);
   MessageBox(0, "", 0, 0, "Success!");
End-If;

Related Topics