Database operations are supported via a RESTful HTTP Interface on the Platform.

Restful Operations

The tables below describe the various operations that are available for Reference Data Management. 

The service endpoint URI is http(s)://<Platform host>:<port>/api/v1.

Basic authentication is used and you must pass user credentials for each RESTful call.

Create and Get Session

Resource path: /session

HTTP method: GET

This operation returns a session id which should be appended to all subsequent API calls where this parameter is applicable.

$ curl -u \
user:passw http://localhost:9000/api/v1/session

Close Session

Resource path: /session/close?sessionid=<sessionid>

HTTP method: GET

This operation closes a session. This session id is no longer valid for any subsequent API calls.

$ curl -u user:passw \
http://localhost:9000/api/v1/session/close?sessionid=vusncl88sjghv7h8nkb0ohja6t

Get Reference Data Profiles

Resource path: /refdatas 

HTTP method: GET

This operation retrieves a list of all Reference Data profiles that are available to the user.

$ curl -u \
user:passw http://localhost:9000/api/v1/refdatas  
  

Get Metadata

Resource path: /refdatas/<Reference Data profile>/table/<table name>?sessionid=<session id>

HTTP method: GET

This operation retrieves table metadata that can be used for viewing or to derive parameters for other REST APIs. If the Last Update feature is enabled, the values stored in the most recent Last Update user and timestamp column are retrieved as well. The result is returned synchronously.

$ curl -u user:passw \
http://localhost:9000/api/v1/refdatas/Default.refprofile_star/table/APP.STAR?sessionid=vusncl88sjghv7h8nkb0ohja6t

Get Query

Resource path: /refdatas/<Reference Data profile>/table/<table name>/rowset?sessionid=<session id>

HTTP method: PUT

Body: This is where the executeQuery is included. The executeQuery JSON payload includes these options:

1. rowsPerPage - the max number of rows (Data Set size) that are allowed in a rowset of the retrieved result.

2. selectedColumns - allows for specific columns to be selected.

3. filterExpression - allows optional definition of query expressions. The query will match all of the specified query expressions.

This operation executes database queries on a specific Reference Data profile and database table. The query is performed asynchronously and control is returned immediately. You can retrieve the result of the query by using /rowset/<rowset number>?sessionid=<session id>. This operation requires input parameters that are passed in a JSON format as part of the HTTP message body.

$ curl -X PUT -u user:passw -H 'content-type: application/json' \
http://localhost:9000/api/v1/refdatas/Default.refprofile_star/table/APP.STAR/rowset?sessionid=vusncl88sjghv7h8nkb0ohja6t \
-d '{"executeQuery": {"rowsPerPage":500,"selectedColumns":["ID","FIRSTNAME","LASTNAME"]}}'

$ curl -X PUT -u user:passw -H 'content-type: application/json' \
http://localhost:9000/api/v1/refdatas/Default.refprofile_star/table/APP.STAR/rowset?sessionid=vusncl88sjghv7h8nkb0ohja6t \
-d '{"executeQuery": {"rowsPerPage":500,"selectedColumns":["ID","FIRSTNAME","LASTNAME"],"filterExpression":[{"col":"ID","expr":9,"op":">="},{"col":"LAST_NAME","expr":"Smith","op":"not like"}]}}'

$ curl -X PUT -T="example.json" -u user:passw -H 'content-type: application/json' \
'http://localhost:9000/api/v1/refdatas/Default.refprofile_star/table/APP.STAR/rowset?sessionid=vusncl88sjghv7h8nkb0ohja6t

{
	"executeQuery": {
		"rowsPerPage": 500,
		"selectedColumns": ["ID", "FIRSTNAME", "LASTNAME"]
	}
}

Get Data Sets

Resource path: /rowset/<rowset number>?sessionid=<session id>

HTTP method: GET

Data sets can be retrieved once downloaded to the file system of the Platform. This operation returns a data set for the given rowset (sequence number). The total number of available data sets can be queried with the status operation.

$ curl -u user:passw \
http://localhost:9000/api/v1/rowset/0?sessionid=vusncl88sjghv7h8nkb0ohja6t

Get Status

Resource path: /status?sessionid=<session id>

HTTP method: GET

This operation returns a status message. It can be used to retrieve active processes and to query the number of available rows, data sets, and the status of imports and exports. If the Last Update feature is enabled, the values stored in the most recent Last Update user and timestamp column are retrieved as well.

$ curl -u user:passw \
http://localhost:9000/api/v1/status?sessionid=vusncl88sjghv7h8nkb0ohja6t   

Abort Process

Resource path: /status/abort?sessionid=<session id>

HTTP method: GET

This operation requests the active process to abort.

$ curl -u user:passw \
http://localhost:9000/api/v1/status/abort?sessionid=vusncl88sjghv7h8nkb0ohja6t

Table Export

Resource path: /refdatas/<Reference Data profile>/table/<table name>/download?sessionid=<sessionid> 

HTTP method: POST

Body: This is where the exportParams are included. The exportParams JSON payload includes these options:

1. opts

   1. textQualifier - designated as double quotes by default.

   2. separator - designated as a comma by default.

   3. extent - designated as all by default.

      1. all - export all rows in the table.

      2. selected - export rows from the result of Get Query.

2. selectedColumns - allows for specific columns to be selected.

This operation performs a database table export. Input parameters are passed in a JSON format as part of the HTTP message body.

$ curl -X POST \
'http://localhost:9000/api/v1/refdatas/ref_data_mgmt_oracle.ref_data_mgmt_pf/table/TABLE.CUSTOMERS/download?sessionid=p3hce86dkb4rmls9peh4e8rps9' \
-u "user:passw" \
-d 'exportParams={}' \
> Export.csv

$ curl -X POST \
'http://localhost:9000/api/v1/refdatas/ref_data_mgmt_oracle.ref_data_mgmt_pf/table/TABLE.CUSTOMERS/download?sessionid=p3hce86dkb4rmls9peh4e8rps9' \
-u "user:passw" \
-d 'exportParams={"opts":{"textQualifier":"'\''","separator":";","extent":"all"},"selectedColumns":["CITY","CUSTOMER_NAME"]}'
> Export.csv

Table Import

Resource path: /refdatas/<Reference Data profile>/table/<table name>/upload?sessionid=<sessionid>


HTTP method: POST

Body: This is where the file and the input parameters are included. The exportParams format includes these options:

1. textQualifier - designated as double quotes by default.

2. separator - designated as a comma by default.

3. opts - designated as append by default.

   1. append - imported rows are appended to the table.

   2. truncate - the existing data in the table are truncated before the import is executed.

4. force - designated as false by default.

This operation performs a database table import. Input parameters are passed in a JSON format as part of the HTTP message body.

$ curl -i -u "user:passw" \
'http://localhost:9000/api/v1/refdatas/ref_data_mgmt_oracle.ref_data_mgmt_pf/table/TABLE.CUSTOMERS/upload?sessionid=rssrh20dcd8lc1j505b3bqnstc' \
-F file=@/path/to/import_test.csv

$ curl -i -u "user:passw" \
'http://localhost:9000/api/v1/refdatas/ref_data_mgmt_oracle.ref_data_mgmt_pf/table/TABLE.CUSTOMERS/upload?sessionid=p3hce86dkb4rmls9peh4e8rps9' \
-F file=@/path/to/import_test.csv \
-F 'opts={"textQualifier":"\'","separator":",","op":"truncate","force":false}'

Save Changes

Resource path: /save?sessionid=<session id> 

HTTP method: PUT

Body: This is where the dataEdits are included. The dataEdit JSON payload includes these options:

1. refProfile - Reference Data Management Profile.

2. tableName - Database table name.

3. updates - modification parameters.

   1. action - to specify the type of modification.

      1. insert - insert a new row to the table.

      2. update - edit an existing row in the table.

      3. delete - delete an existing row in the table.

   2. ids - to specify column value pairs of primary key(s).

      1. column - private key column name.

      2. value - private key value for the respective column.

   3. values - to specify column value pairs to be inserted/updated.

      1. column - column name to be inserted/updated.

      2. value - insert/update value for the respective column.

This operation saves data grid edit modification (post insert/update/delete). Changes are saved within the client session. Input parameters are passed in a JSON format as part of the HTTP message body. 

$ curl -X PUT -u "user:passw" -H 'content-type: application/json \
-d '{"dataEdit":{"refProfile":"Default.refTest","tableName":"MZADMIN.REFRENCE_DATA","updates":[{"action":"delete","ids":[{"column":"ID","value":8}]}]}}' \
http://localhost:9000/api/v1/save?sessionid=vusncl88sjghv7h8nkb0ohja6t

$ curl -X PUT -T="example.json" -u "user:passw" -H 'content-type: application/json \
http://localhost:9000/api/v1/save?sessionid=vusncl88sjghv7h8nkb0ohja6t

{
   "dataEdit" : {
      "refProfile" : "Default.refTest",
      "tableName" : "MZADMIN.REFRENCE_DATA",
      "updates" : [
         {
            "action" : "insert",
            "ids" : [
               {
                  "column" : "ROWID",
                  "value" : "ins0"
               }
            ],
            "values" : [
               {
                  "column" : "ID",
                  "value" : 645
               },
               {
                  "column" : "FIRST_NAME",
                  "value" : "Roberts"
               },
               {
                  "column" : "LAST_NAME",
                  "value" : "Polis"
               }
            ]
         },
         {
            "action" : "update",
            "ids" : [
               {
                  "column" : "ID",
                  "value" : "6"
               }
            ],
            "values" : [
               {
                  "column" : "LAST_NAME",
                  "value" : "Wick"
               },
               {
                  "column" : "FIRST_NAME",
                  "value" : "John"
               }
            ]
         },
         {
            "action" : "delete",
            "ids" : [
               {
                  "column" : "id",
                  "value" : "8"
               }
            ]
         }
      ]
   }
}

Commit Changes

Resource path: /save/commit?force=<true|false>&sessionid=<session id> 

HTTP method: GET

This operation applies saved edits in the database and commits the work in case of success. You can use force commit in case of errors. If the Last Update feature is enabled, the user name and modification timestamp values for insert/update modifications are stored in the Last Update columns specified in the Reference Data Management Profile. The Last Update information is used by the Get Status operation to retrieve the most recent Last Update user and timestamp.

$ curl -u user:passw \
http://localhost:9000/api/v1/commit?force=false&sessionid=vusncl88sjghv7h8nkb0ohja6t

List Changes

Resource path: /save/list?sessionid=<session id>

HTTP method: GET

This operation returns a list of the modifications saved.

$ curl -u user:passw \
http://localhost:9000/api/v1/save/list?sessionid=vusncl88sjghv7h8nkb0ohja6t

Cancel Changes

Resource path: /save/cancel?sessionid=<session id>

HTTP method: PUT

Body: This is where the dataEdits are included. The dataEdit JSON payload includes these options:

1. scope - specify the scope of changes to be canceled.

   1. single - allow only specific modifications identified by ids to be cancelled.

   2. all - allow all the modifications to be canceled.

2. ids - to specify column value pairs of primary key(s). This is only required when the scope is single.

   1. column - private key column name.

   2. value - private key value for the respective column.

This operation cancels the changes made from being saved. Input parameters are passed in a JSON format as part of the HTTP message body.

$ curl -u user:passw -H 'content-type: application/json' \
-d '{"dataEdit":{"scope":"single","ids": [{"id":[{"column":"ORDER_NUM","value":10398005}]}] \
http://localhost:9000/api/v1/save/cancel?sessionid=vusncl88sjghv7h8nkb0ohja6t

Show Demo Query

Resource path: /demo/queryRequestParameters

HTTP method: GET

This operation shows an example JSON payload format that applies for a Get Query operation.

Show Demo Changes

Resource path: /demo/dataEditRequestParameters

HTTP method: GET

This operation shows and example JSON payload format that applies for a Save Changes operation.