Using VoltDB

contents

sqlcmd

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

Synopsis

sqlcmd [args...]

Description

The sqlcmd command starts an interactive session and 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 assumes the database is accessible via localhost.

At the sqlcmd prompt, you have three key 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 command, followed by the procedure class name, the procedure parameters, and a closing semi-colon. For example, the following sqlcmd command executes the @SystemCatalog system procedure requesting information about the stored procedures.

    $ sqlcmd
1> exec @SystemCatalog procedures;

    Note that string values are entered as plain text and are not enclosed in quotation marks. Also, the exec command must be terminated by a semi-colon.

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

For information about additional sqlcmd commands and keyboard control while using the interactive command prompt, see the sqlcmd help text by using the --help argument when invoking sqlcmd. For example:

$ sqlcmd --help

Arguments

--help

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

--servers=server-id[,...]

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

--port=port-num

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).

--user=user-id

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

--password=password-string

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.

--output-skip-metadata

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.

Example

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;
TOTAL   CONTESTANT_NUMBER 
------- ------------------
 757240                  1
 630429                  6
 442962                  5
 390353                  4
 384743                  2
 375260                  3


(6 row(s) affected)
3> exit
$
contents