SWS Configuration

Before configuring a new web service there are a few things to think through. We will cover them here at a high-level then go into detail in the subsequent sections.

• URL “Path” - A REST API is generally laid out with an intentionally designed URL structure that manifests in the URL Paths. In SWS, the URL path will be used to find the SQL statement to run. Thinking about the path hierarchy gives the API clients a good structure to reason about. When reading the paths from left to right, they start out broad and narrow down the requested. The URL paths for all SWS configured services will be global to the target database. A URL Path structure could be something like this:
  • {prefix}/person/{emplid}
    • This output information about a person.
  • {prefix}/person/{emplid}/phone
    • This would output phones for a person.
  • {prefix}/person/{emplid}/address/
    • This would output addresses for a person.
  • {prefix}/person/{emplid}/address/{address_type}}
    • This would output a specific address type for a person.
  • {prefix}/security/users/{oprid}/
    • This would output general information on a specific OPRID.
  • {prefix}/security/users/{oprid}/roles/
    • This would output roles a user was a member of.
• SQL - The SWS code looks up a configured SQL or PsoftQL statement, applies SQL criteria from the request, executes the query, encodes the data, and returns it to the API client. So you need to plan the SQL or PsoftQL statement carefully. The API client can send in parameters in the URL, query string, or headers that bind to SQL or PsoftQL statement criteria. You need to think through what parameters you need to pass and how you want the client to pass them. We will have many examples of this later in this document.
• Output field names - When using SQL, You can define the output field names that map to the SQL columns returned. These will be the field names in the output encoding. If you don’t provide output field names then the data is returned with generic “COLUMN1”, “COLUMN2”, “COLUMNx” naming convention. This will not be helpful to the client. For PsoftQL the fields output encoding field names match the underlying PeopleSoft record field names.
• Security - Security is always very important with PeopleSoft data as the database holds very sensitive information. SWS is based on the PeopleSoft REST services. The only viable authentication mechanism for REST based services is “Basic Authentication” which is tied to a PeopleSoft OPRID and password stored in PSOPRDEFN. There is a very thorough document on REST Security in our Integration broker book. That should be your reference on how REST authentication works and the best practices.

Configuring a new web service

Configuring a new web service in SWS is easy and fast. You can deploy a new SQL or PsoftQL statement as a new web service in a few minutes. We will walk through all the configuration sections in this section. In later sections, we will show how to create different web services in order to give you some ideas of how this can be used.

Here is a screenshot of the SWS configuration page for a simple person example. Let’s go through each configuration at a high level. Then in later sections we will drill into the detailed functionality offered.

• Unique Identifier: This is a system generated GUID that will be generated at save time.
• Description: This is the description of the configuration.
• URL Path: This field is very important and will map to the full URL that a client will use to target your web service.
• Active: Allows you to easily turn on or off the configuration.
• Notes: This is a section for you to add notes about this web service. This might be internal notes or links to documentation or development tickets.
• SQL Statement Text: This is the section where you enter the SQL statement that will run.
  • There is some special syntax for binding variables from the client request to the SQL statement. We will cover that detail in the next section.
• Row Limit: Optional row limit. You can specific a non-zero number here and the handler will stop the output after that number of output rows has been encoded.
• Default Encoding: Used to set a default encoding scheme if the client does not include one in the request. All SWS web services automatically handle JSON, CSV and XML encodings.
• Include metadata in Response: If checked then some additional information about the SWS request and response will be encoded. We will show some examples of this shortly.
• (danger) Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”. Please read the Caching article before enabling this. There are some hidden dangers.
• Parameters Grid: This grid allows you to configure how the client passes parameters that are substituted into the SQL. If your SQL does not have any variables provided by the client, there will be no rows here. We will cover this in depth shortly.
• Output Fields Grid: This grid is where you name the SQL columns that show up in the encoding section. For JSON and XML encodings, the values here will be the names of the properties and nodes. For CSV, these will be the header values.
• Allowed Permission Lists Grid: This grid is where you configure SWS to tell what permission lists are authorized to run this SQL statement. There is a detailed security section below that documents how to use this and setup API client users.
• Copy & Delete - Inside this group box are buttons to clone/copy the current configuration or to delete it.
• DMS Export - Inside this group box is a generated DMS script template that can be used to export your configuration between databases.
• HTTP Info Page - This page gives you both HTTP and curl examples on how to invoke the service operation. This removes the guesswork and speeds up the testing cycle.