15.2. JSON HTTP Interface

Documentation

Home » Documentation » Using VoltDB

15.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.

15.2.1. How the JSON Interface Works

To use the VoltDB JSON interface, you must first enable JSON in the deployment file. You do this by adding the following tags to the deployment file:

<httpd>
   <jsonapi enabled="true"/>
</httpd>

With JSON enabled, when a VoltDB database starts it opens port 8080[1] on the local machine as a simple web server. Any HTTP requests sent to the location /api/1.0/ on that port are interpreted as requests to run a stored procedure. The structure of the request is:

URLhttp://<server>:8080/api/1.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/1.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 a SHA-1 encoded string. (The hashed password must be a 40-byte hex-encoding of the 20-byte SHA-1 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.

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). If the database is in admin mode, you must submit requests over the admin port or else the request is rejected by the server.

The admin port should be used for administrative tasks only. Although all stored procedures can be invoked through the admin port, using the admin port through JSON is far less efficient than using the client port. All admin mode requests to JSON are separate synchronous requests; whereas calls to the normal client port are asynchronous through a shared session.

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 resulting display is the following:

{"status":1,"appstatus":-128,"statusstring":null,"appstatusstring":null,
"exception":null,"results":[{"status":-128,"schema":[{"name":"HELLO",
"type":9},{"name":"WORLD","type":9}],"data":[["Bonjour","Monde"]]}]}

As you can see, the results (which are a JSON-encoded string) are not particularly easy to read. But then, the JSON interface is not really intended for human consumption. It's real purpose is to provide a generic interface accessible from almost any programming language, many of which already provide methods for encoding and decoding JSON strings and interpreting their results.

15.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

// Construct the procedure name, parameter list, and URL.
 
   $voltdbserver = "http://myserver:8080/api/1.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/1.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/1.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/1.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();

      }
    }
  }
}

15.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 15.1, “Datatypes in the JSON Interface” summarizes how to pass different datatypes in the JSON interface.

Table 15.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"

15.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 15.1, “The Structure of the VoltDB JSON Response” shows the structure of the object returned by the JSON interface.

Figure 15.1. The Structure of the VoltDB JSON Response

{  appstatus           (integer, boolean)
   appstatusstring     (string)
   exception           (integer)
   results             (array)
     [                 (object, VoltTable)
         {  data       (array)
              [        (any type)
              ]
            schema     (array)
              [  name  (string)
                 type  (integer, enumerated)
              ]
            status     (integer, boolean)
         }
     ]
   status              (integer)
   statusstring        (string)
}

The key components of the JSON response are the following:

appstatus

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

results

An array of objects representing the data returned by the stored procedure. This is an array of VoltTable objects. If the stored procedure does not return a value (i.e. is void or null), then results will be null.

data

Within each VoltTable object, data is the array of values.

schema

Within each VoltTable, object schema is an array of objects with two elements: the name of the field and the datatype of that field (encoded as an enumerated integer value).

status

Indicates the success or failure of the VoltDB server in its attempt to execute the stored procedure. The difference between appstatus and status is that if the server cannot execute the stored procedure, the status is returned in status, whereas if the stored procedure can be invoked, but a failure occurs within the stored procedure itself (such as a SQL constraint violation), the status is returned in appstatus.

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 VoltTable object with two string elements. 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/1.0/'
voltparams = json.dumps(["French"])
httpparams = urllib.urlencode({
    'Procedure': 'Select',
    'Parameters' : voltparams
})

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

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

# Get the data as a simple array and display them
foreignwords = result[u'results'][0][u'data'][0]

print foreignwords[0], foreignwords[1]

15.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.)



[1] You can specify an alternate port for the JSON interface when you start the VoltDB server by including the port number as an attribute of the <httpd> tag in the deployment file. For example: <httpd port="{port-number}">.

[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, use an SSL proxy server in front of the database servers.