Result Formats

Result Formats

RdbHost delivers the results of a query as an XML, JSON or binary page; the structure of the JSON page is described here.

The error codes themselves are documented on their own page.

For most applications, JSON is the most straight-forward and easiest format to use. XML docs can be found here, and docs on the binary format can be found here.

JSON

A decoded result from RdbHost will always be a hash (as the outermost container) and will always have a 'status' key. The evaluation of the result set should always start with inspecting the value of the status element. Other elements may, or may not, be present, depending on the result status.

{ 'status': [ 'error', 'error message' ], ... }

The value of 'status' is an array of 2 elements. The first element is one of 'error', 'complete', 'incomplete', or 'deferred'. The second is 'OK' for successful results, and an error class-name for errors.

When 'status' is 'error', there will be an 'error' key, with a value that is an array of two elements.

{ 'status': [ 'error', 'OperationalError' ], 'error': [ '-', 'password mismatch' ], ... }

The first element is an error code, and the second is an error message; both of these are usually from an underlying component, such as PostgreSQL or SimpleJSON.

When the result is successful (not error), the remaining data elements will depend on whether the query was one statement, or more than one. Multiple SQL statements can be included in the query string, seperated by ';' characters.

Single Statement

First, we will consider the case of a single statement.

It the results were complete or incomplete, there will be a 'row_count' field like:

{ 'status': [ 'complete', 'OK' ], 'row_count': [ 1, '1 row affected' ], ... }

row_count is a 2-element array, containing a number, and a string. The row_count numerical value might be -1 or might indicate the number of records updated, inserted or returned from a SELECT. The row_count for a SELECT will generally not be more than 101, even if more qualifying records are in the database, simply because the server stops fetching at 101.

Results from a SELECT query will include a 'records' element, and that 'records' element will always include a 'header' element, and a 'rows' element:

{ "records": { "header": [ [ 21, "int_field" ], ], "rows": [ [ 2 ], ] }, "row_count": [ 2, "2 Rows Affected" ], "status": [ "complete", "OK" ] }

The 'header' element contains one item (a list) for each field in the selection set. Each such field element in the header is a 2-element list, containing PostgreSQLs field-type-code and the name of the field.

The 'rows' element may be empty, depending on whether the query returned any table rows. If it has data, it will contain a list of records; each record is a list of field values in whatever value type (integer, string, list) makes the most sense for that field type.

Request this format with the parameter 'format=json' (lower case).

Multiple Statements

The case of multiple statements in the query is similar, except that instead of having row_count and records elements as peers of status, the data object has one element 'result_sets', which is an array with one element for each statement. Each such element is a hash containing its own status element, and conditionally row_count and records elements, as described above. The status values within result_sets containers will always be ['complete','OK'] or ['incomplete','OK'], as any error results in a single error status and no result_sets.

{ "status": [ "complete", "OK" ], "result_sets": [ { "status": [ "complete", "OK" ], "row_count": [ 0, "0 Rows Affected" ] }, { "status": [ "complete", "OK" ], "row_count": [ 1, "1 Rows Affected" ], "records": { "header": [ [ 23, "int_field" ] ], "rows": [ [ 3 ] ] } } ] }

JSON-Easy

JSON-Easy format is like JSON, described above, except for how the fields are described. It converts using the same JSON method. The 'header', though, is a hash (AKA associative array, or dictionary), where the keys are field names and the values are PostgreSQL field-type-codes.

The 'rows' likewise are hashes, where the keys are the field names, and the values are the field values.

{ "records": { "header": { "a": 21, "b": 21 }, "rows": [ { "a": 2, "b": 1 } ] }, "row_count": [ 1, "1 Rows Affected" ], "status": [ "complete", "OK" ] }

The name JSON-Easy suggests the data are easier to use, as each record associates the field values with field names.

Request this format with the parameter 'format=json-easy' (lower case).

The examples in the Python and JavaScript pages use JSON, so look for further assistance there.

JSONP

The JSONP and JSONP-Easy formats are like JSON and JSON-Easy, described above, except that the data structure is wrapped in a function call. Because it is wrapped in such a manner, it can be loaded by a page using a script tag.

The JSON-Easy example above, as a JSONP-Easy result, would be like what is below, where the callback value was 'dojson'. The request could be made with 'format=jsonp-easy&callback=dojson' or 'format=jsonp-easy:dojson'. The former syntax is commonly used by web-services with JSONP, and the latter is consistent with other format variations at RdbHost.

dojson({ "records": { "header": { "a": 21, "b": 21 }, "rows": [ { "a": 2, "b": 1 } ] }, "row_count": [ 1, "1 Rows Affected" ], "status": ["complete", "OK"] })

We know of no purpose for using JSONP format from Python, but it can be very useful from JavaScript in working around cross-site scripting obstacles.

JSOND (described next) is generally preferrable to JSONP, as JSONP does not permit the complete range of operations. Specifically, it does not allow an authcode to be included in a GET request, and thus disallows any operation that requires authentication. The script tag implicitly uses a GET to retrieve the data, so JSONP type requests are always GET.