5.2. The Anatomy of a VoltDB Stored Procedure

Documentation

VoltDB Home » Documentation » Using VoltDB

5.2. The Anatomy of a VoltDB Stored Procedure

You can write VoltDB stored procedures as Java classes. The following code sample illustrates the basic structure of a VoltDB java stored procedure.

import org.voltdb.*;
 
public class Procedure-name extends VoltProcedure {
 
                 // Declare SQL statements ...
 
    public datatype run ( arguments ) throws VoltAbortException {
 
                // Body of the Stored Procedure ...
 
    }
}

The key points to remember are to:

  1. Import the VoltDB classes from org.voltdb.*

  2. Include the class definition, which extends the abstract class VoltProcedure

  3. Define the method run(), which performs the SQL queries and processing that make up the transaction

It is important to understand the details of how to design and develop stored procedures for your application as described in the following sections. However, for simple data access, the following techniques may suffice for some of your stored procedures:

The following sections describe the components of a stored procedure in more detail.

5.2.1. The Structure of the Stored Procedure

The stored procedures themselves are written as Java classes, each procedure being a separate class. Example 5.1, “Components of a VoltDB Java Stored Procedure” shows the stored procedure that looks up a flight to see if there are any available seats. The callouts identify the key components of a VoltDB stored procedure.

Example 5.1. Components of a VoltDB Java Stored Procedure

package fadvisor.procedures;                                         
 
import org.voltdb.*;                                         1       
 
public class HowManySeats extends VoltProcedure {            2      
 
   public final SQLStmt GetSeatCount = new SQLStmt(          3    
        "SELECT NumberOfSeats, COUNT(ReserveID) " +
        "FROM Flight AS F, Reservation AS R " +
        "WHERE F.FlightID=R.FlightID AND R.FlightID=? " +
        "GROUP BY NumberOfSeats;");
 
    public long run( int flightid)                           4    
        throws VoltAbortException {  
 
        long numofseats;
        long seatsinuse;
        VoltTable[] queryresults;
 
        voltQueueSQL( GetSeatCount, flightid);               5     
 
        queryresults = voltExecuteSQL();                     6     
 
        VoltTable result = queryresults[0];                  7      
        if (result.getRowCount() < 1) { return -1; }
        numofseats = result.fetchRow(0).getLong(0);          8
        seatsinuse = result.fetchRow(0).getLong(1);
        numofseats = numofseats - seatsinuse;
        return numofseats; // Return available seats         9
    }
}

1

Stored procedures are written as Java classes. To access the VoltDB classes and methods, be sure to import org.voltdb.*.

Although VoltDB stored procedures must be written in Java and the primary client interface is Java (as described in Chapter 6, Designing VoltDB Client Applications), it is possible to write client applications using other programming languages. See Chapter 8, Using VoltDB with Other Programming Languages for more information on alternate client interfaces.

2

Each stored procedure extends the generic class VoltProcedure.

3

Within the stored procedure you access the database using ANSI-standard SQL statements. To do this, you declare the statement as a special Java type called SQLStmt, which must be declared as final.

In the SQL statement, you insert a question mark (?) everywhere you want to replace a value by a variable at runtime. In this example, the query GetSeatCount has one input variable, FlightID. (See Appendix B, Supported SQL Statements for details on the supported SQL statements.)

To ensure the stored procedure code is single partitioned, queries must filter on the partitioning column for a single value (using equal, =). Filtering for a range of values will not be single-partitioned because the code will have to look up in all the partitions to ensure the entire range is found.

4

The bulk of the stored procedure is the run() method, whose input specifies the input arguments for the stored procedure. See Section 5.2.2, “Passing Arguments to a Stored Procedure” next for details.

Note that the run() method throws the exception VoltAbortException if any exceptions are not caught. VoltAbortException causes the stored procedure transaction to rollback. (See Section 5.2.6, “Rolling Back a Transaction” for more information about rollback.)

5

To perform database queries, you queue SQL statements, specifying both the SQL statement and the variables it requires, using the voltQueueSQL() method. More details are described in Section 5.2.3, “Creating and Executing SQL Queries in Stored Procedures”.

6

After you queue all of the SQL statements you want to perform, use voltExecuteSQL() to execute the statements in the queue.

7

Each statement returns its results in a VoltTable structure. Because the queue can contain multiple queries, voltExecuteSQL() returns an array of VoltTable structures, one array element for each query. More details are described in Section 5.2.4, “Interpreting the Results of SQL Queries”.

8

In addition to queueing and executing queries, stored procedures can contain custom code. However, you should limit the amount of custom code in stored procedures to only that processing that is necessary to complete the transaction, so as not to delay subsequent transactions.

9

Stored procedures can return a long integer, a VoltTable structure, or an array of VoltTable structures. For more details, see Section 5.2.5, “Returning Results from a Stored Procedure”.

5.2.2. Passing Arguments to a Stored Procedure

You specify the number and type of the arguments that the stored procedure accepts in the run() method. For example, the following is the declaration of the run() method for an Initialize() stored procedure from the voter sample application found in the /doc/tutorials/voter folder where Volt is installed. This procedure accepts two arguments: an integer and a string.

public long run(int maxContestants, String contestants) { . . .

VoltDB stored procedures can accept parameters of any of the following Java and VoltDB datatypes:

Integer typesbyte, short, int, long, Byte, Short, Integer, and Long
Floating point typesfloat, double, Float, Double
Fixed decimal typesBigDecimal
String and binary typesString and byte[]
Timestamp types

org.voltdb.types.TimestampType 
java.util.Date, java.sql.Date, java.sql.Timestamp

VoltDB typeVoltTable

The arguments can be scalar objects or arrays of any of the preceding types. For example, the following run() method defines three arguments: a scalar long and two arrays, one array of timestamps and one array of Strings:

import org.voltdb.*;
public class LogMessagesByEvent extends VoltProcedure {
 
     public long run (
           long eventType, 
          org.voltdb.types.TimestampType[] eventTimeStamps,
          String[] eventMessages
    ) throws VoltAbortException {

The calling client application can use any of the preceding datatypes when invoking the callProcedure() method and, where necessary, VoltDB makes the appropriate type conversions (for example, from int to String or from String to Double). See Section 6.2, “Invoking Stored Procedures” for more on using the callProcedure() method.

5.2.3. Creating and Executing SQL Queries in Stored Procedures

The main function of the stored procedure is to perform database queries. In VoltDB this is done in two steps:

  1. Queue the queries using the voltQueueSQL() function

  2. Execute the queue and return the results using the voltExecuteSQL() function

Queuing SQL Statements The first argument to voltQueueSQL() is the SQL statement to be executed. The SQL statement is declared using a special class, SQLStmt, with question marks as placeholders for values that will be inserted at runtime.

The SQL statements must be declared as final and initialized at compile time, either when declared or within a constructor or static initializer. This allows the VoltDB planner to determine the optimal execution plan for each statement when the procedure is loaded and declared in the schema. To allow for code reuse, SQLStmt objects can be inherited from parent classes or constructed from other compile-time constants.

The remaining arguments to voltQueueSQL() are the actual values that VoltDB inserts into the placeholders. For example, if you want to perform a SELECT of a table using two columns in the WHERE clause, your SQL statement might look something like this:

SELECT CustomerID FROM Customer WHERE FirstName=? AND LastName=?;

At runtime, you want the questions marks replaced by values passed in as arguments from the calling application. So the actual voltQueueSQL() invocation might look like this:

public final SQLStmt getcustid = new SQLStmt(
                                "SELECT CustomerID FROM Customer " +
                                "WHERE FirstName=? AND LastName=?;");
 
     ...
 
voltQueueSQL(getcustid, firstnm, lastnm);

Your stored procedure can call voltQueueSQL() more than once to queue up multiple SQL statements before they are executed. Queuing multiple SQL statements improves performance when the SQL queries execute because it minimizes the amount of network traffic within the cluster. Once you have queued all of the SQL statements you want to execute together, you then process the queue using the voltExecuteSQL() function.

VoltTable[] queryresults = voltExecuteSQL();

Cycles of Queue and Execute

Your procedure can queue and execute SQL statements in as many cycles as necessary to complete the transaction. For example, if you want to make a flight reservation, you may need to access the database and verify that the flight exists before creating the reservation in the database. One way to do this is to look up the flight, verify that a valid row was returned, then insert the reservation, like so:

Example 5.2. Cycles of Queue and Execute in a Stored Procedure

final String getflight = "SELECT FlightID FROM Flight WHERE FlightID=?;";    1
final String makeres = "INSERT INTO Reservation (?,?,?,?,?);";
 
public final SQLStmt getflightsql = new SQLStmt(getflight);
public final SQLStmt makeressql = new SQLStmt(makeres);
 
public VoltTable[] run( int reservenum, int flightnum, int customernum )     2
        throws VoltAbortException {
 
        // Verify flight ID
  voltQueueSQL(getflightsql, flightnum);                                     3
  VoltTable[] queryresults = voltExecuteSQL();
 
        // If there is no matching record, rollback 
  if (queryresults[0].getRowCount() == 0 ) throw new VoltAbortException();   4
 
        // Make reservation
  voltQueueSQL(makeressql, reservenum, flightnum, customernum,0,0);          5
  return voltExecuteSQL();
}

This stored procedure code to make a reservation is described as follows:

1

Define the SQL statements to use. The getflight string contains an SQL statement that verifies the flight ID, and the makeres string contains the SQL statement that makes the reservation.

2

Define the run() method for the stored procedure. This stored procedure takes as input arguments the reservation number, the flight number, and the customer number.

3

Queue and execute an SQL statement. In this example the voltExecuteSQL() method processes the single getflightsql() function, which executes the SQL statement specified in the getflight string.

4

Process results. If the flight is not available, the exception VoltAbortException aborts the stored procedure and rolls back the transaction.

5

The second SQL statement to make the reservation is then queued and executed. The voltExecuteSQL() method processes the single makeressql() function, which executes the SQL statement specified in the makeres string.

5.2.4. Interpreting the Results of SQL Queries

With the voltExecuteSQL() call, the results of all the queued SQL statements are returned in an array of VoltTable structures. The array contains one VoltTable for each SQL statement in the queue. The VoltTable structures are returned in the same order as the respective SQL statements in the queue.

The VoltTable itself consists of rows, where each row contains columns, and each column has the column name and a value of a fixed datatype. The number of rows and columns per row depends on the specific query.

Figure 5.1. Array of VoltTable Structures

Array of VoltTable Structures

For example, if you queue two SQL SELECT statements, one looking for the destination of a specific flight and the second looking up the ReserveID and Customer name (first and last) of reservations for that flight, the code for the stored procedure might look like the following:

public final SQLStmt getdestsql = new SQLStmt(
              "SELECT Destination FROM Flight WHERE FlightID=?;");
public final SQLStmt getressql = new SQLStmt(
             "SELECT r.ReserveID, c.FirstName, c.LastName " +
             "FROM Reservation AS r, Customer AS c " +
             "WHERE r.FlightID=? AND r.CustomerID=c.CustomerID;");
 
         ...
 
   voltQueueSQL(getdestsql,flightnum);
   voltQueueSQL(getressql,flightnum);
   VoltTable[] results = voltExecuteSQL();

The array returned by voltExecuteSQL() will have two elements:

  • The first array element is a VoltTable with one row (FlightID is defined as unique) containing one column, because the SELECT statement returns only one value.

  • The second array element is a VoltTable with as many rows as there are reservations for the specific flight, each row containing three columns: ReserveID, FirstName, and LastName.

Assuming the stored procedure call input was a FlightID value of 134, the data returned for the second array element might be represented as follows:

Figure 5.2. One VoltTable Structure is returned for each Queued SQL Statement

One VoltTable Structure is returned for each Queued SQL Statement

VoltDB provides a set of convenience methods for accessing the contents of the VoltTable array. Table 5.1, “Methods of the VoltTable Classes” lists some of the most common methods. (See also Java Stored Procedure API.)

Table 5.1. Methods of the VoltTable Classes

MethodDescription

int fetchRow(int index)

Returns an instance of the VoltTableRow class for the row specified by index.

int getRowCount()

Returns the number of rows in the table.

int getColumnCount()

Returns the number of columns for each row in the table.

Type getColumnType(int index)

Returns the datatype of the column at the specified index. Type is an enumerated type with the following possible values:

BIGINT
DECIMAL
FLOAT
GEOGRAPHY
GEOGRAPHY_POINT
INTEGER
INVALID
NULL
NUMERIC
SMALLINT
STRING
TIMESTAMP
TINYINT
VARBINARY
VOLTTABLE

String getColumnName(int index)

Returns the name of the column at the specified index.

double getDouble(int index)
long getLong(int index)
String getString(int index)
BigDecimal getDecimalAsBigDecimal(int index)
double getDecimalAsDouble(int index)
Date getTimestampAsTimestamp(int index)
long getTimestampAsLong(int index)
byte[] getVarbinary(int index)

Methods of VoltTable.Row

Return the value of the column at the specified index in the appropriate datatype. Because the datatype of the columns vary depending on the SQL query, there is no generic method for returning the value. You must specify what datatype to use when fetching the value.


It is also possible to retrieve the column values by name. You can invoke any of the getDatatype() methods and pass a string argument specifying the name of the column, rather than the numeric index. Accessing the columns by name can make code easier to read and less susceptible to errors due to changes in the SQL schema (such as changing the order of the columns). On the other hand, accessing column values by numeric index is potentially more efficient under heavy load conditions.

Example 5.3, “Displaying the Contents of VoltTable Arrays” shows a generic routine for “walking” through the return results of a stored procedure. In this example, the contents of the VoltTable array are written to standard output.

Example 5.3. Displaying the Contents of VoltTable Arrays

public void displayResults(VoltTable[] results) {
  int table = 1;
     for (VoltTable result : results) {
      System.out.printf("*** Table %d ***\n",table++);
      displayTable(result);
   }
}
 
public void displayTable(VoltTable t) {
 
   final int colCount = t.getColumnCount();
   int rowCount = 1;
   t.resetRowPosition();
   while (t.advanceRow()) {
      System.out.printf("--- Row %d ---\n",rowCount++);

      for (int col=0; col<colCount; col++) {
         System.out.printf("%s: ",t.getColumnName(col));
         switch(t.getColumnType(col)) {
            case TINYINT: case SMALLINT: case BIGINT: case INTEGER:
               System.out.printf("%d\n", t.getLong(col));
               break;
            case STRING:
               System.out.printf("%s\n", t.getString(col));
               break;
            case DECIMAL:
               System.out.printf("%f\n", t.getDecimalAsBigDecimal(col));
               break;
            case FLOAT:
               System.out.printf("%f\n", t.getDouble(col));
               break;
         }
      }
   }
}

For further details on interpreting the VoltTable structure, see the Java documentation that is provided online in the doc/ subfolder for your VoltDB installation.

5.2.5. Returning Results from a Stored Procedure

Stored procedures can return the following types:

  • Long integer

  • Single VoltTable

  • Array of VoltTable structures

You can return all of the query results by returning the VoltTable array, or you can return a scalar value that is the logical result of the transaction. (For example, the stored procedure in Example 5.1, “Components of a VoltDB Java Stored Procedure” returns a long integer representing the number of remaining seats available in the flight.)

Whatever value the stored procedure returns, make sure the run() method includes the appropriate datatype in its definition. For example, the following two definitions specify different return datatypes; the first returns a long integer and the second returns the results of a SQL query as a VoltTable array.

public long run( int flightid)
 
public VoltTable[] run ( String lastname, String firstname)

Note that you can interpret the results of SQL queries either in the stored procedure or in the client application. However, for performance reasons, it is best to limit the amount of additional processing done by the stored procedure to ensure it executes quickly and frees the queue for the next stored procedure. So unless the processing is necessary for subsequent SQL queries, it is usually best to return the query results (in other words, the VoltTable array) directly to the calling application and interpret them there.

5.2.6. Rolling Back a Transaction

Finally, if a problem arises while a stored procedure is executing, whether the problem is anticipated or unexpected, it is important that the transaction rolls back. Rollback means that any changes made during the transaction are undone and the database is left in the same state it was in before the transaction started.

VoltDB is a fully transactional database, which means that if a transaction (stored procedure) fails, the transaction is automatically rolled back and the appropriate exception is returned to the calling application. Exceptions that can cause a rollback include the following:

  • Runtime errors in the stored procedure code, such as division by zero or datatype overflow.

  • Violating database constraints in SQL queries, such as inserting a duplicate value into a column defined as unique.

The atomicity of the stored procedure depends on VoltDB being able to roll back incomplete database changes. VoltDB relies on Java exception handling outside the stored procedure to perform the roll back. Therefore, you should not attempt to alter any exceptions thrown by the voltExecuteSql method. If your procedure code does catch exceptions thrown as a result of executing SQL statements, make sure that the exception handler re-throws the exception to allow VoltDB to perform the necessary roll back activities before the stored procedure returns to the calling program.

On the other hand, there may be situations where an exception occurs in the program logic. The issue might not be one that is caught by Java or VoltDB, but still there is no practical way for the transaction logic to complete. In these situations, you can force a rollback by explicitly throwing the VoltAbortException exception. For example, if a flight ID does not exist, you do not want to create a reservation so the stored procedure can force a rollback like so:

if (!flightid) { throw new VoltAbortException(); }

See Section 7.3, “Verifying Expected Query Results” for another way to roll back procedures when queries do not meet necessary conditions.