(2024.2) SQL API Endpoint

1 Using POST Calls
2 SQL End Point
3 Using the Data Dictionary
4 Sample SQL Call
5 Sample Return
6 Classes of Service

The PCR-360 API offers the ability to send a SELECT query to retrieve any data the system offers. It is important to note that this functionality is limited to only SELECT statements and no data can be modified (UPDATE statements) or destroyed (DELETE statements). Having the ability to SELECT any data that is required is a very powerful tool. This allows the API to be more flexible when retrieving data than any standard endpoint.

To use the SQL endpoint, make a POST request to the SQL endpoint using one of the three possible data formats (JSON, XML, or PHP). This may seem odd as retrieving data is usually done via a GET request, however, SQL statements can become quite long and, to avoid issues with URL maximum lengths, we have decided to utilize POST requests for this functionality.

Click the Headers link for more information on the API Header. Click the Making a Request link for the basics of initiating a request.

Using POST Calls

Using POST Calls

POST requests will ignore parameters supplied as a query string and need any additional parameters in the request Body.
To configure the number of Pages/Results returned by the API (this is most useful for the SQL Endpoint), you can send these as additional Parameter's in the Body.
API fields are not case sensitive, and will always be returned in the lower case format.

SQL End Point

http://DOMAIN/api/API_KEY/sql.OUTPUT

Below is the single parameter for making an SQL POST request.

Parameter	Required	Data Type	Notes

Parameter	Required	Data Type	Notes
sql	yes	string	SQL SELECT statement

Using the Data Dictionary

Please see the Data Dictionary for the structure of the database which will assist in creating queries.

The Data Dictionary can be used as an index to see what data might be related to other tables for ease in building up the SELECT and FROM portions of the statements you desire to build up. Specific data on identified fields can be further filtered in the WHERE portion of the statement.

Sample SQL Call

Call:

POST DOMAIN/KEY/sql.json

Headers:

Key	Value

Key	Value
Content-Type	application/json
Pcr-Html-Encoded	TRUE

Body:

Key	Value

Key	Value
sql	SELECT RECID FROM SERVICES

This call will be expected to RECIDs from the SERVICES Table in JSON format.

Sample Return

{
	"status": "success",
	"page": "1",
	"count": 20,
	"total_count": "324500",
	"results": [
		{
			"RECID": "1"
		},
		{
			"RECID": "2"
		},
		{
			"RECID": "3"
		},
		{
			"RECID": "4"
		},
		{
			"RECID": "5"
		},
		{
			"RECID": "6"
		},
		{
			"RECID": "7"
		},
		{
			"RECID": "8"
		},
		{
			"RECID": "9"
		},
		{
			"RECID": "10"
		},
		{
			"RECID": "11"
		},
		{
			"RECID": "12"
		},
		{
			"RECID": "13"
		},
		{
			"RECID": "14"
		},
		{
			"RECID": "15"
		},
		{
			"RECID": "16"
		},
		{
			"RECID": "17"
		},
		{
			"RECID": "18"
		},
		{
			"RECID": "19"
		},
		{
			"RECID": "20"
		}
	]
}

Classes of Service

Classes of Service values can be retrieved only through the SQL endpoint. Retrieving these values is needed for using the Authentication Token Mode of the API.

Call:

Body:

Key	Value

Key	Value
sql	SELECT RECID, CODE, DESCRIPTION FROM CLASSES_OF_SERVICE

Return: