Using VoltDB

contents

4.2. Shortcut for Defining Simple Stored Procedures

Sometimes all you want is to execute a single SQL query and return the results to the calling application. In these simple cases, writing the necessary Java code can be tedious. So VoltDB provides a shortcut.

For very simple stored procedures that execute a single SQL query and return the results, you can define the entire stored procedure as part of the database schema. Normally, the schema contains entries that identify each of the stored procedures, like so:

CREATE PROCEDURE FROM CLASS procedures.MakeReservation;
CREATE PROCEDURE FROM CLASS procedures.CancelReservation;

The CREATE PROCEDURE statement specifies the class name of the Java procedure you write. However, to create procedures without writing any Java, you can simply insert the SQL query in the AS clause:

CREATE PROCEDURE procedures.simple.CountReservations AS
    SELECT COUNT(*) FROM RESERVATION;

When you include the SQL query in the CREATE PROCEDURE AS statement, VoltDB generates the necessary Java code for you and compiles it when you build your application (as described in Section 5.3, “Building the Application Catalog”). Note that you must still provide a unique class name for the procedure. It is a good idea to put these simplified procedures into a separate package (procedures.simple, in the preceding example) from those written by hand.

It is also possible to pass arguments to the SQL query in simple stored procedures. If you use the question mark placeholder in the SQL, any additional arguments you pass through the callProcedure method are used to replace the placeholders, in their respective order. For example, the following simple stored procedure expects to receive three additional parameters:

CREATE PROCEDURE procedures.simple.MyReservationsByTrip AS
        SELECT R.RESERVEID, F.FLIGHTID, F.DEPARTTIME 
        FROM RESERVATION AS R, FLIGHT AS F
        WHERE R.CUSTOMERID = ?  
        AND R.FLIGHTID = F.FLIGHTID
        AND F.ORIGIN=? AND F.DESTINATION=?;

Finally, you can also specify whether the simple procedure is single-partitioned or not. By default, simple stored procedures are assumed to be multi-partitioned. But if your procedure is single-partitioned, you can specify the partitioning information in a PARTITION PROCEDURE statement. In the following example, the stored procedure is partitioned on the FLIGHTID column of the RESERVATION table using the first parameter as the partitioning key.

CREATE PROCEDURE procedures.simple.FetchReservations AS
   SELECT * FROM RESERVATION WHERE FLIGHTID=?;
PARTITION PROCEDURE procedures.simple.FetchReservations 
   ON TABLE Reservation COLUMN flightid;
contents