Using the OneDB Rest API

Once the REST API server is started, you can use any HTTP client to access your data through REST. Authenticate using HTTP Basic Authentication and a user and password known on the HCL OneDB™ database server. Here are some examples to get you started.

The following examples assume the REST API is running on the localhost at port 8080 and that you have a server alias called server1 in your REST configuration file.

Example 1: Get the list of databases on the server

GET http://localhost:8080/api/servers/server1/databases

Response:

[
    "db1",
    "db2",
    "stores_demo"
]

Example 2: Get the list of tables in the “stores_demo” database

GET http://localhost:8080/api/servers/server1/databases/stores_demo/tables

Response:

[
    
    "call_type",
    "catalog",
    "classes",
    "cust_calls",
    "customer",
    ...
]

Example 3: Query for all rows in the “customer” table

POST http://localhost:8080/api/servers/server1/databases/stores_demo/tables/customer/query

Response:

{
    "results": [
        {
            "customer_num": 101,
            "fname": "Ludwig",
            "lname": "Pauli",
            "company": "All Sports Supplies",
            "address1": "213 Erstwild Court",
            "address2": null,
            "city": "Sunnyvale",
            "state": "CA",
            "zipcode": "94086",
            "phone": "408-789-8075"
        },
        ...
    ],
    "hasMore": false,
    "responseTime": 31
}

Example 4: Query for all rows in the “customer” table, sorting by “customer_num” and only including certain fields in the result

POST http://localhost:8080/api/servers/server1/databases/stores_demo/tables/customer/query

Request body:

{
    "fields": [ "customer_num", "lname", "state" ],
    "orderBy": [ { "key": "customer_num", "direction": "asc"} ]
}

Response:

{
    "results": [
        {
            "customer_num": 101,
            "lname": "Pauli",
            "state": "CA"
        },
        {
            "customer_num": 102,
            "lname": "Sadler",
            "state": "CA"
        },
        {
            "customer_num": 103,
            "lname": "Currie",
            "state": "CA"
        },
        ...
    ],
    "hasMore": false,
    "responseTime": 514
}
                    

Example 5: Query the “customer” table with query condition

POST http://localhost:8080/api/servers/server1/databases/stores_demo/tables/customer/query

Request body:

{
    "fields": [ "customer_num", "fname", "lname", "city", "state" ],
    "filter": {
        "op": "and",
        "filters": [
            { "key": "state", "op": "=", "value": "CA"},
            { "key": "customer_num", "op": ">=", "value": 110 }
        ]
    }
}

Response:

{
    "results": [
        {
            "customer_num": 110,
            "fname": "Roy",
            "lname": "Jaeger",
            "city": "Redwood City",
            "state": "CA"
        },
        {
            "customer_num": 111,
            "fname": "Frances",
            "lname": "Keyes",
            "city": "Sunnyvale",
            "state": "CA"
        },
        {
            "customer_num": 112,
            "fname": "Margaret",
            "lname": "Lawson",
            "city": "Los Altos",
            "state": "CA"
        },
        {
            "customer_num": 113,
            "fname": "Lana",
            "lname": "Beatty",
            "city": "Menlo Park",
            "state": "CA"
        },
        ...
    ],
    "hasMore": false,
    "responseTime": 35
}

Example 6: Insert one row into a table or JSON collection

POST http://localhost:8080/api/servers/server1/databases/mydb/collections/people/insert
Request body:
{ "firstName": "John", "lastName": "Doe", "age": 31 }
Response:
{
    "n": 1,
    "responseTime": 65
}

Example 7: Insert multiple rows into a table or JSON collection

POST http://localhost:8080/api/servers/server1/databases/mydb/collections/people/load
Request body:
{ 
    "data": [
        {"firstName": "Jane", "lastName": "Doe", "age": 28},
        {"firstName": "Joseph", "lastName": "Doe", "age": 32}
    ]
}
Response:
{
    "n": 2,
    "responseTime": 72
}

Example 8: Update one or more rows or documents in a table or JSON collection

POST http://localhost:8080/api/servers/server1/databases/mydb/collections/people/update

Request body:

{ 
    "filter": { "key": "firstName", "op": "=", "value": "John" },
    "updates": {
        "age": 32
    }
}

Response:

{
    "n": 1,
    "responseTime": 44
}

Example 9: Delete one or more rows or documents in a table or JSON collection based on a query condition

POST http://localhost:8080/api/servers/server1/databases/mydb/collections/people/delete

Request body:

{ 
    "filter": { "key": "age", "op": "eq", "value": 32 }
}

Response:

{
    "n": 1,
    "responseTime": 44
}

Example 10: Run a SQL query directly through REST

POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql

Request body:

{ 
    "sql": "select count(*) as count from customer"
}

Response:

{
    "results": [
        {
            "count": 28
        }
    ],
    "hasMore": false,
    "responseTime": 363
}

Example 11: Run a SQL query with host variables through REST

POST http://localhost:8080/api/servers/server1/databases/stores_demo/sql

Request body:

{
    "sql": "select count(*) as count from customer where state = ? and customer_num > ?",
    "hostVariables": [ "CA", 110 ]
}

Response:

[
    {
        "count": 8
    }
]

You can find the full OneDB REST API syntax and many more examples here or you can access the REST API's OpenAPI documentation directly from your REST API server at http://localhost:8080/openapi.