Query Database

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