7.3. Verifying Expected Query Results

Documentation

VoltDB Home » Documentation » Using VoltDB

7.3. Verifying Expected Query Results

The automated default and simple stored procedures reduce the coding needed to perform simple queries. However, another substantial chunk of stored procedure and client application code is often required to verify the correctness of the results returned by the queries. Did you get the right number of records? Does the query return the correct value?

Rather than you having to write the code to validate the query results manually, VoltDB provides a way to perform several common validations as part of the query itself. The Java client interface includes an Expectation object that you can use to define the expected results of a query. Then, if the query does not meet those expectations, the executing stored procedure automatically throws a VoltAbortException and rolls back.

You specify the expectation as the second parameter (after the SQL statement but before any arguments) when queuing the query. For example, when making a reservation in the Flight application, the procedure must make sure there are seats available. To do this, the procedure must determine how many seats the flight has. This query can also be used to verify that the flight itself exists, because there should be one and only one record for every flight ID.

The following code fragment uses the EXPECT_ONE_ROW expectation to both fetch the number of seats and verify that the flight itself exists and is unique.

import org.voltdb.Expectation;
           .
           .
           .
public final SQLStmt GetSeats = new SQLStmt(
        "SELECT numberofseats FROM Flight WHERE flightid=?;");
 
voltQueueSQL(GetSeats, EXPECT_ONE_ROW, flightid);
VoltTable[] recordset = voltExecuteSQL();
Long numofseats = recordset[0].asScalarLong();

By using the expectation, the stored procedure code does not need to do additional error checking to verify that there is one and only one row in the result set. The following table describes all of the expectations that are available to use in stored procedures.

ExpectationDescription
EXPECT_EMPTYThe query must return no rows.
EXPECT_ONE_ROWThe query must return one and only one row.
EXPECT_ZERO_OR_​ONE_ROWThe query must return no more than one row.
EXPECT_NON_EMPTYThe query must return at least one row.
EXPECT_SCALARThe query must return a single value (that is, one row with one column).
EXPECT_SCALAR_​LONGThe query must return a single value with a datatype of Long.
EXPECT_SCALAR_​MATCH( long )The query must return a single value equal to the specified Long value.