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.

package fadvisor.procedures;                                         
 
import org.voltdb.*;                                                
 
public class HowManySeats extends VoltProcedure {                  
 
   public final SQLStmt GetSeatCount = new SQLStmt(              
        "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)                               
        throws VoltAbortException {  
 
        long numofseats;
        long seatsinuse;
        VoltTable[] queryresults;
 
        voltQueueSQL( GetSeatCount, flightid);                    
 
        queryresults = voltExecuteSQL();                          
 
        VoltTable result = queryresults[0];                        
        if (result.getRowCount() < 1) { return -1; }
        numofseats = result.fetchRow(0).getLong(0);          
        seatsinuse = result.fetchRow(0).getLong(1);
        numofseats = numofseats - seatsinuse;
        return numofseats; // Return available seats         
    }
}

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

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.

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

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:

final String getflight = "SELECT FlightID FROM Flight WHERE FlightID=?;";    
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 )     
        throws VoltAbortException {
 
        // Verify flight ID
  voltQueueSQL(getflightsql, flightnum);                                     
  VoltTable[] queryresults = voltExecuteSQL();
 
        // If there is no matching record, rollback 
  if (queryresults[0].getRowCount() == 0 ) throw new VoltAbortException();   
 
        // Make reservation
  voltQueueSQL(makeressql, reservenum, flightnum, customernum,0,0);          
  return voltExecuteSQL();
}

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

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

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:

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

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

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.

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.

Stored procedures can return the following types:

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.

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:

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.