Storing Datasets

Modifying Datasets - Single Modification

Now we are ready to modify some data. To do this, we submit an http request with request method PUT (update), POST (insert), or DELETE (delete). These requests may specify either a single record, or an array of one or more records.

Note: For clients written in Adobe Flex, the framework you use may force you to use only GET or POST. In this case you may specify an override method (POST, POST, DELETE) to Jarvis by passing the _method parameter as a CGI parameter in the GET or POST request.

In return you will receive a response body containing a matching single record, or a matching array of one or more records. Let us consider a practical case. Here is a POST request body to insert a record into the boat table in the demo database in the single record (non-array) case.

The Content-Type must be application/json or text/json for JSON, and application/xml or `text/xml for XML.

In JSON the request is as follows:

(Request-Line) POST /jarvis-agent/demo/boat HTTP/1.1
Content-Type    application/json; charset=UTF-8
{
    "id"               : 0,
    "name"             : "New Boat Name",
    "class"            : "Makkleson",
    "registration_num" : 0,
    "_record_id"       : 1007
}

Corresponding request in XML:

(Request-Line) POST /jarvis-agent/demo/boat HTTP/1.1
Content-Type    application/xml; charset=UTF-8
<request>
    <id>0</id>
    <name>New Boat Name</name>
    <class>Makkleson</class>
    <registration_num>0</registration_num>
    <_record_id>12</_record_id>
</request>

The SQL in the <insert> statement is:

    ...
    <insert returning="yes">
        INSERT INTO boat (name, registration_num, class, owner, description, change_user, change_date)
        VALUES (
            {$name},
            NULLIF ({$registration_num}, 0),
            NULLIF (BTRIM ({$class}), ''),
            NULLIF (BTRIM ({$owner}), ''),
            NULLIF (BTRIM ({$description}), ''),
            {$__username}, now()
        ) RETURNING {$_record_id}::integer as _record_id, id;
    </insert>
    ...

The user parameters in the JSON record are substituted into the SQL, along with any server side default_parameters values, and any secure parameters such as {$__username}. The statement is prepared and executed. Because the attribute returning is defined as yes, then the insert statement results are fetched and returned in a returning parameter of the resulting JSON.

The response is correspondingly a single record, e.g response in JSON:

(Status-Line)   HTTP/1.1 200 OK
Content-Type    application/json; charset=ISO-8859-1
{
    "success" : 1,
    "returning" : [
        {
            "_record_id" : "1009",
            "id"         : "16"
        }
    ],
    "modified" : 1
}

Response in XML:

<?xml version="1.0" encoding="iso-8859-1" ?>
<?meta name="GENERATOR" content="XML::Smart/1.6.9 Perl/5.010000 [linux]" ?>
<response success="1" modified="1">
    <returning _record_id="1206" id="639"/>
</response>

In either case, the top-level attributes of the returned object are as follows if the http_code_on_store_error flag at the Jarvis -> App configuration level is set to no (or not specified).

If the http_code_on_store_error flag is set to yes, only successful store actions return a 200 success. Any failure received from the database (e.g. due to a constraint error) will cause Jarvis to generate a HTTP 500 error back.

Modifying Datasets - Array of Modifications

The array update case allows multiple transactions of the same type to be performed at once, for better throughput. Only transactions of the same type (i.e. either UPDATE or INSERT or DELETE) may be performed within a single Jarvis http request. If you require a mix of transaction types, you must send separate requests for each type.

If your database supports the MERGE statement (e.g. recent Oracle versions, or PostgreSQL, or SQL Server 2008) then you could write your <update> SQL statement in your dataset definition file to be a combined insert/update statement. Alternatively you could write a stored procedure to do the same thing.

For SQL Server, see: http://weblogs.sqlteam.com/mladenp/archive/2007/07/30/60273.aspx

Request Format

The format of the array modification request is simply an array of single modifications.

Note: An array with one element (an array modification) will generate a different response from a single modification (not in an array).

Here is a request updating one toggle to true and another toggle to false. Request in JSON:

[
    { "key": "music", "name": "Reprints"   , "toggle": true  },
    { "key": "music", "name": "Second Hand", "toggle": false }
]

In XML an parallel structure with <request> and <row> tags.

Note: The parameters may be given as attributes OR elements. E.g. the following two XML requests will give identical results.

<request>
    <row key="music" name="Reprints" toggle="true"/>
    <row key="music" name="Second Hand" toggle="false"/>
</request>

.. or ..

<request>
    <row>
        <key>music</key>
        <name>Reprints</name>
        <toggle>true</toggle>
    </row>
    <row>
        <key>music</key>
        <name>Second Hand</name>
        <toggle>false</toggle>
    </row>
</request>

The response in JSON format might be:

{
    "success" : 1,
    "row" : [
        {
            "success"  : 1,
            "modified" : 1
        },
        {
            "success"  : 1,
            "modified" : 1
        }
    ],
    "modified" : 2
}

Or in XML the response might be:

<?xml version="1.0" encoding="iso-8859-1" ?>
<?meta name="GENERATOR" content="XML::Smart/1.6.9 Perl/5.010000 [linux]" ?>
<response success="1" modified="2">
    <results>
        <row modified="1" success="1"/>
        <row modified="1" success="1"/>
    </results>
</response>

Note: The preceding example does not show the optional returning clause. The attributes of each returned row entry are as follows.

Modifying Datasets - Array of Mixed Modifications

In the previous section describing array modifications, all of the changes in the transaction needed to be of a single type – e.g. all update, all insert, all delete, etc. This is according to the RESTful standard, which requires the method to be specified at the top level of the request.

However, in practical terms, it is often desirable to perform a single transaction which consists of a mixture of insert/update/delete on the same dataset. Jarvis does support that, with the MIXED method request.

Simply specify MIXED as the request method. Then, for each row, specify the _ttype parameter as one of the per-row attributes or elements. E.g.

<request>
	<row>
        <_ttype="update" key="music" name="Reprints" toggle="true"/>
    </row>
	<row>
        <_ttype="insert" key="music" name="Second Hand" toggle="false"/>
    </row>
	<row>
        <_ttype="delete" key="music" name="Underfunded"/>
    </row>
</request>

All of these modifications will be performed within a single begin/end transaction, including (if defined) any before and/or after statements as described subsequently.

Before & After Statements

The before and after elements in a dataset allow you to specify optional SQL to be executed at the beginning and end of a modification transaction (either array or single). This allows you to perform the equivalent of Pre-Commit triggers in databases which do not otherwise support them.

When performing array modifications, all changes are performed within a bounding begin/end transaction. The actual sequence for single http/s modification request is as follows.

• Begin transaction.
• Perform before SQL (if configured for this dataset).
• Perform the one single or one-or-more array modifications in the request body.
• Perform after SQL (if configured for this dataset).
• Commit transaction.

An error at any stage will cause the entire transaction to be rolled back.

Note: Any before or after statements may contain references to:

1. Secure Dataset Parameters (such as {__username} and {__group_list}.
2. Indexed RESTful arguments as specified in the URL e.g. {1} or {2}.
3. Named RESTful arguments assigned by a route rule (see the router configuration).

However, before and after statements naturally do not have access to any of the per-row parameters in the request body.

Note: The before and after statements are not invoked for select operations, only for modification operations.

Transaction and Rollback

It is important to note:

• For fetch statements in a dataset (i.e. the <select> attributes), no explicit transaction is performed.
• For store statements in a dataset (i.e. <insert>/<update>/<delete> attributes), an outer transaction is automatically applied.

Note: This is not based on the textual content of the SQL, but on which named attribute tag is being executed.

This means that if your <select> attribute SQL performs multiple data modifications as a side-effect, then a failure in a later step will not rollback any earlier changes. If you require a transaction around the modifications, then either define it as an <insert>/<update>/<delete> and use the appropriate REST action to invoke it, or else implement your own transaction within your SQL body.

Here is an example of a case where <insert> element SQL was used with an array of two new rows being requested. In this case, one failed. All changes are rolled back:

{
   "success" : 0,
   "message" : "ERROR:  duplicate key violates unique constraint \"boat_pkey\""
}

Noting:

• Even though the first modification succeeded, it was rolled back.
• The first failing error message is shown.
• The row elements and modified keys are not present.

Note: SSAS Data Pump datasets support only the fetch mechanism. There is no writeback.