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
{ "firstName": "John", "lastName": "Doe", "age": 31 }
{
"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
{
"data": [
{"firstName": "Jane", "lastName": "Doe", "age": 28},
{"firstName": "Joseph", "lastName": "Doe", "age": 32}
]
}
{
"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
.