8.2. JSON HTTP Interface

Documentation

VoltDB Home » Documentation » Using VoltDB

8.2. JSON HTTP Interface

JSON (JavaScript Object Notation) is not a programming language; it is a data format. The JSON "interface" to VoltDB is actually a web interface that the VoltDB database server makes available for processing requests and returning data in JSON format.

The JSON interface lets you invoke VoltDB stored procedures and receive their results through HTTP requests. To invoke a stored procedure, you pass VoltDB the procedure name and parameters as a querystring to the HTTP request, using either the GET or POST method.

Although many programming languages provide methods to simplify the encoding and decoding of JSON strings, you still need to understand the data structures that are created. So if you are not familiar with JSON encoding, you may want to read more about it at http://www.json.org.

8.2.1. How the JSON Interface Works

When a VoltDB database starts, it opens port 8080 on each server as a simple web server. You have complete control over this feature through the configuration file and the voltdb start command, including:

  • Disabling just the JSON interface, or the HTTP port entirely using the <httpd> element in the configuration file.

  • Enabling TLS encryption on the port using the <ssl> element.

  • Changing the port number using the --http flag on the voltdb start command.

See the section on the "Web Interface Port" in the VoltDB Administrator's Guide for more information on configuring the HTTP port.

This section assumes the database is using the default httpd configuration. In which case, any HTTP requests sent to the location /api/2.0/ on that port are interpreted as JSON requests to run a stored procedure. The structure of the request is:

URLhttp://<server>:8080/api/2.0/
Arguments

Procedure=<procedure-name>
Parameters=<procedure-parameters>
User=<username for authentication>
Password=<password for authentication>
Hashedpassword=<Hashed password for authentication>
admin=<true|false>
jsonp=<function-name>

The arguments can be passed either using the GET or the POST method. For example, the following URL uses the GET method (where the arguments are appended to the URL) to execute the system procedure @SystemInformation on the VoltDB database running on node voltsvr.mycompany.com:

http://voltsvr.mycompany.com:8080/api/2.0/?Procedure=@SystemInformation

Note that only the Procedure argument is required. You can authenticate using the User and Password (or Hashedpassword) arguments if security is enabled for the database. Use Password to send the password as plain text or Hashedpassword to send the password as an encoded string. (The hashed password must be a 64-byte hex-encoding of the 32-byte SHA-256 hash.)[2]

You can also include the parameters on the request. However, it is important to note that the parameters — and the response returned by the stored procedure — are JSON encoded. The parameters are an array (even if there is only one element to that array) and therefore must be enclosed in square brackets. Also, although there is an upper limit of 2 megabytes for the entire length of the parameter string, large parameter sets must be sent using POST to avoid stricter limitations on allowable URL lengths.

The admin argument specifies whether the request is submitted on the standard client port (the default) or the admin port (when you specify admin=true). When the database is in admin mode, the client port is read-only; so you must submit write requests with admin=true or else the request is rejected by the server.

The jsonp argument is provided as a convenience for browser-based applications (such as Javascript) where cross-domain browsing is disabled. When you include the jsonp argument, the entire response is wrapped as a function call using the function name you specify. Using this technique, the response is a complete and valid Javascript statement and can be executed to create the appropriate language-specific object. For example, calling the @Statistics system procedure in Javascript using the jQuery library looks like this:

$.getJSON('http://myserver:8080/api/1.0/?Procedure=@Statistics' +
          '&Parameters=["MANAGEMENT",0]&jsonp=?',
          {},MyCallBack);

Perhaps the best way to understand the JSON interface is to see it in action. If you build and start the Hello World example application that is provided in the VoltDB distribution kit (including the client that loads data into the database), you can then open a web browser and connect to the local system through port 8080, to retrieve the French translation of "Hello World". For example:

http://localhost:8080/api/1.0/?Procedure=Select&Parameters=["French"]

The query returns the following results:

{"status":1,"appstatus":-128,"statusstring":null,"appstatusstring":null,
"results":{"0":[{ "HELLO":"Bonjour","WORLD":"Monde"}]}}

As you can see, the JSON-encoded results are not particularly easy to read. But they do provide a simple, generic interface accessible from almost any programming language, most of which provide methods for encoding and decoding JSON strings and interpreting their results.

8.2.2. Using the JSON Interface from Client Applications

The general process for using the JSON interface from within a program is:

  1. Encode the parameters for the stored procedure as a JSON-encoded string

  2. Instantiate and execute an HTTP request, passing the name of the procedure and the parameters as arguments using either GET or POST.

  3. Decode the resulting JSON string into a language-specific data structure and interpret the results.

The following are examples of invoking the Hello World Insert stored procedure from several different languages. In each case, the three arguments (the name of the language and the words for "Hello" and "World") are encoded as a JSON string.

PHP

<?php
// Construct the procedure name, parameter list, and URL.
 
   $voltdbserver = "http://myserver:8080/api/2.0/";
   $proc = "Insert";
   $a = array("Croatian","Pozdrav","Svijet");
   $params = json_encode($a);
   $params = urlencode($params);
   $querystring = "Procedure=$proc&Parameters=$params";

// create a new cURL resource and set options
   $ch = curl_init();
   curl_setopt($ch, CURLOPT_URL, $voltdbserver);
   curl_setopt($ch, CURLOPT_HEADER, 0);
   curl_setopt($ch, CURLOPT_FAILONERROR, 1);
   curl_setopt($ch, CURLOPT_POST, 1);
   curl_setopt($ch, CURLOPT_POSTFIELDS, $querystring);
   curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);

// Execute the request
   $resultstring = curl_exec($ch);
?>

Python

import urllib
import urllib2
import json

# Construct the procedure name, parameter list, and URL.
url = 'http://myserver:8080/api/2.0/'
voltparams = json.dumps(["Croatian","Pozdrav","Svijet"])
httpparams = urllib.urlencode({
    'Procedure': 'Insert',
    'Parameters' : voltparams
})
print httpparams
# Execute the request
data = urllib2.urlopen(url, httpparams).read()

# Decode the results
result = json.loads(data)

Perl

use LWP::Simple;

my $server = 'http://myserver:8080/api/2.0/';

# Insert "Hello World" in Croatian
my $proc = 'Insert';
my $params = '["Croatian","Pozdrav","Svijet"]';
my $url = $server . "?Procedure=$proc&Parameters=$params";
my $content = get $url;
die "Couldn't get $url" unless defined $content;

C#

using System;
using System.Text;
using System.Net;
using System.IO;

namespace hellovolt

{
  class Program
  {
    static void Main(string[] args)
    {
      string VoltDBServer = "http://myserver:8080/api/2.0/";
      string VoltDBProc = "Insert";
      string VoltDBParams = "[\"Croatian\",\"Pozdrav\",\"Svijet\"]";
      string Url = VoltDBServer + "?Procedure=" + VoltDBProc 
                   + "&Parameters=" + VoltDBParams;
 
      string result = null;
      WebResponse response = null;
      StreamReader reader = null;

      try
      {
        HttpWebRequest request = (HttpWebRequest)WebRequest.Create(Url);
        request.Method = "GET";
        response = request.GetResponse();
        reader = new StreamReader(response.GetResponseStream(),Encoding.UTF8 );
        result = reader.ReadToEnd();

      }
      catch (Exception ex)

      {     // handle error
        Console.WriteLine( ex.Message );
      }
      finally
      { 
        if (reader != null)reader.Close();
        if (response != null) response.Close();

      }
    }
  }
}

8.2.3. How Parameters Are Interpreted

When you pass arguments to the stored procedure through the JSON interface, VoltDB does its best to map the data to the datatype required by the stored procedure. This is important to make sure partitioning values are interpreted correctly.

For integer values, the JSON interface maps the parameter to the smallest possible integer type capable of holding the value. (For example, BYTE for values less than 128). Any values containing a decimal point are interpreted as DOUBLE.

String values (those that are quoted) are handled in several different ways. If the stored procedure is expecting a BIGDECIMAL, the JSON interface will try to interpret the quoted string as a decimal value. If the stored procedure is expecting a TIMESTAMP, the JSON interface will try to interpret the quoted string as a JDBC-encoded timestamp value. (You can alternately pass the argument as an integer value representing the number of microseconds from the epoch.) Otherwise, quoted strings are interpreted as a string datatype.

Table 8.1, “Datatypes in the JSON Interface” summarizes how to pass different datatypes in the JSON interface.

Table 8.1. Datatypes in the JSON Interface

DatatypeHow to PassExample
Integers (Byte, Short, Integer, Long)An integer value12345
DOUBLEA value with a decimal point123.45
BIGDECIMALA quoted string containing a value with a decimal point"123.45"
TIMESTAMPEither an integer value or a quoted string containing a JDBC-encoded date and time

12345

"2010-07-01 12:30:21"

StringA quoted string"I am a string"

8.2.4. Interpreting the JSON Results

Making the request and decoding the result string are only the first steps. Once the request is completed, your application needs to interpret the results.

When you decode a JSON string, it is converted into a language-specific structure within your application, composed of objects and arrays. If your request is successful, VoltDB returns a JSON-encoded string that represents the same ClientResponse object returned by calls to the callProcedure method in the Java client interface. Figure 8.1, “The Structure of the VoltDB JSON Response” shows the structure of the object returned by the JSON interface.

Figure 8.1. The Structure of the VoltDB JSON Response

{  status                   (integer)
   appstatus                (integer)
   statusstring             (string)
   appstatusstring          (string)
   results                  (list)
     {  result-index        (array)
          [ 
             { column-name  (any type)  ,...
             }
          ]
     }
}

The key components of the JSON response are the following:

status

Indicates the success or failure of the stored procedure. If status is false, statusstring contains the text of the status message..

appstatus

Returns additional information, provided by the application developer, about the success or failure of the stored procedure. The values of appstatus and appstatusstring can be set programmatically in the stored procedure. (See Section 6.5.1, “Interpreting Execution Errors” for details.)

results

A list of objects representing the VoltTables returned by the stored procedure. Each element of the list is one set of results, identified by an index value ("0", "1", "2" and so on). Within each set is an array of rows. And within each row is a list of columns represented by the column name and value. If the stored procedure does not return any results (i.e. is void or null), then the results object will be null.

It is possible to create a generic procedure for testing and evaluating the result values from any VoltDB stored procedure. However, in most cases it is far more expedient to evaluate the values that you know the individual procedures return.

For example, again using the Hello World example that is provided with the VoltDB software, it is possible to use the JSON interface to call the Select stored procedure and return the values for "Hello" and "World" in a specific language. Rather than evaluate the entire results array (including the name and type fields), we know we are only receiving one result object with two column values. So we can simplify the code, as in the following python example:

import urllib
import urllib2
import json
import pprint

# Construct the procedure name, parameter list, and URL.
url = 'http://localhost:8080/api/2.0/'
voltparams = json.dumps(["French"])
httpparams = urllib.urlencode({
    'Procedure': 'Select',
    'Parameters' : voltparams
})

# Execute the request
data = urllib2.urlopen(url, httpparams).read()

# Decode the results
results = json.loads(data)[u'results']
volttable = results[u'0']
row = volttable[0]

# Get the data by column name and display them
hello = row[u'HELLO']
world = row[u'WORLD']

print hello, world

8.2.5. Error Handling using the JSON Interface

There are a number of different reasons why a stored procedure request using the JSON interface may fail: the VoltDB server may be unreachable, the database may not be started yet, the stored procedure name may be misspelled, the stored procedure itself may fail... When using the standard Java client interface, these different situations are handled at different times. (For example, server and database access issues are addressed when instantiating the client, whereas stored procedure errors can be handled when the procedures themselves are called.) The JSON interface simplifies the programming by rolling all of these activities into a single call. But you must be more organized in how you handle errors as a consequence.

When using the JSON interface, you should check for errors in the following order:

  1. First check to see that the HTTP request was submitted without errors. How this is done depends on what language-specific methods you use for submitting the request. In most cases, you can use the appropriate programming language error handlers (such as try-catch) to catch and interpret HTTP request errors.

  2. Next check to see if VoltDB successfully invoked the stored procedure. You can do this by verifying that the HTTP request returned a valid JSON-encoded string and that its status is set to true.

  3. If the VoltDB server successfully invoked the stored procedure, then check to see if the stored procedure itself succeeded, by checking to see if appstatus is true.

  4. Finally, check to see that the results are what you expect. (For example, that the data array is non-empty and contains the values you need.)



[2] Hashing the password stops the text of your password from being detectable from network traffic. However, it does not make the database access any more secure. To secure the transmission of credentials and data between client applications and VoltDB, enable TLS encryption for the HTTP port using the configuration file.