Archive
Archive
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.
$sqlcmd1>exec @SystemCatalog procedures;
Note that string values can be entered as plain text or enclosed in single quotation marks. Also, the exec directive must be terminated by a semi-colon.
Echo directives — The echo and echoerror directives let you add comments or informational messages to the sqlcmd output. Any text following the directive up to and including the line break or carriage return is repeated verbatim:
ECHO [text] — Writes the specified text, as is, to standard output (stdout).
ECHOERROR [text] — Writes the specified text, as is, to standard error (stderr).
Show, Describe, and Explain directives — The show, describe, 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.
DESCRIBE {table-name} — Lists the columns of a table, stream, or view.
EXPLAIN {sql-query} — Displays the execution plan for the specified SQL statement.
EXPLAINPROC {procedure-name} — Displays the execution plans for the specified stored procedure.
EXPLAINVIEW {view-name} — Displays the execution plans for the components of the specified view.
Query statistics directive — The querystats directive lets you select and format the output of the @Statistics system procedure using SQL-like syntax. In the directive you specify a SELECT statement identifying the columns you want returned, using FROM STATISTICS(selector, delta-flag) in place of the table name. You can also use the WHERE, ORDER BY, and GROUP BY clauses to filter the results as desired. For example, the following directive returns the total number of rows in each table:
$ sqlcmd 1> querystats select table_name, sum(tuple_count) from statistics(table,0) group by table_name;
Column aliases are not supported.
Query must be on a single line.
Errors are reported on the console but not returned to the user.
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 or resource files in the specified JAR file. If a class or resource already exists in the database, it is replaced by the new definition from 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 one or more text
files as an argument and executes all of the SQL queries and exec directives in the file(s) as if
they were entered interactively. (Do not use control directives such as recall and
exit in script files.) Separate multiple script files with spaces. Enclose file names that contain
spaces with single quotation marks. For example, the first command in the following example processes all of the SQL
queries and procedure invocations in the file myscript.sql. The second command processes the SQL
queries from two files:
$ sqlcmd 1> file myscript.sql; 2> file yourscript.sql 'their script.sql';
If the file(s) contain only data definition language (DDL) statements, you can also have the files processed as a
single batch by including the -batch argument:
V13.x Documentation$ 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.AddEmployee; CREATE PROCEDURE FROM CLASS procs.ChangeDept; CREATE PROCEDURE FROM CLASS procs.PromoteEmployee; END_OF_BATCH
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.
All text data in Volt is processed and stored as UTF-8 encoded strings. However, it is possible to enter and display data in other character sets if desired.
For interactive sessions, the sqlcmd utility automatically translates between the localized character set for the user's interactive session and UTF-8 used by Volt internally. For example, when executing an INSERT statement into a VARCHAR column, the text being inserted is converted from the localized character set into UTF-8 before being inserted. Similarly, when displaying results from a SELECT statement, text fields are converted from UTF-8 into the user's session character set before being sent to the terminal. The web-based Volt Management Console (VMC) provides a similar service automatically converting to and from the user's session character set to UTF-8 on input and output.
When processing data from a file, sqlcmd provides the --charset qualifier that
lets you specify the character set used both for processing files for input (either with the --file
qualifier or the FILE directive) and writing files for output (with the --output
qualifier). You can also use the --charset qualifier with the csvloader utility to
specify the character set of the CSV input file. Finally, the file export connector supports a charset
property for specifying what character set to use when exporting data to a file. (See Table 15.1, “File Export Properties”
for more information on the file export connector properties.)
Which character sets are supported depends on which Java virtual machine (JVM) release you are using on your servers (for export) or client machines (for sqlcmd and csvloader). For established character sets, such as Shift_JIS or ISO-8859-1, all supported JVM releases provide support. For newer character sets, you may need a more recent release of the JVM. For example, the recent Simplified Chinese character set GB18030-2022 requires a JVM released in 2023 or later. For OpenJDK this includes the following releases:
Java 8 — release 8u32-b05
Java 11 — release 11.0.20+8
Java 17 — release 17.0.8+7
When used with the --file qualifier, processes the data definition language (DDL) statements
in the file as a single batch (rather than one at a time) and returns to the shell prompt. Batch processing of DDL
statements is orders of magnitude faster than processing each statement separately. However, the file must contain DDL
statements only, no data manipulation language (DML) queries such as SELECT or INSERT. If the file contains both DDL
and DML, use the file --inlinebatch directive in the file to delineate batches of DDL statements
then process the file with the --file qualifier only.
Specifies the character encoding to use when interpreting file input (using the --file
qualifier or FILE directive) and when writing file output (using the --output-file qualifier). If
no character set is specified, sqlcmd uses UTF-8 for file input and output. Note that the
--charset qualifier does not affect text entered at the sqlcmd prompt, which is
always interpreted using the session's default character set, as is also true of output displayed on the
terminal.
Specifies a file that lists the username and password of the account to use when connecting to a database with
security enabled. This is useful when writing shell scripts because it avoids having to hardcode the password as plain
text in the script. The credentials file is interpreted as a Java properties file defining the properties
username and password. For example:
username: johndoe password: 4tUn8
Because it is a Java properties file, you must escape certain special characters in the username or password, including the colon or equals sign.
Executes the SQL statements in the specified file and then returns to the shell prompt.
Displays the sqlcmd help text then returns to the shell prompt.
Specifies the use of kerberos authentication when connecting to the database server(s). The service name identifies the Kerberos client service module, as defined by the JAAS login configuration file.
Write the output from sqlcmd commands and statements to the specified file. Note that only the out of the commands are written to the file, errors and informational messages (such as how many rows were affected) are still written to the terminal. By default all output is written to the terminal.
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 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).
Submits the specified text as an SQL query to the database, displays the results and returns to the shell prompt.
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 configuration (or set to 10 seconds by default). Only users with ADMIN privileges can set a sqlcmd timeout longer than the cluster-wide setting.
Specifies the network address of one or more nodes in the database cluster. When specifying an IPv6 address, enclose the address in square brackets. By default, sqlcmd attempts to connect to a database on localhost.
Specifies the use of TLS encryption when communicating with the server. Only necessary if the cluster is configured to use TLS encryption for the external ports. See the section called “Using CLI Commands with TLS/SSL” for more information.
When processing file input (with the --file qualifier or the file
directive) stops any further execution when an error occurs, if set to true. If false, the statement causing the error
is ignored and processing continues to the next statement in the file. The default is true.
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 when connecting to the database. You must specify a username and password if security is enabled for the database. If you specify a username with the --user argument but not the --password argument, VoltDB prompts for the password.
The following example demonstrates an sqlcmd session, accessing the voter sample database running on node zeus.
$sqlcmd --servers=zeusSQL 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$