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

Restful Operations

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

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

If encryption is enabled, the URI is https://<Platform host>:<port>/api/v1.

For further information about enabling encryption, see Network Security in the System Administration user's guide.

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

Create and Get Session

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

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

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

This operation retrieves table metadata that can be used for grid configuration, export, and Import configuration etc. If Last Update feature is enabled, the values stored in the most recent Last Update user and timestamp column will be 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

This operation executes database queries on specific a 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 JSON payload as input.

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

{
	"executeQuery": {
		"rowsPerPage": 1024,
		"tableName": "APP.STAR",
		"selectedColumns": ["ID", "FIRSTNAME", "LASTNAME"]
	}
}

Get Data Sets

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 status of imports and exports. If Last Update feature is enabled, the values stored in the most recent Last Update user and timestamp column will be 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 request the active process to abort.

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

Table export

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

## exportParams={}

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" \
-H 'content-type: application/x-www-form-urlencoded' \
-d 'exportParams=%7B%7D' \
> Export.csv

## exportParams={"opts":{"textQualifier":"\'","separator":";","extent":"all"},"selectedColumns":["CITY","CUSTOMER_NAME"]}

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" \
  -H 'content-type: application/x-www-form-urlencoded' \
  -d 'exportParams=%7B%22opts%22%3A%7B%22textQualifier%22%3A%22%5C'\''%22%2C%22separator%22%3A%22%3B%22%2C%22extent%22%3A%22all%22%7D%2C%22selectedColumns%22%3A%5B%22CITY%22%2C%22CUSTOMER_NAME%22%5D%7D'
> Export.csv

Table Import

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

This operation saves data grid edit modification (post insert/update/delete). Changes are saved within client session. This operation requires JSON payload as input.

$ curl -X PUT -T="example.json" \
http://user:passw@localhost:9000/api/v1/save? \
sessionid=vusncl88sjghv7h8nkb0ohja6t

{
	"dataEdit": {
		"refProfile": "Default.refTest",
		"tableName": "MZADMIN.REFRENCE_DATA",
		"updates": [{
			"action": "insert",
			"values": [{
				"column": "ID",
				"value": 645
			}, {
				"column": "FIRSTNAME",
				"value": "Roberts"
			}, {
				"column": "LASTNAME",
				"value": "Polis"
			}]
		}]
	}
}

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. 

$ 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. You can use this operation to reapply changes in the Web UI during a grid refresh.

$ 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

This operation cancels the changes made from being saved. As the input, Json payload specifies either all the changes to be canceled or lists the selected Primary Keys to be cancelled. You can cancel the saving of several changes at the same time (one Primary Key per change). The scope is single or all. The format is the same as for the Save changes operation but the ID fields are stored in a root structure. Ids are in a JSON array to hold multiple Primary Keys to be cancelled.

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

{
	"dataEdit": {
		"scope": "single",
		"ids": [{
			"id": [{
				"column": "ORDER_NUM",
				"value": 10398005
			}]
		}]
	}
}

{
	"dataEdit": {
		"updates": [{
			"action": "update",
			"ids": [{
				"column": "ORDER_NUM",
				"value": 10398005
			}],
			"undoRow": [{
					"column": "SHIPPING_DATE",
					"value": "2011 - 05 - 24 00: 00: 00"
				},
				{
					"column": "ORDER_NUM",
					"value": 10398005
				},
				{
					"column": "QUANTITY",
					"value": 100
				}
			],
			"values": [{
				"column": "FREIGHT_COMPANY",
				"value": "Zagreb"
			}],
			"SaveSize": 3
		}]
	}
}

Show Demo Query

Resource path: /demo/queryRequestParameters

HTTP method: GET

This operation shows the json payload format that applies for a query operation e g rowset/. 

Show Demo Changes

Resource path: /demo/dataEditRequestParameters

HTTP method: GET

This operation shows the json payload format that applies to edit data via the Web UI e g save/commit.