VoltDB Home » Documentation » Using VoltDB


sqlcmd — Starts an interactive command prompt for issuing SQL queries to a running VoltDB database


sqlcmd [args...]


The sqlcmd command lets you query a VoltDB database interactively. You can execute SQL statements, invoke stored procedures, or use directives to examine the structure of the database. When sqlcmd starts it provides its own command line prompt until you exit the session. When you start the session, you can optionally specify one or more database servers to access. By default, sqlcmd accesses the database on the local system via localhost.

At the sqlcmd prompt, you have several options:

  • SQL queries — You can enter ad hoc SQL queries that are run against the database and the results displayed. You must terminate the query with a semi-colon and carriage return.

  • Procedure calls — You can have sqlcmd execute a stored procedure. You identify a procedure call with the exec directive, followed by the procedure class name, the procedure parameters, and a closing semi-colon. For example, the following sqlcmd directive executes the @SystemCatalog system procedure requesting information about the stored procedures.

    $ sqlcmd
    1> exec @SystemCatalog procedures;

    Note that string values can be entered as plain text or enclosed in single or double quotation marks. Also, the exec directive must be terminated by a semi-colon.

  • Show and Explain directives — The show and explain directives let you examine the structure of the schema and user-defined stored procedures. Valid directives are:

    • SHOW CLASSES — Lists the user-defined classes in the database. Classes are grouped into procedures classes (those that can be invoked as a stored procedure) and non-procedure classes (shared classes that cannot themselves be called as stored procedures but can be invoked from within stored procedures).

    • SHOW PROCEDURES — Lists the user-defined, default, and system procedures for the current database, including the type and number of arguments for each.

    • SHOW TABLES — Lists the tables in the schema.

    • EXPLAIN {sql-query} — Displays the execution plan for the specified SQL statement.

    • EXPLAINPROC {procedure-name} — Displays the execution plan for the specified stored procedure.

  • Class management directives — The load classes and remove classes directives let you add and remove Java classes from the database:

    • LOAD CLASSES —Loads any classes in the specified JAR file. If a class already exists in the database, it is replaced by the new class definition in the JAR file.

    • REMOVE CLASSES — Removes any classes that match the specified class name string. The class specification can include wildcards.

  • Command recall — You can recall previous commands using the up and down arrow keys. Or you can recall a specific command by line number (the command prompt shows the line number) using the recall command. For example:

    $ sqlcmd
    1> select * from votes;
    2> show procedures;
    3> recall 1
    select * from votes;

    Once recalled, you can edit the command before reissuing it using typical editing keys, such as the left and right arrow keys and backspace and delete.

  • Script files — You can run multiple queries or stored procedures in a single command using the file directive. The file directive takes a text file as an argument and executes all of the SQL queries and exec directives in the file as if they were entered interactively. Any show, explain, recall, or exit directives are ignored. For example, the following command processes all of the SQL queries and procedure invocations in the file myscript.sql:

    $ sqlcmd
    1> file myscript.sql;

    If the file contains only data definition language (DDL) statements, you can also have the entire file processed as a batch by including the -batch argument:

    $ sqlcmd
    1> file -batch myscript.sql;

    If a file or set of statements includes both DDL and DML statements, you can still batch process a group of DDL statements by enclosing the statements in a file -inlinebatch directive and the specified end marker. For example, in the following code the three CREATE PROCEDURE statements are processed as a batch:

    load classes myprocs.jar;
    file -inlinebatch END_OF_BATCH
    CREATE PROCEDURE FROM CLASS procs.PromoteEmployee;

    Batch processing the DDL statements has two effects:

    • Batch processing can significantly improve performance since all of the schema changes are processed and distributed to the cluster nodes at one time, rather than individually for each statement.

    • The batch operates as a transaction, succeeding or failing as a unit. If any statement fails, all of the schema changes are rolled back.

  • Exit — When you are done with your interactive session, enter the exit directive to end the session and return to the shell prompt.

To run a sqlcmd command without starting the interactive prompt, you can pipe the command through standard input to the sqlcmd command. For example:

$ echo "select * from contestants;" | sqlcmd

In general, the sqlcmd commands are not case sensitive and must be terminated by a semi-colon. However, the semi-colon is optional for the exit, file, and recall directives. Also, list and quit are supported as synonyms for the show and exit directives, respectively.



Displays the sqlcmd help text then returns to the shell prompt.


Specifies the network address of one or more nodes in the database cluster. By default, sqlcmd attempts to connect to a database on localhost.


Specifies the port number to use when connecting to the database servers. All servers must be using the same port number. By default, sqlcmd connects to the standard client port (21212).


Specifies the username to use for authenticating to the database. The username is required if the database has security enabled.


Specifies the password to use for authenticating to the database. The password is required if the database has security enabled.

--output-format={csv | fixed | tab}

Specifies the format of the output of query results. Output can be formatted as comma-separated values (csv), fixed monospaced text (fixed), or tab-separated text fields (tab). By default, the output is in fixed monospaced text.


Specifies that the column headings and other metadata associated with query results are not displayed. By default, the output includes such metadata. However, you can use this argument, along with the --output-format argument, to write just the data itself to an output file.


Specifies a time limit for read-only queries. Any read-only queries that exceed the time limit are canceled and control returned to the user. Specify the time out as an integer number of milliseconds. The default timeout is set in the cluster deployment file or is unlimited if not set by the deployment file. Only users with ADMIN privileges can set a sqlcmd timeout longer than the cluster-wide setting.


The following example demonstrates an sqlcmd session, accessing the voter sample database running on node zeus.

$ sqlcmd --servers=zeus
SQL Command :: zeus:21212
1> select * from contestants;
 1 Edwina Burnam   
 2 Tabatha Gehling 
 3 Kelly Clauss    
 4 Jessie Alloway  
 5 Alana Bregman   
 6 Jessie Eichman  

(6 row(s) affected)
2> select sum(num_votes) as total, contestant_number from 
v_votes_by_contestant_number_State group by contestant_number 
order by total desc;
------- ------------------
 757240                  1
 630429                  6
 442962                  5
 390353                  4
 384743                  2
 375260                  3

(6 row(s) affected)
3> exit