Versions Compared

Old Version 21

changes.mady.by.user Yuri Au Yong

Saved on

compared with

New Version Current

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Note

Note!

Temporary files are created on the Platform host when the user executes queries via Reference Data Management. These temporary files, which are stored in $MZ_HOME/tmp/rowset, are removed automatically when the user session expires. Users User sessions expire automatically after six (6) hours.

...

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

...

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

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

...

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 will be are retrieved as well. The result is returned synchronously.

...

Rw ui textbox macro
typetip

Example - Get metadata

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

...

This operation executes database queries on a 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 inputInput parameters that are passed in a JSON format as part of the HTTP message body.

Note

Note!

Any ongoing query process running on the same session will be aborted and a new process for the latest query will be executed.

This operation will clear any uncommitted changes saved in the same session.

...

Resource path

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

HTTP method

PUT

Body

This is where the executeQuery will be is included. The executeQuery JSON payload format will include includes these options:

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

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

...

typetip

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

Rw ui textbox macro
typetip

Example - Get query without query expression in JSON payload body

Code Block
languagebash
$ 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 \
-d '{"executeQuery": {"rowsPerPage":500,"selectedColumns":["ID","FIRSTNAME","LASTNAME"]}}'
Rw ui textbox macro
typetip

Example - Get query

using a

with query expression in JSON payload

file

body

Code Block
languagebash
$ 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 \
sessionid=vusncl88sjghv7h8nkb0ohja6t

Example.json file

Code Block
languagejson
{
	
-d '{"executeQuery": {
		"rowsPerPage": 500,
		"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.

...

Resource path

...

/rowset/<rowset number>?sessionid=<session id>

...

HTTP method

...

GET

Rw ui textbox macro
typetip

Example - Get data sets

Code Block
language
,"filterExpression":[{"col":"ID","expr":9,"op":">="},{"col":"LAST_NAME","expr":"Smith","op":"not like"}]}}'
Rw ui textbox macro
typetip

Example - Get query using a JSON payload file

Code Block
languagebash
$ curl -X PUT -T="Example.json" -u user:passw -H 'content-type: application/json' \
'http://localhost:9000/api/v1/rowset/0? \
sessionid=vusncl88sjghv7h8nkb0ohja6t

Get Status

...

refdatas/Default.refprofile_star/table/APP.STAR/rowset?sessionid=vusncl88sjghv7h8nkb0ohja6t

Example.json file

Code Block
languagejson
{
	"executeQuery": {
		"rowsPerPage": 500,
		"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 Get Status operation.

Resource path

/

status

rowset/<rowset number>?sessionid=<session id>

HTTP method

GET

Rw ui textbox macro
typetip

Example - Get

status

data sets

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

...

Get Status
This operation request the active process to abort.
...
Note!
...
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.
Resource path
/status
/abort
?sessionid=<session id>
HTTP method
GET
 Rw ui textbox macro
typetip
Example - Get status
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/status/abort? \
sessionid=vusncl88sjghv7h8nkb0ohja6t
...
   
Abort Process
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. 
...
Parameters
...
Description
...
Value
...
Resource path
...
The path to where the resource is located
...
/refdatas/<Reference Data profile>/table/<table name>/download?sessionid=<sessionid> 
...
HTTP method
...
The HTTP method of the export command.
...
POST
...
Basic Auth
...
This is a required parameter which contains the Image Removed username and password. 
...
user:passw
...
Body
...
This is where the exportParams will be included in url encoding. The exportParams format will include these optional 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.
  2. selectedColumns - allows for specific columns to be selected.
Example format: exportParams={"opts":{"textQualifier":"\'","separator":";","extent":"all"},"selectedColumns":["COLUMN1","COLUMN2"]}
...
requests the active process to abort.
 Note
Note!
To prevent a user from initiating another operation before the first operation initiated is complete, Abort Process can be used before an operation is complete. 
Resource path
/status/abort?sessionid=<session id>
HTTP method
GET
 Rw ui textbox macro
typetip
Example - Get status
 Code Block
languagebash
$ 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. 
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.
 Note
Note!
For the extend option, selected value is only applicable when Get Query is applied prior Table Export. 
 Rw ui textbox macro
typetip
Example - Table export without options
 Code Block
languagebash
## 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" \
-Hd 'content-type: application/x-www-form-urlencoded' \
-d 'exportParams=%7B%7DexportParams={}' \
> Export.csv
 Rw ui textbox macro
typetip
Example - Table export with options
 Code Block
languagebash
## exportParams={"opts":{"textQualifier":"\'","separator":";","extent":"all"},"selectedColumns":["CITY","CUSTOMER_NAME"]}

curl -X POST \
  'http://localhost:9000/$ 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" \
 
-Hd '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%7DexportParams={"opts":{"textQualifier":"'\''","separator":";","extent":"all"},"selectedColumns":["CITY","CUSTOMER_NAME"]}'
> Export.csv
Table Import
This operation performs a database table import. Input parameters are passed in a JSON format as part of the form-data in the HTTP message body.
Parameters
Description
Value
Resource path
The path to where the resource is located
/
Resource path
/refdatas/<Reference Data profile>/table/<table name>/upload?sessionid=<sessionid> 
HTTP method
The HTTP method of the import command.
POST
Basic Auth
Body
This is 
 a required parameter which contains the Image Removed username and password. 
user:passw
File Input
This is where the file input will be included in url encoding
where the file and the input parameters are included. The exportParams format 
 will include 
includes these 
 optional opts
options:
  1. textQualifier - designated as double quotes by default.
  2. separator - designated as a comma by default.
append/truncate 
  1. opts - designated as append by default.
 Changing to truncate will result in 
    1. append - imported rows are appended to the table.
    2. truncate - the existing data in the table 
 to be 
    1. are truncated before the import is executed.
  2. force - designated as false by default.
Example format: file=@/<path to csv file>/test.csv 'opts={"textQualifier":"\'","separator":",","op":"append","force":false}'
file=@/path/to/import_test.csv 'opts={"textQualifier":"\'","separator":",","op":"append","force":false}'
...
 Rw ui textbox macro
typetip
Example - Table import without options
 Code Block
languagebash
$ 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
rw-ui-textbox-macro
typetip
Example - Table import 
 without 
with options
 Code Block
languagebash
$ curl -i -u "user:passw" \
 
'http://localhost:9000/api/v1/refdatas/ref_data_mgmt_oracle.ref_data_mgmt_pf/table/TABLE.CUSTOMERS/upload?sessionid=rssrh20dcd8lc1j505b3bqnstcp3hce86dkb4rmls9peh4e8rps9' \
 
-F file=@/path/to/import_test.csv
 Rw ui textbox macro
typetip
Example - Table import with options
 Code Block
languagebash
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
This operation saves data grid edit modification (post insert/update/delete). Changes are saved within client session. This operation requires JSON payload as input.
 Note
Note!
tableName and refProfile are mandatory in order to save. A single table can be modified in a single session only.  
 Note
Note!
 \
-F 'opts={"textQualifier":"\'","separator":",","op":"truncate","force":false}'
Save Changes
This operation saves data modification (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. 
 Note
Note!
tableName and refProfile are mandatory in order to save. A single table can be modified in a single session only.  
 Note
Note!
The save operation is supported either on Oracle (based on the ROWID pseudo column) or non-Oracle type tables containing a Primary Key constraint. Non-Oracle tables without a Primary Key are not supported for data modifications. 
Resource path
/save?sessionid=<session id> 
HTTP method
PUT
 Rw ui textbox macro
typetip
Example - Save changes
 Code Block
languagebash
$ curl -X PUT -T="example.json" \
http://user:passw@localhost:9000/api/v1/save? \
sessionid=vusncl88sjghv7h8nkb0ohja6t
...
typetip
Example json
...
languagejson
...
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.
 Note
Note!
When inserting a row, specifying a pseudo ids column value pair allows Cancel Changes to be applied to that specific insert row modification.
 Rw ui textbox macro
typetip
Example - Save changes with a JSON payload body
 Code Block
languagebash
$ curl -X PUT -u "user:passw" -H 'content-type: application/json \
-d '{"dataEdit":{"refProfile":"Default.refTest","tableName":"MZADMIN.REFRENCE_DATA",
		"updates": [{
			"action": "insertdelete",
			"valuesids": [{
				"column": "ID",
				"value": 645
			}, {
				"column": "FIRSTNAME",
				"value": "Roberts"
			}, {
				"column": "LASTNAME",
				"value": "Polis"
			}]
		}]
	}
}
Commit Changes
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. 
...
Resource path
...
/save/commit?force=<true|false>&sessionid=<session id> 
...
HTTP method
...
GET
 Rw ui textbox macro
typetip
Example - Commit changes
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/commit? \
force=false&sessionid=vusncl88sjghv7h8nkb0ohja6t
List Changes
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.
...
Resource path
...
/save/list?sessionid=<session id>
...
HTTP method
...
GET
 Rw ui textbox macro
typetip
Example - Commit changes
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/save/list?
\sessionid=vusncl88sjghv7h8nkb0ohja6t
Cancel Changes
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.
 Note
Note!
The Web UI generates surrogate Primary Keys for inserted rows for the cancel function. 
...
Resource path
...
/save/cancel?sessionid=<session id>
...
HTTP method
...
PUT
 Rw ui textbox macro
typetip
Example - Commit changes
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/save/cancel? \
sessionid=vusncl88sjghv7h8nkb0ohja6t 
 Rw ui textbox macro
typetip
Example json
 Code Block
languagejson
{
	"dataEdit": {
		"scope": "single",
		"ids": [{
			"id": [{
				"column": "ORDER_NUM",
				"value": 10398005
			}]
		}]
	}
}
 Rw ui textbox macro
typetip
Example response
 Code Block
languagejson
{
	"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
		}]
	}
}
 Note
Note!
SaveSize indicates the number of changes remaining. 
Show Demo Query
...
8}]}]}}' \
http://localhost:9000/api/v1/save?sessionid=vusncl88sjghv7h8nkb0ohja6t
 Rw ui textbox macro
typetip
Example - Save changes using a JSON payload file
 Code Block
languagebash
$ curl -X PUT -T="Example.json" -u "user:passw" -H 'content-type: application/json \
http://localhost:9000/api/v1/save?sessionid=vusncl88sjghv7h8nkb0ohja6t
Example.json
 Code Block
languagejson
{
   "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
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.
Resource path
/save/commit?force=<true|false>&sessionid=<session id> 
HTTP method
GET
 Rw ui textbox macro
typetip
Example - Commit changes
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/commit?force=false&sessionid=vusncl88sjghv7h8nkb0ohja6t
List Changes
This operation returns a list of the modifications saved.
Resource path
/save/list?sessionid=<session id>
HTTP method
GET
 Rw ui textbox macro
typetip
Example - List changes
 Code Block
languagebash
$ curl -u user:passw \
http://localhost:9000/api/v1/save/list?sessionid=vusncl88sjghv7h8nkb0ohja6t
Cancel Changes
This operation cancels the changes made from being saved. Input parameters are passed in a JSON format as part of the HTTP message body.
 Note
Note!
Pseudo Primary Keys for inserted rows can be included in the Save Changes operation to allow the cancel function for a specific insert row modification. 
 Note
Note!
SaveSize in the Cancel Changes response indicates the number of changes remaining. 
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.
 Rw ui textbox macro
typetip
Example - Cancel changes
 Code Block
languagebash
$ 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
This operation shows an example JSON payload format that applies for a Get Query operation. 
Resource path
/demo/queryRequestParameters
HTTP method
GET
Show Demo Changes
This operation shows the json an example JSON payload format that applies to edit data via the Web UI, such as save/commitfor a Save Changes operation. 
Resource path
/demo/dataEditRequestParameters
HTTP method
GET
...