USER MANUALS

IDU Requests

In addition to querying data, you can also use the RESTful web service to execute Insert, Delete and Update (IDU) operations over views. To execute IDU operations over a view, the view has to be “updateable”. The section Inserts, Updates and Deletes Over Views of the VQL Guide explains when a view is “updateable”.

Following the RESTful conventions, the statement sent to Virtual DataPort by the service depends on the HTTP method of the request:

RESTful Web service: HTTP methods for IDU operations

HTTP Method

Syntax of the Request URL

SQL Statement that the Service Executes

POST

/denodo-restfulws/<database name>/views/<view name>

INSERT

PUT

/denodo-restfulws/<database name>/views/<view name>/<primary key value>

UPDATE

DELETE

/denodo-restfulws/<database name>/views/<view name>/<primary key value>

DELETE

The POST and PUT requests have to contain the data that you want to insert/modify in the view, in the body of the request. You can represent these data as a JSON or an XML document. Depending on the type of document, the HTTP Header Content-Type has to be either application/json or application/xml.

In both types of documents, the values of type timestamptz have to be expressed with the following format: yyyy-MM-dd'T'hh:mm:ssZ. E.g. “2005-06-29T17:19:41+0000”.

INSERT Queries (POST)

To insert one or more rows into a view, send a POST request to the URL that represents the view (/denodo-restfulws/<database name>/views/<view name>). For example, if the view is called “incidence”, the URL is this:

https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence

When the service receives this request, it executes an INSERT statement. If the statement finishes correctly, the service returns the HTTP code 201 (“Created”). If the view has primary key and the request is to insert just one row, the response will also include the HTTP header “Location” with an URL that points to the new row.

Below, there are two examples of a request to insert several rows. Both examples do the same, the first one sends the data in a JSON document and the second one, in an XML. These examples show how to insert multiple rows but you can also use them to insert just one row.

Example #1 (JSON)

  • URL: https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence

  • HTTP Method: POST

  • HTTP headers:

    Accept: application/json
Content-Type: application/json

  • Body of the request:

Body of the request to insert three rows in the view “employee” (JSON request)
[
      {
             "employee_id": 9999,
             "first_name": "Peter",
             "last_name": "Jackson",
             "email": "pjackson",
             "hire_date": "2016-01-24",
             "job_id": "AD_PRES",
             "phone_number": "515.123.8181"
      },
      {
             "employee_id": 10000,
             "first_name": "Christopher",
             "last_name": "Nolan",
             "email": "cnolan",
             "hire_date": "2016-01-22",
             "job_id": "AD_PRES",
             "department_id": null
      },
      {
             "employee_id": 10001,
             "first_name": "Steven",
             "last_name": "Spielberg",
             "email": "sspielberg",
             "hire_date": "2016-01-25",
             "job_id": "AD_PRES",
             "salary": null
      }
]

When the service receives this request, it executes this query:

INSERT INTO employee
    (employee_id, first_name, last_name, email, hire_date, job_id, phone_number, department_id)
VALUES
  ( 9999, 'Peter', 'Jackson', 'pjackson', DATE '2016-01-24', 'AD_PRES', '515.123.8181', null)
, ( 10000, 'Christopher', 'Nolan', 'cnolan', DATE '2016-01-22', 'AD_PRES', null, null)
, ( 10001, 'Steven', '', 'sspielberg', DATE '2016-01-26', 'AD_PRES', null, null)

If a field is present in at least one row, the value of that field is set to NULL in the in the other rows of the INSERT statement that do not provide a value for it. For example, phone_number is only present in the first row but as all the new rows have to have the same number of fields, the service puts NULL for that field.

Example #2 (XML)

  • URL: https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/employee

  • HTTP Method: POST

  • HTTP headers:

    Accept: application/xml
Content-Type: application/xml

  • Body of the request:

Body of the request to insert three rows in the view “employee” (XML request)
<?xml version="1.0" encoding="UTF-8"?>
<view name="employee"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
     <employee>
             <employee_id>9999</employee_id>
             <first_name>Peter</first_name>
             <last_name>Jackson</last_name>
             <email>pjackson</email>
             <hire_date>2016-01-24</hire_date>
             <job_id>AD_PRES</job_id>
             <phone_number>515.123.8181</phone_number>
     </employee>
     <employee>
             <employee_id>10000</employee_id>
             <first_name>Christopher</first_name>
             <last_name>Nolan</last_name>
             <email>cnolan</email>
             <hire_date>2016-01-22</hire_date>
             <job_id>AD_PRES</job_id>
             <phone_number xsi:nil="true"/>
             <department_id xsi:nil="true"/>
     </employee>
     <employee>
             <employee_id>10001</employee_id>
             <first_name>Steven</first_name>
             <last_name>Spielberg</last_name>
             <email>sspielberg</email>
             <hire_date>2016-01-26</hire_date>
             <phone_number/>
             <job_id>AD_PRES</job_id>
     </employee>
</view>

Considerations:

  • The name of the root element has to be “view” and has to have the attribute “name” whose value is the name of the view to insert the data.

  • Add one element with the name of the view per each row you want to insert. In this example, “<employee>”.

  • To assign NULL to a value in the INSERT statement, define the namespace http://www.w3.org/2001/XMLSchema-instance and add the attribute xsi:nil="true" to the fields that you need to (in this example, specific_field1 and specific_field2).

The INSERT statement that the service executes is the same as when you send a JSON body.

Alternative Structure for the Body of the Request to Insert One Row

The text above explains the structure of the body of the requests to insert one or more rows. There is an alternative structure to insert a single row. This is the structure that the REST web service of Denodo 6.0 supported. Since Denodo 7.0, the syntax above is already supported and we recommend using the new one because you can insert one or several rows in a single request.

The examples below show the old body of the request, to insert one row into a view. Both examples are equivalent; in one the data is sent in JSON and the other, in XML.

Example #1 (JSON)

Body of the request:

Body of the request to insert one row in the view “incidence” (JSON request)
{
    "iinc_id": 2000
  , "summary": "New incidence"
  , "taxid": "B99999999"
  , "specific_field1": null
  , "specific_field2": null
}

Example #2 (XML)

Body of the request:

Body of the request to insert one row in the view “incidence” (XML request)
<?xml version="1.0" encoding="UTF-8"?>
<incidence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <iinc_id>2000</iinc_id>
    <summary>New incidence</summary>
    <taxid>B99999999</taxid>
    <specific_field1 xsi:nil="true" />
    <specific_field2 xsi:nil="true" />
</incidence>

Considerations:

  • The root element has to be the name of the view.

  • To assign NULL to a value define the namespace http://www.w3.org/2001/XMLSchema-instance and add the attribute xsi:nil="true" to the fields that you want to set to NULL explicitly (in this example, specific_field1 and specific_field2).

UPDATE Queries (PUT)

To modify the values of a row, send a PUT request to the URL that represents the row of the view (/denodo-restfulws/<database name>/views/<view name>/<primary key values>). For example, if the view is called “incidence”, the URL is this:

https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence/12

The final segment of the URL (12) is the value of the primary key, which in the view incidence is the field IINC_ID.

When the service receives this request, it executes an UPDATE statement. If the statement completes correctly, the service returns the HTTP code 204 (No content).

As the service uses the value of the primary key to identify the row to delete, you cannot use the RESTful web service to update rows of views that do not have primary key.

If the primary key is formed by two or more fields, separate the value of each field with a comma (,). If any of the values of the primary key contains a “,”, encode it with %2C to avoid interpreting it as the separator of primary key values.


Below, there are two equivalent examples of a PUT (UPDATE) request. The first one sends the data in an XML document and the second one, in a JSON document.

Example 1 (JSON)

  • URL: https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence/2

  • HTTP method: PUT

  • HTTP headers:

    Accept: application/json
Content-Type: application/json

  • Body of the request:

RESTful Web service: PUT request with JSON body
{
  "summary": "Incident in ADSL router ..."
}

When the Service receives this request, it executes the following query:

UPDATE incidence SET
    summary = 'Incident in ADSL router ...'
WHERE iinc_id = 2

The WHERE clause is formed by the field of the primary key (IINC_ID) and the value of the last segment of the URL (2).

Example 2 (XML)

  • URL: https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence/2

  • HTTP method: PUT

  • HTTP headers:

    Accept: application/xml
Content-Type: application/xml

  • Body of the request:

Body of the request to modify one row of the view incidence XML
<?xml version="1.0" encoding="UTF-8"?>
<incidence xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <summary>Incident in ADSL router ...</summary>
</incidence>

When the service receives this request, it executes the same query as when it receives a JSON document.

DELETE Queries

To delete a row of a view, send a DELETE request to the URL that represents the row of the view (/denodo-restfulws/<database name>/views/<view name>/<primary key values>). For example, if the view is called “incidence”, the URL is this:

https://denodo-server.acme.com:9443/denodo-restfulws/admin/views/incidence/12

The final segment of the URL (12) is the value of the primary key, which in the view incidence is the field IINC_ID.

When the service receives this request, it executes a statement like DELETE FROM <view> WHERE <primary key>. If the row is deleted, the service returns the HTTP code 204 (No content).

As the service uses the value of the primary key to identify the row to delete, you cannot use the RESTful web service to delete rows of views that do not have primary key.

To delete several rows, send one request per each row to delete.

If the primary key is formed by two or more fields, separate the value of each field with a comma (,). If any of the values of the primary key contains a “,”, encode it with %2C to avoid interpreting it as the separator of primary key values.

The difference with the DELETE requests and the POST and PUT ones, is that the body of the request is empty and you do not need to add the HTTP headers Accept nor Content-Type.

Add feedback