Tip 063: Running a PS Query in PeopleCode

PeopleCode | Opening and accessing the results of a PS Query in PeopleCode.

<< Previous | Next >>

A. PS Query: Making Life Easier

One of your system users may have set up a relatively complex PS Query. This user then makes a request for the query logic to be included as part of a report or process. You could of course reverse engineer the PS Query to create an equivalent view or SQL object, but such an approach is fraught with danger. It is easy to make a small mistake, which could prevent the entire query from working as desired. Also, if the user makes a change to the PS Query in the future, you then have to update the program to cater for the new logic.

A better option is to continue using the existing PS Query, but call the query directly in PeopleCode. This can be done via the code samples below.

 

B. PS Query Example – No Prompt

We start with the simpler of the two examples. The following code will open and execute a query containing no field prompts. Provided the user has security access to the query, the ‘RunToRowset’ method will populate the specified rowset with all the selected results. This can then be processed as a normal standalone rowset.

Local Object &Session = %Session;
Local Object &PageQuery = &Session.GetQuery();
Local Rowset &Results_RS;

/* Check the user has access to the query */      
If &PageQuery.Open("QUERY_NAME", True, False) = 0 Then
   &Results_RS = &PageQuery.RunToRowset(Null, 0);
Else
   Error "No access to query QUERY_NAME";
End-If;

The query ‘Open’ method has three parameters: (i) the query name, (2) whether the query is public or private (True is public, False is private), and (iii) an update indicator (this field is required, but currently not used).

The ‘RunToRowset’ method has two parameters: (i) the prompt record field (null in this case), and (ii) the maximum number of rows to retrieve (0 means no limit).

 

C. PS Query Example – With Prompts

Here’s an extended version of the code above. In this case the PS Query has three prompts, so a record object is used to populate the prompt values before the query is run:

Local Object &Session = %Session;
Local Object &PageQuery = &Session.GetQuery();
Local Rowset &Results_RS;

/* Check the user has access to the query */      
If &PageQuery.Open("QUERY_NAME", True, False) = 0 Then
   /* Set query prompts */
   &queryPrompts = &PageQuery.PromptRecord;
   &queryPrompts.OPRID.Value = %OperatorId;
   &queryPrompts.MENUNAME.Value = "MENU_NAME";
   &queryPrompts.PNLNAME.Value = "PAGE_NAME";

   &Results_RS = &PageQuery.RunToRowset(&queryPrompts, 0);
Else
   Error "No access to query QUERY_NAME";
End-If;

 

See Also:

<< Previous | Next >>