October 14, 2017

Working with Oracle MCS Database Management API

­­­­In Oracle Mobile Cloud Service (MCS) "Database Management API" is another API in addition to "Database Access API" which lets you view table metadata, and create, drop, and re-create tables. This post is not to explain about the API but to see how to access it from outside. Please click here to know more about the API.

The good part of this API is, you can access this API from outside of MCS using any rest client tool. 
In this example, I am using Postman.

To access the “Database Management API” from outside, you need to pass MCS login credentials instead of mobile backend authentication details along with mobile backend id to every API.

Below is a screenshot for your reference where it is fetching meta data of my table “MY_CUSTOMER”. Note that the table name here is case sensitive.


Below is the full output of the above GET operation to fetch meta data of a table "MY_CUSTOMER".

{
    "name": "MY_CUSTOMER",
    "primaryKeys": [
        "id"
    ],
    "requiredColumns": [
        "id"
    ],
    "columns": [
        {
            "name": "id",
            "type": "decimal"
        },
        {
            "name": "vehiclenum",
            "size": 6,
            "type": "string"
        },
        {
            "name": "customermobile",
            "size": 14,
            "type": "string"
        },
        {
            "name": "customernum",
            "size": 7,
            "type": "integer"
        },
        {
            "name": "customername",
            "size": 8,
            "type": "string"
        },
        {
            "name": "vehicleserial",
            "size": 9,
            "type": "string"
        },
        {
            "name": "createdBy",
            "size": 80,
            "type": "string"
        },
        {
            "name": "createdOn",
            "type": "dateTime"
        },
        {
            "name": "modifiedBy",
            "size": 80,
            "type": "string"
        },
        {
            "name": "modifiedOn",
            "type": "dateTime"
        }
    ]
}

Below are the other operations and APIs allowed with Database Management API.
  • To list all the tables in the MCS database:

URL: https://<MCS API URI>/mobile/system/databaseManagement/tables
Operation: GET
  •       To list meta data of a table in the MCS database:

URL: https://<MCS API URI>/mobile/system/databaseManagement/tables/<Table Name>
Operation: GET
  •     To drop table in the MCS database:

URL: https://<MCS API URI>/mobile/system/databaseManagement/tables/<Table Name>
Operation: DELETE
  •    To create a new table in the MCS database:

URL: https://<MCS API URI>/mobile/system/databaseManagement/tables
Operation: POST
Input:
{ "name" : "MY_CUSTOMER",
  "columns": [
    {"name": " id", "type": "integer", "size": 3},
    {"name": " vehiclenum", "type": "string", "size": 50},
    {"name": " customermobile", "type": "string"},
    {"name": " customernum", "type": "integer", "size": 3},
    {"name": " customername", "type": "string"},
    {"name": " vehicleserial", "type": "string"}
],
  "primaryKeys" : [ "id" ],
  "requiredColumns": ["id", "vehiclenum" ]
}