The Direct Query feature allows users to 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 requirement do not correspond exactly to an OData request.

Enable Direct Query

Direct Query is disabled by default, but can be enabled for each existing connection in the API Server. To do so, check the box ‘Allow Direct Queries’ in the option for the connection. API Users must also be explicitly authorized to perform Direct query, by checking the box ‘Allow Direct Query’ in the User options. Because of the security implications associated with running any SQL queries against a database, CData recommends administrators be mindful of which users are given access to the Direct SQL Query feature.

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:

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

Parameters

Query parameters can be included 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 query above 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"
        },
        ...
    ]
}