Query Database

API Type

REST

HTTP Method

POST

Description

Queries the database with a valid SQL query and returns the dataset as a string in the XML format.

Good to Know

• If trustedAuthorization is set to False, this method will not execute unless a special access control is turned on.

  For more information, refer to Why Do I Get an Access Denied Error for an API Method Call?.

  ​

URL Format (OnPremises, Private Cloud)

https://[domain]:[port]/AgilePointServer/Workflow/QueryDatabase

URL Format (OnDemand)

https://[domain]:[port]/AgilePointService/Workflow/QueryDatabase

Request Parameters

Request Body Properties

JavaScript Code Example

$("#btnQueryDatabase").click(function () {
  var JSONObject = {
    sql: "SELECT * FROM WF_AUDIT_TRAILS where CATEGORY = 0
                AND PURPOSE='Check-in process definition'" 
  };
  $.ajax({
    url: "https://mydomain:9011/AgilePointServer/Workflow/QueryDatabase",
    data: JSON.stringify(JSONObject)
  });
});

Java Code Example

public String QueryDatabase() {

  String URI = "https://mydomain:9011/AgilePointServer/Workflow/QueryDatabase";

  HTTPOperations ops = new HTTPOperations(domain, userName, 
                       password, appID, locale);

  JSONObject postData = new JSONObject();
  
    postData.put(
      "sql",
      "SELECT * FROM WF_AUDIT_TRAILS where CATEGORY = 0 AND 
      PURPOSE='Check-in process definition'");

    return ops.POSTMethod(URI, postData.toString());
  return "";
}

C# Code Example

public string QueryDatabase()
  {
    string URI = "https://mydomain:9011/AgilePointServer/Workflow/QueryDatabase";

    string jsonRequestdata = "{
      \"where\":\"SELECT * FROM WF_AUDIT_TRAILS where CATEGORY = 0 AND 
                           PURPOSE='Check-in process definition'\"}";

    HTTPOperations ops = new HTTPOperations(domain, this.userName, 
                         password, appID, locale);

    return ops.POSTMethod(URI, jsonRequestdata);
  }

cURL Code Example

curl --header "Content-Type: application/json" 
                         --header "Authorization:Basic QVAtNDI0XEFkbWluOnBhc3M=" 
                         --request POST 
                         --data {\"ColumnName\":\"CATEGORY\",
                         \"Operator\":\"EQ\",
                         \"WhereClause\":\"8\",
                         \"IsValue\":\"true\"} 
                         https://mydomain:9011/AgilePointServer/Workflow/QueryDatabase

Output

An XML string that contains the dataset with the results of the database query.

JSON Response Body Example

  "<DataSet>
   <Table>
   <CATEGORY>0</CATEGORY>
   <PURPOSE>Check-in Process Definition</PURPOSE>
   <DATE_OCCURRED>2014-02-13T13:11:59.36</DATE_OCCURRED>
   <PERFORMER>mydomain\Administrator</PERFORMER>
   <DESCRIPTION>Core.FailedUncheckOut.Ownership</DESCRIPTION>
   <STATUS>Failed</STATUS>
   </Table>
  </DataSet>"

Supported Versions

AgilePoint v5.0 R2 and higher

Code Examples in the AgilePoint NX Documentation

The AgilePoint NX Product Documentation is intended as a basic reference to help you understand how to complete basic coding tasks, such as make API or JavaScript method calls. Code examples that show specific use cases, the solutions to specific business problems, or detailed implementation scenarios are outside the scope of the AgilePoint NX Product Documentation. For specific and/or advanced types of examples that may better meet your requirements, AgilePoint provides several resources:

• AgilePoint Community Forums - A free, AgilePoint-moderated, crowd-sourcing user forum where you can ask questions about specific techniques, the solutions to use cases, workarounds, or other topics that may not be covered in the Product Documentation.
• Professional Services - If you can not find the information you need for your specific business problem, mentoring is available through AgilePoint Professional Services.
• Personalized Training - AgilePoint can provide personalized training for your organization. To request personalized training, contact AgilePoint Sales.