In this article, I will walk through all the steps you need to take to be able to create your own QbQuery API endpoint .
When to Create an QbQuery API ?
In general, consider an API if:
-
- Your data set is large, making download via FTP unwieldy or resource-intensive.
-
- Your users will need to access your data in real time, such as for display on another website or as part of an application.
-
- Your data changes or is updated frequently.
-
- Your users only need access to a part of the data at any one time.
-
- Your users will need to perform actions other than retrieve data, such as contributing, updating, or deleting data.
Architecture Flow
Prerequisites
-
- APM Tenant (aka ProducerPro)
-
- Authentication : User to have System API
-
- Write your own SQL statements to get results
-
- Test it with Postman
Authentication
-
- System APIs Feature must be enabled.
-
- Specified credentials (user/password) must reference a valid Producer Manager User.
-
- Producer Manager User must have access to the System API’s AppArea. (Admin > Users).
-
- Client IP address must be valid based on the system.api.ip.whitelist option configuration. If this option is not specified, any Client IP address is valid.
Now, Lets test it to get CaldsessionId
|
URI Path |
/REST/auth/login |
|
HTTP Method(s) |
POST |
|
Request Parameters |
u – User ID of Producer Manager user (required) p – Password of Producer Manager user (required) |
|
Return MIME Type(s) |
application/xml (default) application/json |
|
Response Codes |
200 OK – Login successful. 403 Forbidden – Authentication failed. 500 Internal Server Error – Unexpected runtime error occurred. See Producer Manager Application Log. |
cURL Example
curl --request POST 'http://tenantid-pmpro-uat.callidusondemand.com/PM/REST/auth/login'
--header 'Content-Type: application/x-www-form-urlencoded'
--data-urlencode 'u=USERNAME'
--data-urlencode 'p=PASSWORD'
Note : Upon successful authentication, the response contains the CaldSessionId value. The CaldSessionId must be included on the HTTP request header for subsequent API calls.
Prepare your own SQL query in SQL Workbench
Example is below
Test the results for your query
Finally, SQL formation looks like this…
select p.producerid as ProdNo,sub.Producerno,
pv.producertype from ProducerVer pv
join Producer p on p.producerno = pv.producerno
join (select max(ProducerVerno) as pvno,ProducerNo
from producerver
${rowLevelJoins}
where
${rowLevelAnd}
prosta <> 9
group by ProducerNo)sub on sub.pvno = pv.producerverno
and p.producerid= '${ProdNo}'
Go to Integration > Configuration > QbQuery
Create your own QbQuery
QbQuery SQL
Summary of QbQuery
Now you can see your newly created
|
URI Path(s) |
/REST/query/run |
|
HTTP Method(s) |
GET |
|
Request Parameters |
qbqueryid – The QBQueryID of the query you wish to run. “System API” should be defined as one of the query contexts to allow the QBQueryID accessible in the API. maxrows – Optional parameter indicating that the query should be limited to the maximum number of rows. Query-specific Parameters – If the query to execute includes parameters, pass these by name as indicated within the query definition. |
|
Return MIME Type(s) |
application/xml (default)Z application/json |
|
Response Codes |
200 OK – QBQuery executed successfully. Does not necessarily mean that rows were found. 400 Bad Request – Invalid QBQueryId or values were not supplied for one or more of the required query parameters. 403 Forbidden – Invalid session or user not authorized to use this API. 500 Internal Server Error – Unexpected runtime error occurred. See the Producer Manager Application Log. |
cURL Example
This example runs the QBQuery with the QBQueryId value of “testityourself”
curl --request
GET 'https://tenantid-pmpro-uat.callidusondemand.com/PM/REST/query/run?qbqueryid=testityourself&ProdNo='000253'
--header 'CaldSessionId: A-hsbsa73le78rdcji9kosubi'
--header 'Content-Type: application/xml'
Postman Example
Finally, Your QbQuery API’s should bring value both to your organization and its intended users.
