Query Database

API Type

REST

HTTP Method

POST

Description

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

URL Format (On Premises)

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

URL Format (AgilePoint for Azure)

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

Request Parameters

Name	Description
None	Not Applicable

Request Body Properties

Name	Description
sql	Definition: The where clause of the SQL statement you want to query. Type string Allowed Values: The where clause of a SQL statement.

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 sql) {

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

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

  JSONObject postData = new JSONObject();
  try {
    postData.put("sql", sql);

    return ops.POSTMethod(URI, postData.toString());
  } catch (JSONException e1) {
    e1.printStackTrace();
  }
  return "";
}

C# Code Example

public string QueryDatabase()
  {
    string URI = "http://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);
  }

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>DEMO3\Administrator</PERFORMER>
   <DESCRIPTION>Core.FailedUncheckOut.Ownership</DESCRIPTION>
   <STATUS>Failed</STATUS>
   </Table>
  </DataSet>"

Supported Versions

AgilePoint BPMS v5.0 R2 and higher