Query Class Methods
In this section, we discuss each Query class method in alphabetical order.
Syntax
AddPrompt(PromptName)
Description
The AddPrompt method adds a prompt with the given name to the query definition. This method returns a new QueryPrompt object that you can then use to specify details about the prompt.
Note: Prompt names are not checked for uniqueness. Each new prompt is just added to the list of prompts.
Parameters
Field or Control |
Definition |
---|---|
PromptName |
Specify the name of the new prompt with a string. The length of this parameter is 30 characters. |
Returns
A reference to a QueryPrompt object.
Syntax
AddQuerySelect()
Description
Use the AddQuerySelect method to add the first Query Select statement into the query definition. This method returns the instance of the newly created QuerySelect.
Parameters
None.
Returns
A reference to a QuerySelect object. If the query already contains the Main Select, it returns NULL.
Syntax
AddTrackingURL(URLString)
Description
Use this method to define the base URL used to construct a fully qualified drilling URL. A base URL consists of the following elements:
http://server_name/servlet_name/site_name/portal_name/node_name
For example:
http://example.com:8080/psp/ps_2/EMPLOYEE/EMP_LOCAL
Because a base URL can be set or derived in several ways, the use of AddTrackingURL is required only when the calling program does not have current context to identify the site, portal, and node and the elements of the base URL (portal name, node name, and content type) were not defined when the drilling URL was defined. Specifically, the base URL can be set or derived as follows:
The portal name, node name, and content type can be defined as optional elements of the query drilling URL definition.
The portal name, node name, and content type can be derived from the current context (that is, from the URL of the logged in user who is executing the query).
Finally, these elements can be defined programmatically through AddTrackingURL. If either of the first two bullets is true, then the value specified by AddTrackingURL is ignored.
Note: The string passed in as the base URL is not validated; therefore, it is the application developer’s responsibility to ensure that the value is complete and correctly formatted.
Use this method for batch programs such as Application Engine programs do not operate in the context of a site, portal, and node. Consequently, prior to invoking an Application Engine program, the calling program needs to define and store the base URL so that the Application Engine program can use this stored value with the AddTrackingURL method.
Parameters
Field or Control |
Definition |
---|---|
URLString |
Specify the base URL as a string. The base URL is used to construct a fully qualified drilling URL. |
Returns
None.
Example
The ExecQry step of the PSQUERY Application Engine program invokes the AddTrackingURL method using a value stored in the run control table prior to executing the query.
&aQry = %Session.GetQuery();
If &aQry.Open(PSQUERY_AET.QRYNAME, &bPublic, False) = 0 Then
&aQry.AddTrackingURL(PSQUERY_AET.URL);
...
&Result = &aQry.RunToFile(&rcdQryPrompts, &sOutFile, %OutDestFormat, 0);
End-if;
Syntax
Close()
Description
The Close method closes the query, freeing the memory associated with that object, and discarding any changes made to the query since the last save. The Close method can be used only on an open query, not a closed query. This means you must have opened the query with the Open or Create methods before you can close it. To save any changes, you must use the Save method before using Close.
It’s very important to close your query when you’re finished processing. Canceling out of a page does not close a query. You may receive error messages every other time you run your program if you haven’t closed your queries.
Parameters
None.
Returns
None.
Syntax
CopyPrivateQuery(QueryName, QryType, TargetUserID)
Description
The CopyPrivateQuery method copies the query from the current user to a user specified by the target user ID.
Parameters
Field or Control |
Definition |
---|---|
QueryName |
Name of the query to be copied, as a string. |
QryType |
Specify the type of query. This parameter takes either a numeric or constant value. See below. |
TargetUserID |
The user ID to which the query is to be copied, as a string. |
The values for QryType can be as follows:
Numeric Value |
Constant Value |
Description |
---|---|---|
1 |
%Query_Query |
Find queries of the type Query. |
4 |
%Query_Role |
Find queries of the type Role. |
5 |
%Query_DBAgent |
Find queries of the type Process. |
7 |
%Query_Archive |
Find queries of the type Archive. |
Returns
0 if successful.
Syntax
Create(QueryName, Public, Type, Description, LongDescription)
Description
The Create method creates a new query, based on the parameters passed with the method. The specified query must be a new query.
Warning! If you specify the name of a query that already exists, the existing query is overwritten by the new query.
The Create method can be used only with a closed query, it cannot be used on an open query.
After you create a new query, you don't have to open it with the Open method. The existing query object points to the new query.
Creating a new query doesn't create the query in the database. You must save the query (with the Save method) to commit it to the database.
Parameters
Field or Control |
Definition |
---|---|
QueryName |
Name of the query to be created. This parameter takes a string value. This parameter takes 30 characters. A query name can contain only US ASCII alphabetic and numeric characters, as well as underscores. |
Public |
Specify if the query is public or private. This parameter takes a Boolean value: True if the query is public, False if it's a private query. |
Type |
Specify the type of query. This parameter takes either a numeric or constant value. See below. |
Description |
Specify a short description for the query. This parameter takes a string value. This parameter takes 30 characters. |
LongDescription |
Specify a long description for the query. This parameter takes a string value. The length of this parameter depends on your system database limit for LONG fields. |
The values for Type can be as follows:
Numeric Value |
Constant Value |
Description |
---|---|---|
1 |
%Query_Query |
Query |
3 |
%Query_View |
View |
4 |
%Query_Role |
Role |
5 |
%Query_DBAgent |
Process |
7 |
%Query_Archive |
Archive |
Returns
An integer value: 0 means the query was created successfully.
Example
/* Use the existing session */
&MySession = %Session;
&MyQry = &MySession.GetQuery();
/* create a new query : public type-User */
&Rslt = &MyQry.Create("MYQUERY", True, %Query_Query, "My Query", "My first Query");
If &Rslt = 0 Then
/* Query created successfully */
Else
/* do error processing */
End-If;
Syntax
Delete()
Description
The Delete method deletes the specified query from the database. The Delete method can be used only with an open query, it cannot be used on a closed query. Before you use the Delete method, you must explicitly open the query object to be deleted (with either the Open or Create method.)
Parameters
None.
Returns
An integer value: 0 means the query was deleted successfully.
Syntax
DeletePrompt(PromptNumber)
Description
The DeletePrompt method deletes a prompt from a query definition.
Parameters
Field or Control |
Definition |
---|---|
PromptNumber |
Specify the numeric location in the query definition of the prompt that you want to delete. |
Returns
An integer value: 0 means the query was deleted successfully.
Syntax
FindExpression(ExpressionNumber)
Description
Use the FindExpression method to search the query definition for an expression with the given expression number. Although expressions are associated with the QuerySelect in which they are defined, PeopleSoft Query doesn't qualify expressions by Select number. Therefore, each expression has a unique number across all QuerySelect objects.
Parameters
Field or Control |
Definition |
---|---|
ExpressionNumber |
Specify the numeric value of the expression number to be searched for in the query definition. |
Returns
A QueryExpression object if successful, NULL otherwise.
Syntax
FormatBinaryResultString(&Rowset, Output_Format, Start_Row, End_Row)
Description
Use the FormatBinaryResultString method to generate a string containing the content of the input (the rowset) in XLS format. You can specify the range of rows to be in the rowset to be used as input. The rowset object is created using the RunToRowset method.
Parameters
Field or Control |
Definition |
---|---|
&Rowset |
Specify an already instantiated and populated rowset object containing the query result. This rowset is created using the RunToRowset method. |
Output_Format |
Specify the format of the output. You can specify either a numeric or constant value. See below. |
Start_Row |
Specify the first row in the rowset that you want to use for output. This parameter takes a numeric value. |
End_Row |
Specify the last row in the rowset that you want to use for output. This parameter takes a numeric value. |
The values for Output_Format can be as follows:
Numeric Value |
Constant Value |
Description |
---|---|---|
8 |
%Query_XLS |
The output for Excel in HTML format. |
Returns
A binary object containing the formatted output.
Example
/* Use RowCount, not ActiveRowCount, to get the total number of rows. */
&End = &MyRowset.RowCount;
&FormString = &MyQuery.FormatBinaryResultString (&MyRowset, %Query_XLS, 1, &End);
Syntax
FormatResultString(&Rowset, Output_Format, StartRow, EndRow)
Description
Use the FormatResultString method to generate a string containing the content of the input (the rowset) in HTML, PDF, XLS, or CSV format. You can specify the range of rows to be in the rowset to be used as input. The rowset object is created using the RunToRowset method.
Parameters
Field or Control |
Definition |
---|---|
&Rowset |
Specify an already instantiated and populated rowset object containing the query result. This rowset is created using the RunToRowset method. |
Output_Format |
Specify the format of the output. You can specify either a numeric or constant value. See below. |
Start_Row |
Specify the first row in the rowset that you want to use for output. This parameter takes a numeric value. |
End_Row |
Specify the last row in the rowset that you want to use for output. This parameter takes a numeric value. |
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. |
20 |
%Query_XML_XmlP |
The output is in XMLP format. |
Returns
A string containing the formatted output.
Example
/* Use RowCount, not ActiveRowCount, to get the total number of rows. */
&End = &MyRowset.RowCount;
&FormString = &MyQuery.FormatResultString(&MyRowset, %Query_TXT, 1, &End);
Syntax
GetTreePromptCount()
Description
Use the GetTreePromptCount method to get the number of tree prompts if available in the query.
Parameters
None.
Returns
A number that indicates the count of tree prompts used in the query. -1 in case of an error.
Example
In this example, the ExecQry step of the PSQUERY Application Engine program invokes the GetTreePromptCount() method using a value stored in the run control table prior to executing the query.
&aQry = %Session.GetQuery();
If &aQry.Open(PSQUERY_AET.QRYNAME, &bPublic, False) = 0 Then
&nTreePromptCount = &aQry.GetTreePromptCount();
End-if;
Syntax
Open(QueryName, Public, Update)
Description
The Open method opens the query object specified by the parameters. The Open method can be used only with a closed query, it cannot be used on an open query. You cannot read or set any properties of a query until after you open it.
Considerations for Opening Different Types of Queries
When you use the Open method, the system tries to open queries according to the type of query. The following is the order used for searching for the query to open:
Query (User)
View
Role
Process
Archive
All query names are unique. You can't have two queries with the same name, just of different types.
Parameters
Field or Control |
Definition |
---|---|
QueryName |
Specify the name of the query to be opened. You must specify an existing query. This parameter takes a string value. |
Public |
Specify if the query is public or private. This parameter takes a Boolean value: True if the query is public, False if it's a private query. |
Update |
This parameter is required, but is unused in this release. You must specify a either True or False. |
Returns
An integer value: 0 means the query was opened successfully.
Syntax
Rename(NewQueryName)
Description
The Rename method renames the existing query definition with NewQueryName. The Rename method can be used only with an open query, it cannot be used on a closed query. Before you use the Rename method, you must explicitly open the query object to be renamed (with either the Open or Create method.)
Note: The Rename method takes place immediately. You don't have to save the query for the rename to occur.
Parameters
Field or Control |
Definition |
---|---|
NewQueryName |
Specify the new name for the existing query. The maximum length of this parameter is 30 characters. |
Returns
An integer value: 0 means the query was renamed successfully.
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.
Related Links
Parameters
Field or Control |
Definition |
---|---|
&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:
|
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:
Open the query.
&aRunQry = %Session.GetQuery(); &aRunQry.Open(&sQryName, False, False);
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);
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);
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;
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
Field or Control |
Definition |
---|---|
&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:
|
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:
Open the Query.
&aRunQry = %Session.GetQuery(); &aRunQry.Open(&sQryName, False, False);
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);
Run the query.
Now that you have the prompt values, you can run the query:
&aRowSet = &aRunQry.RunToRowset(&aQryPromptRec, 0);
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> </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;
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.
Parameters
Field or Control |
Definition |
---|---|
&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:
|
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", %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", %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;
Syntax
Save()
Description
The Save method writes any changes to the existing query to the database.
The Save method can be used only on an open query, not on a closed query. This means you must have opened the query with the Open method before you can save it.
The query object remains open after executing Save. You must execute the Close method on the object before it is closed and the memory freed.
Note: If you’re calling the query API from an Application Engine program, the data won’t actually be committed to the database until the Application Engine program performs a COMMIT.
Parameters
None.
Returns
An integer value: 0 means the query was saved successfully.
Syntax
SetTrackingURL(ExpressionText, ExpressionNumber)
Description
Use the SetTrackingURL method to re-establish the drilling URL if the current execution context is different from the context in which the drilling URL was initially set.
For example, a drilling URL is set as a query expression by the program that executes the query. After query execution, a different program—an iScript program—allows the user to download the query results to an Excel spreadsheet. This iScript program needs to include the drilling URL in the spreadsheet data. In order for the iScript program to have access to the drilling URL query expression values, these values must be defined as global objects by the program that executes the query. Then, the iScript program can re-establish the drilling URL by calling the SetTrackingURL method.
Parameters
Field or Control |
Definition |
---|---|
ExpressionText |
Specify the drilling URL as a string by reference to an established query expression. |
ExpressionNumber |
Specify the drilling URL as a numeric value by reference to an established query expression. |
Returns
None.
Example
If &rsURLList <> Null And
&rsURLList.ActiveRowCount >= 1 And
All(&rsURLList(1).QRY_URL_WRK.QRYCRIT1EXPRTEXT.Value) Then
For &nCount = 1 To &rsURLList.ActiveRowCount;
&rRecordExpr = &rsURLList(&nCount).QRY_URL_WRK;
&QryObj.SetTrackingURL(&rRecordExpr.QRYCRIT1EXPRTEXT.Value, &rRecordExpr.QRYCRIT1EXPRNUM.Value);
End-For;
End-If;