Reporting Web Services: Using the REST Web Services to run a Query

In this article we are going to look at how the REST-based Reporting Web Services works. This allows an HTTP client to run a PeopleSoft query created in “Query Manager” over a web service. I have a blog post on using HTTP Post Running a PeopleSoft Query using the Reporting Web Services which uses the HttpListeningConnector (HTTP POST). We will see that using the REST services is a little more “clean” and requires less overhead on the client. After all REST is best!

The default return of these web services are XML. We will see in the latter section how to return JSON.

Evolution of REST Reporting Web Services

First let’s give some background into what the “Reporting Web Services” are. PeopleTools delivers some functionality called the “Reporting Web Services” which allows a third party to execute a PeopleSoft Query created in PeopleSoft Query Manager over a web service. This query web service can be an easy way for external systems to pull data out of PeopleSoft. You don’t have to create a new web service for each new data extract. You get a functional user to create the query, ensure your query security is setup and communicate the new parameters to the client.

There are several different execution models documented in the Reporting Web Services PeopleBooks. However, we are going to cover only the synchronous method which the query results will be run in real-time and a response will be returned while the client waits on the line. This is the most simple approach and requires the least amount of code for the third party. There are also web services for getting meta-data on queries as well as creating query definitions from outside systems but I don’t think those have common use cases.

• When the “Reporting Web Services” were first released, they were only delivered using standard Service Operations that you posted to the HttpListeningConnector or the PeopleSoftServiceListeningConnector if you wanted SOAP.
• When PeopleTools started to support REST endpoints via the RESTListeningConnector these web services were ported over to the REST model.
• Now there are really two versions of these web services: REST and “Standard”.
• The REST versions are all contained under the QAS_QRY_REST service and the standard/old operations are contained under the QAS_QRY_SERVICE service definition.

In the screenshot below, I searched for any service operations whose service name started with “QAS”, then sorted by the Service Operation name. I also highlighted in yellow the REST versions of the services.

What you will see here is that there are 2 versions of the service operations. The ones that are REST-based typically have “REST_GET” suffix. So for example, you will see the QAS_LISTQUERY_OPR and QAS_LIST_QUERY_REST_GET which are basically the same service. The former being “standard” and the latter being REST.

You will also notice a few that have a “REST_POST” suffix. These are ones that require an HTTP Post as they are creating something in the system. This is in-line with standard REST design principles.

Security Setup

In order to run these web services we need to create a PeopleSoft account that has access to both the query objects and the web services. The details of REST web service authorization are covered in the REST Security section. We are assuming you have read that article.

In this section we assume:

• You have a PeopleSoft user called
  • OPRID: RESTUSER
  • Password: $ecretknock1234
  • This equates to a basic authorization token of Authorization: Basic some-value-that-identifies-you-to-the-server.
• That user ID as permissions to:
  • Web Services Authorizations: Service: QAS_QRY_REST, Service Operation: QAS_EXECUTEQRY_REST_GET
  • Query Permissions: Check - “Only Allowed to run Queries”
  • Query Access Group Permissions - Grant whatever tree levels you need here for the user to be able to access the records on the query.

First Query - No Prompts

First we are going to create a query called USERS_IN_ROLE. It is a simple query that has no prompts and returns one column. Here is the SQL.

SELECT A.ROLEUSER 
    FROM PSROLEUSER A 
    WHERE ( A.ROLENAME = 'Portal Administrator')

Lets call the query web service. Here is the HTTP signature of that web service call.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/USERS_IN_ROLE/XMLP/NONFILE?isconnectedquery=n&maxrows=3 HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0

The response from the server is:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <query numrows="3" queryname="USERS_IN_ROLE" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
        <row rownumber="1">
            <ROLEUSER>
                <![CDATA[AA_USER]]>
            </ROLEUSER>
        </row>
        <row rownumber="2">
            <ROLEUSER>
                <![CDATA[ACA_USER]]>
            </ROLEUSER>
        </row>
        <row rownumber="3">
            <ROLEUSER>
                <![CDATA[ADM_USER]]>
            </ROLEUSER>
        </row>   
    </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

There are a few things to note about the URL.

• My PeopleSoft local node name is: PSFT_CHGDEM
• The request has a path and query string that looks like this: ExecuteQuery.v1/PUBLIC/USERS_IN_ROLE/XMLP/NONFILE?isconnectedquery=n&maxrows=3. Inside that we see that:
  • We are limiting the rows to 3 with the “maxRows” parameters
  • We are telling it what query name to run: USERS_IN_ROLE
  • We want a XMLP data format back.
  • We want a NONFILE response type.

If you contrast this with the “standard”/SOAP method of calling these services you basically get the same result. However, the location of the parameters are completely in the URL path and query string instead of the HTTP Post Body.

REST Service Operation Signatures

How do you know what the parameters are and in what order? When you define a REST service operation in PeopleTools you define a “template” which are the different path and parameters signature combinations. Here is a screenshot of the QAS_EXECUTEQRY_REST_GET parameters. Note that I had to expand the template sections to make them viewable in the grid. If you look at this in your your PeopleSoft environment, not all the text is visible.

Your requests have to match one of theses templates for it to pass into a handler to be processed. In the first example above, we matched on the number 6 template.

Query Example with 1 Prompt

Let’s take this one step farther. Let’s add a query prompt to the equation and see how we can pass that to the web service. First we create a new query called USERS_IN_ROLE_PROMPT. It has the following SQL.

SELECT A.ROLEUSER 
    FROM PSROLEUSER A 
    WHERE ( A.ROLENAME = :1)

The :1 query prompt is defined as “BIND1” which we will need in the HTTP request.

In order for the client to pass the prompts, new query string parameters must be included to pass the bind name and value. We want to pass a value of “Portal Administrator” as the value for “BIND1”. When we map that to the template for the web service, you add in the following query string parameters.

&prompt_uniquepromptname=BIND1,BIND2&prompt_fieldvalue=VALUE1,VALUE2

The request looks like this:

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/USERS_IN_ROLE_PROMPT/XMLP/NONFILE?isconnectedquery=n&maxrows=3&prompt_uniquepromptname=BIND1&prompt_fieldvalue=Portal Administrator HTTP/1.1
Host: demo.cedarhillsgroup.com
Content-Type: application/xml
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0
Cache-Control: no-cache

The output would be:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <query numrows="3" queryname="USERS_IN_ROLE_PROMPT" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
        <row rownumber="1">
            <ROLEUSER>
                <![CDATA[CMALEK]]>
            </ROLEUSER>
        </row>
        <row rownumber="2">
            <ROLEUSER>
                <![CDATA[TESTUSER]]>
            </ROLEUSER>
        </row>
        <row rownumber="3">
            <ROLEUSER>
                <![CDATA[PS]]>
            </ROLEUSER>
        </row>
    </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

If you had more bind variables, you would then just include several key values pairs in this form.

&prompt_uniquepromptname=BIND1&prompt_fieldvalue=VALUE1&prompt_uniquepromptname=BIND2&prompt_fieldvalue=VALUE2

Filter Field Example

If you look at the templates for the QAS_EXECUTEQRY_REST_GET service operation you will see some of them have a “filterfields” parameter. This looks interesting. Let’s see what it does.

First let’s add a few more columns to the query definition. We add here ROLENAME and DYNAMIC_SW.

SELECT A.ROLEUSER, A.ROLENAME, A.DYNAMIC_SW
    FROM PSROLEUSER A 
    WHERE ( A.ROLENAME = :1)

If you run that query via the web service you now get back this result.

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <query numrows="3" queryname="USERS_IN_ROLE_PROMPT" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
        <row rownumber="1">
            <ROLEUSER>
                <![CDATA[CMALEK]]>
            </ROLEUSER>
            <ROLENAME>
                <![CDATA[Portal Administrator]]>
            </ROLENAME>
            <DYNAMIC_SW>
                <![CDATA[N]]>
            </DYNAMIC_SW>
        </row>
        <row rownumber="2">
            <ROLEUSER>
                <![CDATA[TESTUSER]]>
            </ROLEUSER>
            <ROLENAME>
                <![CDATA[Portal Administrator]]>
            </ROLENAME>
            <DYNAMIC_SW>
                <![CDATA[N]]>
            </DYNAMIC_SW>
        </row>
        <row rownumber="3">
            <ROLEUSER>
                <![CDATA[PS]]>
            </ROLEUSER>
            <ROLENAME>
                <![CDATA[Portal Administrator]]>
            </ROLENAME>
            <DYNAMIC_SW>
                <![CDATA[N]]>
            </DYNAMIC_SW>
        </row>
    </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

Let’s imagine that we only care about the ROLEUSER and DYNAMIC_SW field and we do NOT want the ROLENAME back because we are passing it as a parameter and we will already know the value. There is a URI in the service operation that has this parameter: &filterfields={FilterFields*}. It is supposed to allow this this.

PeopleBooks mentions this:

FilterFieldName : List of field names to be returned. This value is case sensitive and must be the unique field name as returned by the service operation QAS_LISTQUERYFIELDS.

You are starting to see this pattern a lot in single page web applications (SPWA) where some javascript will call a REST service that may have different clients. The service may return a lot of data. However, the SPWA may only care about 5 fields out of the 100. Therefore, it asks the server to only return those 5 fields if possible. This filterfields is supposed to follow that same basic approach.

So let’s grant our user RESTUSER access to the QAS_LISTQUERYFIELDS_REST_GET service operation (an exercise for the reader) and execute that web service for our query.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/QueryFields.v1/USERS_IN_ROLE_PROMPT?isconnectedquery=N HTTP/1.1
Host: demo.cedarhillsgroup.com
Accept: application/xml
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0

The response is returns the fields that are defined in our query definition.

<?xml version='1.0'?>
<QAS_LISTQUERYFIELDS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_LISTQUERYFIELDS_RESP_MSG.VERSION_1">
    <QAS_LISTQUERYFIELDS_RESP>
        <PTQASWRK class="R" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_LISTQUERYFIELDS_RESP.VERSION_1">
            <QueryName>USERS_IN_ROLE_PROMPT</QueryName>
            <PTQASFIELDWRK class="R">
                <ColumnNumber>1</ColumnNumber>
                <FieldName>ROLEUSER</FieldName>
                <FieldType>string</FieldType>
                <FieldLength>30</FieldLength>
                <FieldDecimal>0</FieldDecimal>
                <HeadingText>User ID</HeadingText>
                <UniqueFieldName>A.ROLEUSER</UniqueFieldName>
            </PTQASFIELDWRK>
            <PTQASFIELDWRK class="R">
                <ColumnNumber>2</ColumnNumber>
                <FieldName>ROLENAME</FieldName>
                <FieldType>string</FieldType>
                <FieldLength>30</FieldLength>
                <FieldDecimal>0</FieldDecimal>
                <HeadingText>Role Name</HeadingText>
                <UniqueFieldName>A.ROLENAME</UniqueFieldName>
            </PTQASFIELDWRK>
            <PTQASFIELDWRK class="R">
                <ColumnNumber>3</ColumnNumber>
                <FieldName>DYNAMIC_SW</FieldName>
                <FieldType>string</FieldType>
                <FieldLength>1</FieldLength>
                <FieldDecimal>0</FieldDecimal>
                <HeadingText>Dynamic</HeadingText>
                <UniqueFieldName>A.DYNAMIC_SW</UniqueFieldName>
            </PTQASFIELDWRK>
        </PTQASWRK>
    </QAS_LISTQUERYFIELDS_RESP>
</QAS_LISTQUERYFIELDS_RESP_MSG>

So the UniqueFieldName for the user ROLEUSER field is “A.ROLEUSER”. Let’s see what happens when we try to tell the query web service to only return that one field even though the query definition has 3 fields.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/USERS_IN_ROLE_PROMPT/XMLP/NONFILE?isconnectedquery=n&maxrows=3&prompt_uniquepromptname=BIND1&prompt_fieldvalue=Portal Administrator&filterfields=A.ROLEUSER HTTP/1.1
Host: demo.cedarhillsgroup.com
Accpet: application/xml
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0

The response back from the server is:

<?xml version="1.0"?>
<QAS_GETQUERYRESULTS_RESP_MSG xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_GETQUERYRESULTS_RESP_MSG.VERSION_1">
    <query numrows="3" queryname="USERS_IN_ROLE_PROMPT" xmlns="http://xmlns.oracle.com/Enterprise/Tools/schemas/QAS_QUERYRESULTS_XMLP_RESP.VERSION_1">
        <row rownumber="1"/>
        <row rownumber="2"/>
        <row rownumber="3"/>
    </query>
</QAS_GETQUERYRESULTS_RESP_MSG>

What the heck! It looks like a big fat fail whale! I am writing this article using an 8.54 tools release.

It looks like there is a bug there with those filterfield parameters. You can see that 3 rows did come back but there is no data in there. Maybe I used the wrong field name. Just for fun lets try using just “ROLEUSER” instead of “A.ROLESER” to see if maybe we did not use the right field name.

Making that one substitution

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/USERS_IN_ROLE_PROMPT/XMLP/NONFILE?isconnectedquery=n&maxrows=3&prompt_uniquepromptname=BIND1&prompt_fieldvalue=Portal Administrator&filterfields=ROLEUSER HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0
Accept: application/xml

You get this back.

<HTML>
<HEAD>
<TITLE>RESTListeningConnector</TITLE>
</HEAD>
<BODY>The fields received in the fields filter do not match the query (228,5) PT_QAS.PT_QES.QASExecutionService.OnExecute  Name:ValidateQuery  PCPC:57704  Statement:759
Called from:PT_QAS.PT_QES.QASExecutionService.OnExecute  Name:ExecutePSQueryImp  Statement:337
Called from:PT_QAS.PT_QES.QASExecutionService.OnExecute  Name:ExecuteQuerySyncRequest  Statement:272
Called from:PT_QAS.QASRequestHandler.OnExecute  Name:ProcessQASRequest  Statement:30
Called from:QAS_EXECUTEQRYSYNC.RequestHandler.OnExecute  Name:OnR</BODY>
</HTML>

There are a few things to note about this response.

• It looks like “A.ROLEUSER” in the original attempt was correct.
• The response is actually HTML. The response header is Content-Type: text/html; charset=UTF-8 and you can see the HTML source. The request told the server via the accept HTTP request header that she only takes XML. However, HTML was sent back. :-(
• The HTTP Status code returned is actually HTTP/1.1 200 OK which is definitely NOT correct.
  • This scenario is more likely a 400 Bad Request because the request is malformed.

These issues could present a problem with some programmatic access to these web services. As you can see they have some rough edges that Oracle needs to work out.

JSON responses

When these first web services were first delivered they only supported XML encoding. Starting in 8.55, there is support for getting JSON encoding back of the data. To get JSON back, you need ot ask for it using a URL Query string parameter: json_resp=true. Let’s look at a full example.

First I created a public query called UNLOCKED_USERS with the following SQL.

SELECT A.OPRID, A.USERIDALIAS, TO_CHAR(CAST((A.LASTUPDDTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),
 A.LASTUPDOPRID, A.PTALLOWSWITCHUSER 
  FROM PSOPRDEFN A 
  WHERE ( A.ACCTLOCK = 0)</pre>

Now let’s call the QAS web service to execute the query and return JSON. Here is the HTTP Syntax.

GET /PSIGW/RESTListeningConnector/PSFT_CHGDEM/ExecuteQuery.v1/PUBLIC/UNLOCKED_USERS/JSON/NONFILE?isconnectedquery=n&maxrows=3&json_resp=true HTTP/1.1
Host: demo.cedarhillsgroup.com
Authorization: Basic UkVTVFVTRVI6JGVjcmV0a25vY2sxMjM0

The JSON response is header has a Content-Type: application/json; charset=UTF-8. The response body has the following JSON:

{
    "status": "success",
    "data": {
        "query": {
            "numrows": 2,
            "queryname=": "UNLOCKED_USERS",
            "rows": [
                {
                    "attr:rownumber": 1,
                    "A.OPRID": "PS",
                    "A.USERIDALIAS": "",
                    "A.LASTUPDDTTM": "2016-05-24T12:17:29-0700",
                    "A.LASTUPDOPRID": "PPLSOFT",
                    "A.PTALLOWSWITCHUSER": 0
                },
                {
                    "attr:rownumber": 2,
                    "A.OPRID": "PTWEBSERVER",
                    "A.USERIDALIAS": "",
                    "A.LASTUPDDTTM": "2016-07-05T09:34:22-0700",
                    "A.LASTUPDOPRID": "PPLSOFT",
                    "A.PTALLOWSWITCHUSER": 0
                }
            ]
        }
    }
}

Note that the JSON key reference the field names and table aliases which can be super brittle.