IDU Requests¶
Besides providing different representations of the data of the views, the RESTful Web service can execute Insert, Delete and Update (IDU) operations.
Note that 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 (see RESTful Web service: HTTP methods and their equivalent VQL statements).
The POST
(INSERT
) and PUT
(UPDATE
) requests have to
contain the data that you want to insert 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
.
The XML document sent in the body of the requests has to meet the following conditions:
The name of the root element has to be the name of the view
If you want to set the value of a field to
NULL
, you have to define thexmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
namespace and add the attributexsi:nil=true
to the field (see RESTful Web service: POST request with XML body)
In both types of documents, the values of type date
have to be
expressed with the following format: yyyy-MM-dd'T'hh:mm:ssZ
. E.g.
“2005-06-29T17:19:41+0000
”.
To execute a PUT
(UPDATE
) or DELETE
request over a view, the
view must have its primary key defined. That is because the row to
update/delete is identified by the values of its primary key.
INSERT Queries (POST)¶
To execute an INSERT
query over a view, you have to send a POST
request to a URL that represents a view.
The syntax of the URL to execute INSERT
queries is
http://localhost:9090/denodo-restfulws/<database name>/views/<view name>
E.g.
http://localhost:9090/denodo-restfulws/admin/views/internet_inc
.
If the Service processes the request correctly, it returns the HTTP code 201 (“Created”). If the target view has primary key, the response will also include the HTTP header “Location” with an URL that points to the new row.
Below, there are two equivalent examples of a POST
(INSERT
)
request. The first one sends the data in an XML document and the second
one, in a JSON document.
Example 1 (XML)
URL = http://localhost:9090/denodo-restfulws/admin/views/internet_inc
HTTP Method = POST
HTTP Header Accept
= application/xml
HTTP Header Content-Type
= application/xml
Body of the request:
<?xml version="1.0" encoding="UTF-8"?>
<internet_inc 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" />
</internet_inc>
By defining the namespace http://www.w3.org/2001/XMLSchema-instance
and adding the attribute xsi:nil="true"
to the specific_field1
and specific_field2
fields, their values will be NULL
.
Also, note that the name of the root element is the name of the view we
want to execute the INSERT
query on.
Example 2 (JSON)
URL = http://localhost:9090/denodo-restfulws/admin/views/internet_inc
HTTP Method = POST
HTTP Header Accept
= application/json
HTTP Header Content-Type
= application/json
Body of the request:
{
"iinc_id": 2000
, "summary": "New incidence"
, "taxid": "B99999999"
, "specific_field1": null
, "specific_field2": null
}
In both examples, when the Service receives this request, it executes the following query:
INSERT INTO internet_inc SET
summary = 'New incidence'
, iinc_id = 2000
, specific_field1 = null
, specific_field2 = null
, taxid = 'B99999999';
UPDATE Queries (PUT)¶
To execute an UPDATE
query over a view, you have to send a PUT
request to a URL that represents a row of view (not a view).
The syntax of the URL to execute UPDATE
queries is
http://localhost:9090/denodo-restfulws/<database name>/views/<view name>/<primary key values>
E.g. http://localhost:9090/denodo-restfulws/admin/views/internet_inc/12
The final segment of the URL (12
) is the value of the primary key,
which in the view internet_inc
is formed by the field IINC_ID
.
If the primary key is formed by more than one field, you have to put the
values of each field separated by comma (,
). Therefore, if any
of the primary key values has this character, you have to replace it
with %2C
to avoid interpreting it as the separator of primary
key values.
If the Service processes the request correctly, it returns the HTTP code 204 (“No content”).
As the Service uses the value of the primary key to identify the row that you want to update, the views without primary key cannot be updated from the RESTful Web service.
Below, there 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 (XML)
URL = http://localhost:9090/denodo-restfulws/admin/views/internet_inc/2
HTTP Method = PUT
HTTP Header Accept
= application/xml
HTTP Header Content-Type
= application/xml
Body of the request:
<?xml version="1.0" encoding="UTF-8"?>
<internet_inc xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<summary>Incident in ADSL router ...</summary>
</internet_inc>
Example 2 (JSON)
URL = http://localhost:9090/denodo-restfulws/admin/views/internet_inc/2
HTTP Method = PUT
HTTP Header Accept
= application/json
HTTP Header Content-Type
= application/json
Body of the request:
{
"summary": "Incident in ADSL router ..."
}
In both examples, when the Service receives this request, it executes the following query:
UPDATE internet_inc 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
).
DELETE Queries¶
To execute a DELETE
query over a view, you have to send a DELETE
request to a URL that represents a row of view (not a view).
The syntax of the URL to execute DELETE
queries is
http://localhost:9090/denodo-restfulws/<database name>/views/<view name>/<primary key values>
E.g. http://localhost:9090/denodo-restfulws/admin/views/internet_inc/12.
In this example, the final segment of the URL (12
) is the value of
the primary key, which in the view internet_inc
is formed by the
field IINC_ID
. As we have seen in the previous section, if the
primary key is formed by more than one field, you have to put the values
of each field separated by comma (,
). Therefore, if any of the
primary key values has this character, you have to replace it with
%2C
.
If the Service processes the request correctly, it returns the HTTP code 204 (“No content”).
As the Service uses the value of the primary key to identify the row that you want to delete, the views without primary key cannot be deleted from the RESTful Web service.
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
.