Options

-h, --help	Show a usage message and exit.
-c config, --config=config
	Specifies the configuration file to use. Defaults to $HOME/.sqlcmd/config. Ignored if -d is specified. See Configuration File, below, for more information on the format of this file.
-d database, --db=database
	Database to use. Format: dbname,dbtype,host[:port],user,password Overrides any specified alias. See Specifying a Database, below, for a complete explanation of this parameter.
-l level, --loglevel=level
	Enable log messages as level n, where n is one of: debug, info, warning, critical, error.
-L logfile, --logfile=logfile
	Dump log messages to logfile, instead of standard output

Parameters

• The alias parameter identifies an alias for the database in the configuration file. It's ignored if the -d option is specified.
• The @file parameter specifies a file of SQL (and sqlcmd) commands to be run once sqlcmd has connected to the database. If this parameter is omitted, sqlcmd will enter command line mode, prompting on standard input for each command.

Specifying a Database

The --db (or -d) parameter is somewhat complicated. It takes five comma-separated parameters, in order:

dbname:

The name of the database. (For SQLite and Gadfly, this is the path to the file.)

dbtype:

The database type, as defined by the Grizzled Utility Library's db package, oracle,``sqlserver``, mysql, postgresql and sqlite. Additional database types can be added, however; see below.

host:port:

The host name and port number on which the database server is listening for connections. This field is ignored, and may be empty, for SQLite and Gadfly. The port number may be omitted (i.e., with only the host name specified), and the database driver will use the default port for the database type.

user:

The user to use when authenticating to the database. Ignored for SQLite and Gadfly.

password:

The password to use when authenticating to the database. Ignored for SQLite and Gadfly.

sqlcmd -d /tmp/test.db,sqlite,,,

sqlcmd -d customers,oracle,db.example.com,scott,tiger

sqlcmd -d mydb,postgresql,localhost,psql,foo.bar

The settings Section

This optional section can contain initial values for any of the settings that are understood by the .set command. See .set for a full explanation of each setting.

The db. Sections

A db. section contains the connection definition for a particular database. The db. prefix must be followed by the primary name of the database. Multiple db. sections can exist in the configuration file; each section supports the following parameters.

Parameter Name	Description	Required/Optional
database	The name of the database, as known by the RDBMS engine.	required
type	The type of the database. This value must be recognized by the Grizzled API's db module. That means it must identify a database driver that is part of the grizzled.db package, or it must be a driver you specify yourself, in a driver. section. (See below.)	required
host	The host on which the database resides. The RDBMS server on that host must be configured to accept incoming database client connections. This parameter is ignored for SQLite databases.	required (but ignored for SQLite and Gadfly)
port	The port on which the database server is listening. If not specified, sqlcmd uses the default port for the RDBMS server (e.g, 1521 for Oracle, 1433 for SQL Server, 3306 for MYSQL, 5432 for PostgreSQL, etc.). This parameter is ignored for SQLite databases.	optional (but ignored for SQLite and Gadfly)
user	The user to use when authenticating to the database. This parameter is ignored for SQLite databases.	required (but ignored for SQLite and Gadfly)
password	The password to use when authenticating to the database. This parameter is ignored for SQLite databases.	required (but ignored for SQLite and Gadfly)
aliases	A comma-separated list of alias names for the database. This list allows you to refer to the database by multiple names	optional
onconnect	Path to a script of commands to run just after connecting to database. The file can contain any valid sqlcmd command (including, obviously, SQL). Any leading "~" in the path is expanding to the home directory of the user running sqlcmd. Relative paths are assumed to be relative to the directory containing the configuration file. Hint: Specify the path using Unix-style forward slashes, even on Windows, to avoid problems with backslashes.	optional

[db.testdb]
names=sqlite, test
database=/tmp/test.db
type=sqlite

[db.customers]
names=oracle, custdb
database=cust001
type=oracle
host=db.example.com
user=scott
password=tiger

The driver. Sections

The driver. section allows you to install additional enhanced database drivers, beyond those that are built into the Grizzled API's db package.

An enhanced driver must extend the grizzled.db.DBDriver class and provide the appropriate methods. See the appropriate Grizzled documentation for details. If you want to write your own driver, the Grizzled source code is invaluable.

The driver. section supports the following options:

Parameter Name	Description	Required/Optional
class	The fully-qualified name of the driver class, including any package and/or module name.	required
name	The logical name to use for the driver.	required

For example, suppose you wrote a driver to connect to the Apache Derby database (perhaps using JPype). Let's further suppose that the driver is implemented by a Python class called DerbyDriver (which extends the Grizzled DBDriver class) and resides in module mycode.db. You could use the following sqlcmd configuration section to make sqlcmd aware of that driver:

[driver.derby]
class=mycode.db.DerbyDriver
name=derby

With that section in the configuration file, you can now use the value derby for the type parameter in any db. section.

Obviously, the appropriate supporting Python (and other) code must be available to sqlcmd, by setting PYTHONPATH, LD_LIBRARY_PATH, and/or PATH, as appropriate for your operating system.

Other Sections

sqlcmd quietly ignores any other sections in the configuration file. One possible use for other sections is as holders for common variable definitions that are substituted in other places in the file. For instance, suppose all your database engine happen to be on the same host and happen to use the same user name and password. To share that common configuration information, you might do something like the following:

[defs]
# Shared definitions
dbhost=db.example.com
admin_user=admin
admin_password=foo.bar

[db.testdb]
names=sqlite, test
database=/tmp/test.db
type=sqlite

[db.customers]
names=oracle
database=customers
type=oracle
host=${dbhost}
user=${admin_user}
password=${admin_password}

[db.mydb]
names=postgres
database=mydb
type=postgresql
host=${dbhost}
user=${admin_user}
password=${admin_password}

Timings

By default, sqlcmd times how long it takes to execute a SQL statement and prints the resulting times on the screen. To suppress this behavior, set the timings setting to false:

.set timings false

SQL Echo

By default, sqlcmd does not echo commands to the screen. That's a reasonable behavior when you're using sqlcmd interactively. However, when you're loading a file full of sqlcmd statements, you might want each statement to be echoed before it is run. To enable command echo, set the echo setting to true:

.set echo true

Comments

sqlcmd honors (and ignores) SQL comments, as long as each comment is on a line by itself. A SQL comment begins with "--".

Example of support syntax:

-- This is a SQL comment.
-- And so is this.

Example of unsupported syntax:

INSERT INTO foo VALUES (1); -- initialize foo

.about

Displays information about sqlcmd.

begin

Start a new transaction. This command is not permitted unless autocommit is true. (See .set) begin is essentially a no-op: It's ignored in autocommit mode, and irrelevant when autocommit mode is off. It's there primarily for SQL scripts.

Example of use:

begin
update foo set bar = 1;
commit

For compatibility with SQL scripts, this command does not begin with a ".".

See also:

• .set
• commit
• rollback

commit

Commit the current transaction. Ignored if autocommit is enabled. For compatibility with SQL scripts, this command does not begin with a ".".

See also:

• .set
• begin
• rollback

.connect

The .connect command closes the current database connection and opens a new one to a (possibly) different database. The general form of the command is:

.connect dbname

dbname is a database name from the configuration file. When it first starts running, sqlcmd issues an implicit .connect to the database specified on the command line.

.describe

The .describe command, which can be abbreviated .desc, is used to describe a table. The general form of the command is:

.describe tablename [full]

If "full" is not specified, then sqlcmd prints a simple description of the table and its columns. For instance:

? .desc users
-----------
Table users
-----------
id             bigint NOT NULL
companyid      bigint NOT NULL
lastname       character varying(254) NOT NULL
firstname      character varying(254) NOT NULL
middleinitial  character(1) NULL
username       character varying(30) NOT NULL
password       character varying(64) NOT NULL
email          character varying(254) NOT NULL
telephone      character varying(30) NULL
department     character varying(254) NULL
isenabled      character(1) NOT NULL

If "full" is specified, sqlcmd also gathers and displays information about the table's indexes. For example:

? .desc users
-----------
Table users
-----------
id             bigint NOT NULL
companyid      bigint NOT NULL
lastname       character varying(254) NOT NULL
firstname      character varying(254) NOT NULL
middleinitial  character(1) NULL
username       character varying(30) NOT NULL
password       character varying(64) NOT NULL
email          character varying(254) NOT NULL
telephone      character varying(30) NULL
department     character varying(254) NULL
isenabled      character(1) NOT NULL

--------
Indexes:
--------

userpk1 Columns:     (id)
        Description: (PRIMARY) Unique, non-clustered btree index
----------------------------------------------------------------------------
userak1 Columns:     (companyid, username)
        Description: Unique, non-clustered btree index

.echo

Echoes all remaining arguments to standard output. This command is useful primarily in scripts.

Example:

.echo Don't look now, but I'm about to run SELECT

.exit

Exit sqlcmd. .exit is equivalent to typing the key sequence corresponding to an end-of-file condition (Ctrl-D on Unix systems, Ctrl-Z on Windows).

.history

.history displays the command history. See Command History for a complete explanation of sqlcmd's command history capabilities.

r or redo

Re-issue a command from the history. General usage:

r [num|str]
redo [num|str]

If num is present, it is the number of the command to re-run. If str is specified, the most recent command that str (using a substring match) is re-run.

For example, consider this history:

? .history
   1: .show tables;
   2: select * from foo;
   3: .desc foo;
   4: .desc foobar;

Here are various redo invocations:

? r 1  <--- re-runs command 1, ".show tables"
? r s  <--- re-runs the most recent command that starts with "s", which is "select * from foo"
? r    <--- re-runs the last command, ".desc foobar"

rollback

Roll the current transaction back. Ignored if autocommit is enabled. For compatibility with SQL scripts, this command does not begin with a ".".

See also:

• .set
• begin
• commit

.run

Loads an external file of commands (typically SQL) and runs those commands in the current session without exiting. After the commands are run, sqlcmd returns to its interactive prompt. .run can also be invoked as .load.

.run path
.load path

Both commands do exactly the same thing.

.set

The .set command displays or alters internal sqlcmd settings. Without any parameters, .set displays all internal settings and their values:

? .set
autocommit = true
binarymax  = 20
colspacing = 1
echo       = true
showbinary = false
stacktrace = false
timings    = true

The supported settings are:

Setting	Meaning	Default
autocommit	Whether or not each SQL statement automatically commits to the database. If true, then each SQL statement is automatically committed to the database. If false, then a new set of SQL statements starts a transaction, which must be explicitly committed via the commit command. Also, if autocommit is false, the rollback command is enabled.	true
binarymax	How many bytes to display from binary (BLOB and CLOB) columns. Ignored unless showbinary is true.	20
colspacing	Number of spaces between each column of result set (i.e., SELECT) output.	1
echo	Whether or not commands are echoed before they are executed.	false
showbinary	Whether or not to show data from binary (BLOB and CLOB) columns. If true, the value of binarymax dictates how many bytes to display.	false
stacktrace	Whether to display a Python stack trace on normal (i.e., expected) errors, like SQL syntax errors.	false
timings	Whether to display execution times for SQL statements.	true

.show

The .show command displays certain information about the currently connected database. Currently, it supports two sub-commands:

? .show tables
all_users
foo
fool
tb_bar
tb_foo
userlocation
? .show tables ^tb
tb_bar
tb_foo
? .show tables tb
tb_bar
tb_foo
? .show tables ^.*foo
foo
fool
tb_foo
? .show tables .*foo
foo
fool
tb_foo
? .show tables .*foo$
foo
tb_foo
? .show tables foo$
foo

.var

Set a shell-style variable that can be interpolated in subsequent input lines. For example:

? .var table=mytable
? select * from $mytable

or:

? table=mytable
? select * from $mytable

See Unix shell-style variables for more information.

.vars

Show all variables current set by .var.

Restrictions

• Some extended commands don't work well through sqlcmd. Your mileage may vary.
• Since these extended commands are database-specific, they do not show up in command completion output, and they do not support command completion themselves.

Some Differences from Unix Shells

There are some differences from bash, however.

? table=mytable
? table = mytable
? table=          mytable
?   table   =  mytable

? fred=This variable has blanks
? fred="This variable has blanks"

? foo='bar'
? .echo $foo
bar
? foo="baz"
? .echo $foo
baz

? var_with_quotes="'<-- That's a quote."
? var_with_quotes='\'<-- That\'s a quote.'

? -a=foo
? .echo $-a
foo
? lispish-var=some value
? .echo ${lispish-var}
some value

? x=foobar
? .echo $x
foobar
? x=
? .echo $x
?