REST API Examples: Running SQL through REST

This topic provides a tutorial on running SQL statements directly through REST.

The REST API allows you to run SQL statements and query directly through REST requests.

The examples shown below can be run against the stores_demo database. To create the stores_demo database, run the dbaccesssdemo script in your database server environment.

Example 1: Run a SQL insert statement

To run a SQL statement through REST, you POST a JSON document that includes the SQL statement to execute against the /api/servers/{alias}/databases/{dbname}/sql URI endpoint.

For DML statements, the response will include an n, which is the number of rows inserted, modified, or deleted by the SQL statement, and will also include the response time in milliseconds.

Request
POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql
Request Body
{
   "sql": "INSERT INTO classes VALUES (5, 600, 'Computer Science')" 
}
Response
{
    "n": 1,
    "responseTime": 8
}

Example 2: Run a SQL query with host variables

When running SQL through REST, you can include question marks as place holders in your SQL statement and then provide the list of host variables to be bound to those placeholders.

Request
POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql
Request Body
{
    "sql": "SELECT customer_num, company, city FROM customer where customer_num > ? and state = ?",
    "hostVariables": [
        110,
        "CA"
    ]
}
Response
{
    "results": [
        {
            "customer_num": 111,
            "company": "Sports Center",
            "city": "Sunnyvale"
        },
        {
            "customer_num": 112,
            "company": "Runners & Others",
            "city": "Los Altos"
        },
        ...
    ],
    "hasMore": false,
    "responseTime": 27
}

Example 3: Running a SQL query with limits and skips

Query responses in REST always include the query results as an array of JSON documents, the responseTime in milliseconds, and a hasMore property which indicates whether or not the entire query result was included in the response. When hasMore is false, the response includes the entire set of query results. When hasMore is true, the query results were limited and a subsequent REST request is required to get more of the data. To request the next batch of results, reissue the same REST request with the addition of a skip property.

By default, the REST API server will use a limit of 100 for all queries. This default limit can be configured in the REST configuration file. Each SQL request can also include a limit specific to that particular request. If you want to receive all results for a particular query in a single response, you can set the limit to -1 in the request body.

Request
POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql
Request Body
{
    "sql": "SELECT customer_num, company, city FROM customer",
    "limit": 2
}
Response
{
    "results": [
        {
            "customer_num": 101,
            "company": "All Sports Supplies",
            "city": "Sunnyvale"
        },
        {
            "customer_num": 102,
            "company": "Sports Spot",
            "city": "San Francisco"
        }
    ],
    "hasMore": true,
    "responseTime": 244
}
To get the next batch of results from this query, reissue this request with the skip set to 2.
Request
POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql
Request Body
{
    "sql": "SELECT customer_num, company, city FROM customer",
    "limit": 2,
    "skip": 2
}