{"type":"doc","content":[{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"toc","parameters":{"macroParams":{"outline":{"value":"true"},"style":{"value":"none"}},"macroMetadata":{"macroId":{"value":"5947f518-42b1-4b48-916d-a4e75e5a00dd"},"schemaVersion":{"value":"1"},"title":"Table of Contents"}},"localId":"2a12bed2-8304-4a49-8c2c-9e7df16d5f7b"}},{"type":"paragraph","content":[{"text":"The PCR-360 API offers the ability to send a ","type":"text"},{"text":"SELECT","type":"text","marks":[{"type":"strong"}]},{"text":" 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 (","type":"text"},{"text":"UPDATE","type":"text","marks":[{"type":"strong"}]},{"text":" statements) or destroyed (","type":"text"},{"text":"DELETE","type":"text","marks":[{"type":"strong"}]},{"text":" statements). Having the ability to ","type":"text"},{"text":"SELECT","type":"text","marks":[{"type":"strong"}]},{"text":" 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.","type":"text"}]},{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"text":"Click the ","type":"text"},{"text":"Headers","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/pcr360wiki/pages/17242971"}}]},{"text":" link for more information on the API Header. Click the ","type":"text"},{"text":"Making a Request","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/pcr360wiki/pages/17242971"}}]},{"text":" link for the basics of initiating a request.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Using POST Calls","type":"text"}]},{"type":"bodiedExtension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"excerpt","parameters":{"macroParams":{"atlassian-macro-output-type":{"value":"INLINE"}},"macroMetadata":{"macroId":{"value":"8c8bf638-d004-4b34-b5a7-a14cda060833"},"schemaVersion":{"value":"1"},"title":"Excerpt"}},"localId":"bce4f346-e7dc-481c-b024-95edcb357286"},"content":[{"type":"paragraph","content":[{"text":"Using POST Calls","type":"text","marks":[{"type":"strong"}]}]},{"type":"bulletList","content":[{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"POST requests will ignore parameters supplied as a query string and need any additional parameters in the request Body.","type":"text"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"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.","type":"text"}]},{"type":"paragraph","content":[{"type":"inlineExtension","attrs":{"extensionType":"com.atlassian.confluence.migration","extensionKey":"__confluenceADFMigrationUnsupportedContentInternalExtension__","text":"","parameters":{"cxhtml":"

Parameter	Data Type	Default	Description
limit	Interger	20	Number of listings to show.
page	Interger	1	Page number to show.

","block-inline":true,"nestedContent":{"type":"doc","content":[{"type":"table","attrs":{"layout":"default"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[88.0]},"content":[{"type":"paragraph","content":[{"text":"Parameter","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[0.0]},"content":[{"type":"paragraph","content":[{"text":"Data Type","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[70.0]},"content":[{"type":"paragraph","content":[{"text":"Default","type":"text"}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[191.0]},"content":[{"type":"paragraph","content":[{"text":"Description","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[88.0]},"content":[{"type":"paragraph","content":[{"text":"limit","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[0.0]},"content":[{"type":"paragraph","content":[{"text":"Interger","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[70.0]},"content":[{"type":"paragraph","content":[{"text":"20","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[191.0]},"content":[{"type":"paragraph","content":[{"text":"Number of listings to show.","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[88.0]},"content":[{"type":"paragraph","content":[{"text":"page","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[0.0]},"content":[{"type":"paragraph","content":[{"text":"Interger","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[70.0]},"content":[{"type":"paragraph","content":[{"text":"1","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[191.0]},"content":[{"type":"paragraph","content":[{"text":"Page number to show.","type":"text"}]}]}]}]}],"version":1},"reason":"A table in list item can't be created or edited in the new editor."}}}]},{"type":"paragraph","content":[{"type":"hardBreak"}]}]},{"type":"listItem","content":[{"type":"paragraph","content":[{"text":"API fields are not case sensitive, and will always be returned in the lower case format.","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"type":"hardBreak"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"SQL End Point","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"http://DOMAIN/api/API_KEY/sql.OUTPUT","type":"text"}]},{"type":"paragraph","content":[{"text":"Below is the single parameter for making an SQL POST request.","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"125b6564-b92d-445a-a114-59c6c25880fe"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[158.0]},"content":[{"type":"paragraph","content":[{"text":"Parameter","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[140.0]},"content":[{"type":"paragraph","content":[{"text":"Required","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[154.0]},"content":[{"type":"paragraph","content":[{"text":"Data Type","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[306.0]},"content":[{"type":"paragraph","content":[{"text":"Notes","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[158.0]},"content":[{"type":"paragraph","content":[{"text":"sql","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[140.0]},"content":[{"type":"paragraph","content":[{"text":"yes","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[154.0]},"content":[{"type":"paragraph","content":[{"text":"string","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[306.0]},"content":[{"type":"paragraph","content":[{"text":"SQL SELECT statement","type":"text"}]}]}]}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Using the Data Dictionary","type":"text"}]},{"type":"paragraph","content":[{"text":"Please see the ","type":"text"},{"text":"Data Dictionary","type":"text","marks":[{"type":"link","attrs":{"href":"/wiki/spaces/pcr360wiki/pages/17242903"}}]},{"text":" for the structure of the database which will assist in creating queries.","type":"text"}]},{"type":"paragraph","content":[{"text":"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 ","type":"text"},{"text":"SELECT","type":"text","marks":[{"type":"em"}]},{"text":" and ","type":"text"},{"text":"FROM","type":"text","marks":[{"type":"em"}]},{"text":" portions of the statements you desire to build up. Specific data on identified fields can be further filtered in the ","type":"text"},{"text":"WHERE","type":"text","marks":[{"type":"em"}]},{"text":" portion of the statement.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Sample SQL Call","type":"text"}]},{"type":"paragraph","content":[{"text":"Call:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"POST DOMAIN/KEY/sql.json","type":"text"}]},{"type":"paragraph","content":[{"text":"Headers:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"e490b1a0-a2e7-455f-9f71-323767f1f1f8"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[404.0]},"content":[{"type":"paragraph","content":[{"text":"Key","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[355.0]},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[404.0]},"content":[{"type":"paragraph","content":[{"text":"Content-Type","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[355.0]},"content":[{"type":"paragraph","content":[{"text":"application/json","type":"text"}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[404.0]},"content":[{"type":"paragraph","content":[{"text":"Pcr-Html-Encoded","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[355.0]},"content":[{"type":"paragraph","content":[{"text":"TRUE","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"Body:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"869948ab-cd29-47ac-a347-2a5ba8a89ec5"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[133.0]},"content":[{"type":"paragraph","content":[{"text":"Key","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[626.0]},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[133.0]},"content":[{"type":"paragraph","content":[{"text":"sql","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[626.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT RECID FROM SERVICES","type":"text"}]}]}]}]},{"type":"paragraph","content":[{"text":"This call will be expected to RECIDs from the SERVICES Table in JSON format.","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Sample Return","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"{\n\t\"status\": \"success\",\n\t\"page\": \"1\",\n\t\"count\": 20,\n\t\"total_count\": \"324500\",\n\t\"results\": [\n\t\t{\n\t\t\t\"RECID\": \"1\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"2\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"3\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"4\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"5\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"6\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"7\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"8\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"9\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"10\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"11\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"12\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"13\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"14\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"15\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"16\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"17\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"18\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"19\"\n\t\t},\n\t\t{\n\t\t\t\"RECID\": \"20\"\n\t\t}\n\t]\n}","type":"text"}]},{"type":"heading","attrs":{"level":2},"content":[{"text":"Classes of Service","type":"text"}]},{"type":"paragraph","content":[{"text":"Classes of Service values can be retrieved only through the SQL endpoint. Retrieving these values is needed for using the ","type":"text","marks":[{"type":"textColor","attrs":{"color":"#1f2326"}}]},{"text":"Authentication Token Mode","type":"text","marks":[{"type":"textColor","attrs":{"color":"#1f2326"}},{"type":"link","attrs":{"href":"https://pcr360.atlassian.net/wiki/x/UogQAQ"}}]},{"text":" of the API.","type":"text","marks":[{"type":"textColor","attrs":{"color":"#1f2326"}}]}]},{"type":"paragraph","content":[{"text":"Call:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"POST DOMAIN/KEY/sql.json","type":"text"}]},{"type":"paragraph","content":[{"text":"Body:","type":"text"}]},{"type":"table","attrs":{"layout":"default","width":1800.0,"localId":"b648e457-88a7-4620-9b13-38829008f429"},"content":[{"type":"tableRow","content":[{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[71.0]},"content":[{"type":"paragraph","content":[{"text":"Key","type":"text","marks":[{"type":"strong"}]}]}]},{"type":"tableHeader","attrs":{"colspan":1,"rowspan":1,"colwidth":[688.0]},"content":[{"type":"paragraph","content":[{"text":"Value","type":"text","marks":[{"type":"strong"}]}]}]}]},{"type":"tableRow","content":[{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[71.0]},"content":[{"type":"paragraph","content":[{"text":"sql","type":"text"}]}]},{"type":"tableCell","attrs":{"colspan":1,"rowspan":1,"colwidth":[688.0]},"content":[{"type":"paragraph","content":[{"text":"SELECT RECID, CODE, DESCRIPTION FROM CLASSES_OF_SERVICE","type":"text","marks":[{"type":"textColor","attrs":{"color":"#1f2326"}}]}]}]}]}]},{"type":"paragraph","content":[{"text":"Return:","type":"text"}]},{"type":"codeBlock","attrs":{"language":"sql"},"content":[{"text":"{\n \"status\": \"success\",\n \"page\": 1,\n \"count\": 3,\n \"total_count\": \"3\",\n \"results\": [\n {\n \"RECID\": \"1\",\n \"CODE\": \"0\",\n \"DESCRIPTION\": \"First Class\"\n },\n {\n \"RECID\": \"2\",\n \"CODE\": \"9\",\n \"DESCRIPTION\": \"Second Class\"\n },\n {\n \"RECID\": \"3\",\n \"CODE\": \"100\",\n \"DESCRIPTION\": \"Third Class\"\n }\n ]\n}","type":"text"}]},{"type":"extension","attrs":{"layout":"default","extensionType":"com.atlassian.confluence.macro.core","extensionKey":"children","parameters":{"macroParams":{"style":{"value":"h6"},"sort":{"value":"title"}},"macroMetadata":{"macroId":{"value":"49fc0618-4e3d-40c8-92c0-5e0b87f43c92"},"schemaVersion":{"value":"2"},"title":"Child pages (Children Display)"}},"localId":"406739a3-c6b4-43fc-994c-d7372f8d8fee"}}],"version":1}

Browser not supported