Version 23.0.9145

• Enable Direct Query
• Send a Custom Query
  • POST data
  • Parameters
  • Sample Response

The Direct Query feature lets users send their own SQL queries to the data sources. The SQL query is then handled by the underlying provider, which returns the requested information in a JSON formatted-response. This can be useful for cases where the data requirements do not correspond exactly to an OData request.

Enable Direct Query

Direct Query is disabled by default, but can be enabled for each existing API Server connection. To enable it, check the Allow Direct Queries box in the connection options. You must also explicitly authorize users to perform Direct Query by checking Allow Direct Query in the User dialog. Because of the security implications associated with running SQL queries against a database, be mindful of which users have access to Direct Query.

Send a Custom Query

Once Direct Query is enabled on a connection, users can submit SQL queries directly to the api endpoint query.rsc\<ConnectionName>.

POST data

The SQL query must be passed as POST data in a JSON object, as shown in the following example:

POST http://MyServer:MyPort/query.rsc/MyConnection
{
    "query": "SELECT UserID, Name, LastModified FROM Users"
}

Parameters

You can include query parameters in the POST data:

POST http://MyServer:MyPort/query.rsc/MyConnection
{
    "query": "SELECT UserID, ParentID, Name FROM Users WHERE UserId=@uid AND ParentID=@pid",
    "parameters": "@ui=201, @pid=101"
}

Sample Response

Using the previous query as an example, here is a sample response from the Direct Query endpoint:

{
    "items": [
        {
            "UserId": 1,
            "LastModified": null,
            "Name": "Alice"
        },
        {
            "UserId": 2,
            "LastModified": null,
            "Name": "Bob"
        },
        ...
    ]
}