Getting Started With VoltDB

contents

3.5. Writing the Stored Procedure for Inserting Records

VoltDB stored procedures are written in Java, using special VoltDB classes to declare, queue, and execute SQL queries. We need two stored procedures: one to load records into the database and one to retrieve a matched pair based on the language specified.

Let's start by creating the stored procedure to load the data first. Start a text editor to create a new Java class file called Insert.java.

Your VoltDB stored procedures must start by importing the appropriate VoltDB libraries. Type (or cut and paste) the following statement into Insert.java:

import org.voltdb.*;

Next, start a Java class using the name of the stored procedure. In this case, the class name is Insert:

public class Insert extends VoltProcedure {

Note that the class extends the prototype VoltProcedure, which provides additional functions that are used when writing the body of the stored procedure to execute database statements and handle the return values.

The stored procedure itself consists of two main statements: the definition of an SQL statement template (using question marks where values will be filled in later) and a method to actually execute the procedure. Type the following code into your file insert.java:

  public final SQLStmt sql = new SQLStmt(
      "INSERT INTO HELLOWORLD VALUES (?, ?, ?);"
  );

  public VoltTable[] run( String language,
                          String hello, 
                          String world) 
      throws VoltAbortException {

The method contains the steps of the stored procedure. In this case, there is just one step: inserting a record into the HELLOWORLD table. This is done in two parts. First the SQL statement is put into a queue, specifying the necessary data to complete the statement template. (In this case, the variables hello, world, and language replace the question marks in the template). Then the queue is executed. Add the following statements to your file:

      voltQueueSQL( sql, hello, world, language );
      voltExecuteSQL();

Your procedure is complete. Normally, you want to evaluate the return value from the SQL statement. But for now you can assume the statement succeeds. Return a null and complete the method and class by adding closing braces.

      return null;
   }
}

Save the file and close the text editor. The completed procedure file is shown in Example A.2, “Insert.java”.

contents