Archive
Archive
The previous chapter (Chapter 6, Designing VoltDB Client Applications) explains how to develop your VoltDB database application using the full power and flexibility of the Java client interface. However, some database tasks — such as inserting records into a table or retrieving a specific column value — do not need all of the capabilities that the Java API provides. In other cases, there are automation techniques that can reduce the amount of application code you need to write and maintain.
Now that you know how the VoltDB programming interface works, VoltDB has features to simplify common tasks and make your application development easier. Those features include:
The following sections describe each of these features separately.
Although it is possible to define quite complex SQL queries, often the simplest are also the most common. Inserting, selecting, updating, and deleting records based on a specific key value are the most basic operations for a database. Another common practice is upsert, where if a row matching the primary key already exists, the record is updated — if not, a new record is inserted. To simplify these operations, VoltDB defines these default stored procedures for tables.
The default stored procedures use a standard naming scheme, where the name of the procedure is composed of the name of
the table (in all uppercase), a period, and the name of the query in lowercase. For example, the Hello World tutorial
(doc/tutorials/helloworld) contains a single table, HELLOWORLD, with three columns and the partitioning
column, DIALECT, as the primary key. As a result, five default stored procedures are included in addition to any
user-defined procedures declared in the schema. The parameters to the procedures differ based on the procedure.
VoltDB defines a default insert stored procedure when any table is defined:
| HELLOWORLD.insert | The parameters are the table columns, in the same order as defined in the schema. |
VoltDB defines default update, upsert, and delete stored procedures if the table has a primary key:
| HELLOWORLD.update | The parameters are the new column values, in the order defined by the schema, followed by the primary key column values. This means the primary key column values are specified twice: once as their corresponding new column values and once as the primary key value. |
| HELLOWORLD.upsert | The parameters are the table columns, in the same order as defined in the schema. |
| HELLOWORLD.delete | The parameters are the primary key column values, listed in the order they appear in the primary key definition. |
VoltDB defines a default select stored procedure if the table has a primary key and the table is partitioned:
| HELLOWORLD.select | The parameters are the primary key column values, listed in the order they appear in the primary key definition. |
Use the sqlcmd command show procedures to list all the stored procedures available including the
number and type of parameters required. Use @SystemCatalog with the PROCEDURECOLUMNS selector to show
more details about the order and meaning of each procedure's parameters.
The following code example uses the default procedures for the HELLOWORLD table to insert, retrieve (select), update, and delete a new record with the key value "American":
V13.x DocumentationVoltTable[] results;
client.callProcedure("HELLOWORLD.insert",
"American","Howdy","Earth");
results = client.callProcedure("HELLOWORLD.select",
"American").getResults();
client.callProcedure("HELLOWORLD.update",
"American","Yo","Biosphere",
"American");
client.callProcedure("HELLOWORLD.delete",
"American");