The dbsh manual

1. Introduction

dbsh is a command-line ODBC client. It aims to provide a unified interface to any (ODBC-supported) RDBMS, combining all the best features of clients like mysql, psql, and sqsh. The target audience is programmers and DBAs who need to work with a variety of DBMSs and want a consistent and powerful interface with which to do so.

dbsh is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.

This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.

You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.

2. Installation

Prerequisites

You’ll need an ODBC 3+ library - dbsh has been tested against unixODBC and iODBC.

A readline-type library is optional but highly recommended. dbsh should work with GNU readline, NetBSD editline or Rich Salz’s libeditline.

Building

dbsh uses GNU autotools, so building and installing it should be a simple matter of:

./configure
make
su -c "make install"

If you have DejaGnu and the SQLite ODBC driver installed you can also run the test suite:

make check

3. Invoking

3.1 Drivers and DSNs

The ‘-l’ switch lists the available ODBC drivers and DSNs (Data Source Names) configured on your system.

ben@arctor:~$ dbsh -l
+---------+
| Driver  |
+---------+
| SQLite  |
| SQLite3 |
| MySQL   |
+---------+
3 rows in set

+------------+------+-------------+
| DSN        | Type | Description |
+------------+------+-------------+
| mysql-test | user | mysql-test  |
+------------+------+-------------+
1 row in set

Consult the documentation of your ODBC implementation for details on configuring drivers and DSNs. One of the following documents may be suitable:

• unixODBC (GUI)
• unixODBC (CLI)
• iODBC (GUI)
• iODBC (CLI)

3.2 Connecting to a DSN

Connect to a DSN as follows:

dbsh dsn [username] [password]

Where dsn is the DSN to connect to, and the optional arguments username and password specify the login credentials to use.

3.3 Using a connection string

You can connect to databases for which DSNs have not been created by using a connection string:

dbsh connection-string

A connection string is a series of ‘NAME=VALUE’ pairs separated by semicolons. The first of these parameters, ‘DRIVER’, should be the ODBC driver to use, while the remainder are driver-specific. For example:

dbsh DRIVER=SQLite;DATABASE=db.sqlite

will connect to the SQLite database in the file db.sqlite.

dbsh assumes that you are using a connection string if you only pass it one argument and that argument contains an ’=’ character.

Internal note: when using a connection string, dbsh connects using SQLDriverConnect rather than SQLConnect.

4. Basics

When you run dbsh you are presented with a prompt something like this:

Connected to foo
foo 1>

The default prompt consists of the DSN you are connected to, and a line number (see prompt to change it).

Text entered at the dbsh prompt is stored in the main SQL buffer. You can enter SQL statements spanning multiple lines into this buffer, and the prompt will update to show the current line number. If your system supports it, you get readline capabilities.

dbsh stops reading input and starts doing stuff with it when it sees an action character. This is ‘\’ or ‘;’ by default (see action_chars). If you need to enter a literal action character, just escape it with a second character (eg ‘\\’).

foo 1> SELECT * FROM bar;
+----+--------------------+
| id | desc               |
+----+--------------------+
| 1  | This is some text. |
| 2  | *NULL*             |
+----+--------------------+
2 rows in set

(0.000556s)

You can follow your query with a pipe (‘|’) or file redirect (‘>’). These behave rather like you’d expect them to in the bourne shell (in fact, they just open a pipe to /bin/sh and let it handle everything from there on).

foo 1> SELECT * FROM bar; | less
+----+--------------------+
| id | desc               |
+----+--------------------+
| 1  | This is some text. |
| 2  | *NULL*             |
+----+--------------------+
2 rows in set

(0.000556s)
(END)

foo 1> SELECT * FROM bar; > output.txt

You can specify a default pager to use when no explicit redirect is specified (see pager).

To exit dbsh, type ‘\q’.

5. Actions

An action is a dbsh operation that (usually) acts on the contents of the main SQL buffer.

The action is specified as a one-character suffix to the action character. For example, to run the ‘G’ action:

foo 1> SELECT * FROM test\G

If no suffix is given, the default action is used (see default_action):

foo 1> SELECT * FROM test;

Some actions take parameters - these follow the action character and suffix and are separated by spaces:

foo 1> SELECT * FROM test WHERE id = ?\C 3

If the SQL buffer is empty (the first thing you type is an action character), the action will instead operate on the previous contents of the SQL buffer. This allows you to quickly perform a new action on the previous query (eg to try a different output format, or to bind different parameters).

foo 1> \G 1

This section describes the available actions and their uses.

5.1 Actions which run SQL

These actions all send the contents of the SQL buffer to the ODBC driver, and fetch the results. They differ only in how they output the results. With the exception of ‘g’, actions which run SQL all use upper case letters.

These actions can take parameters, which are bound to parameter markers in the SQL. For example:

foo 1> INSERT INTO test (id) VALUES (?); 4
foo 1> ; 5
foo 1> ; 6

g - Horizontal output

foo 1> SELECT * FROM test\g
+----+--------------------+
| id | desc               |
+----+--------------------+
| 1  | This is some text. |
| 2  | *NULL*             |
| 3  | Some more text.    |
+----+--------------------+
3 rows in set

This is the default value of the default_action setting.

G - Vertical output

Useful when the rows in the result set are too wide to fit onto one line.

foo 1> SELECT * FROM test\G
+------+--------------------+
|   id | 1                  |
| desc | This is some text. |
+------+--------------------+
|   id | 2                  |
| desc | *NULL*             |
+------+--------------------+
|   id | 3                  |
| desc | Some more text.    |
+------+--------------------+
3 rows in set

C - CSV output

Comma-separated values format. Useful for redirecting to files.

foo 1> SELECT * FROM test\C
"id","desc"
"1","This is some text."
"2",""
"3","Some more text."

T - TSV output

Tab-separated values. Useful for redirecting to files.

foo 1> SELECT * FROM test\T
id	desc
1	This is some text.
2	
3	Some more text.

F - Flat output

Each data value on its own line. Useful for copying and pasting large chunks of text.

foo 1> SELECT * FROM test\F
id
1

desc
This is some text.

id
2

id
3

desc
Some more text.

L - List output

Displays each column from the result set as a comma-separated list. Useful for pasting into IN() clauses.

foo 1> SELECT * FROM test\L
id: 1,2,3
desc: This is some text.,Some more text.

5.2 Actions which manipulate the SQL buffer

c - Clear

Clears the contents of the SQL buffer. Useful if you’ve made a mistake.

e - Edit

This opens an editor (using the EDITOR or VISUAL environment variables if set) to edit the contents of the main SQL buffer. Upon closing the editor, the text is stored in the ’previous’ SQL buffer, so that other actions can be used on it immediately simply by entering an action character.

p - Print

Outputs the current contents of the SQL buffer. Potentially useful for verifying it after some complex query editing.

5.3 Other actions

These actions don’t really fit into the dbsh concept of actions being things which act on the SQL buffer, but they are implemented as actions rather than commands for familiarity with other database clients.

r - Reconnect

Reconnect to the current data source.

q - Quit

Exit dbsh.

6. Commands

If an SQL buffer begins with a command character (‘/’ by default, see command_chars), then rather than being sent to the driver as an SQL statement it is interpreted as a command by dbsh. Commands can be seen as pseudo-statements in that they return result sets - they are thus amenable to all the usual manipulation with actions and redirects. However, for convenience commands are run as soon as return is pressed, without checking for an action character.

The following commands are available:

6.1 Help commands

Command: help

Displays a list of available commands.

Command: copying

Displays the license.

Command: warranty

Displays the (lack of a) warranty.

6.2 Schema commands

These commands fetch information about the database schema using the ODBC SQLGetTables and SQLGetColumns calls. Support for these calls varies between drivers, and therefore the utility of the commands does likewise.

Command: catalogs

Lists the catalogs in the data source.

Command: schemas

Lists the schemas in the data source.

Command: tables [catalog]

Lists tables. The optional argument is a catalog or schema (depending on driver) to limit the results to.

Command: columns table

Lists the columns in a table.

6.3 Transaction commands

These commands are of course only useful for databases that support transactions.

Command: autocommit [status]

Displays the current autocommit status, or with a parameter of ‘on’ or ‘off’, sets it.

Command: commit

Commits the current transaction.

Command: rollback

Rolls back the current transaction

6.4 Other commands

Command: set [name [value]]

Used to manipulate dbsh’s configuration at runtime. With no arguments, outputs all current configuration settings. With one argument, outputs a single setting. With two arguments, changes a setting.

Command: unset name

Unsets a configuration value.

Command: info

Fetches some information about the current data source from ODBC.

7. Configuration

dbsh has a number of options and three distinct means of settings them:

• Per-user config file located in ~/.dbsh/dbshrc. Each line in the file is treated as a ‘name=value’ pair, with the option names in lower case. This is the normal way to configure your favourite settings.
• Environment variables. Any dbsh option can also be set as an environment variable named ‘DBSH_’‘OPTION’, where ‘OPTION’ is the name of the option in upper case. This is useful for scripts or when you want to use a different option for a single session.
• Options may be altered at runtime using the set and unset commands.

The following options are available:

User Option: action_chars

One or more characters used to terminate SQL statements. Default ‘\;’.

User Option: command_chars

One or more characters used to indicate that the contents of the SQL buffer should be interpreted as a dbsh command. Default ‘/’.

User Option: default_action

The action to use when none is specified. Default ‘g’.

User Option: pager

The default pager to invoke when no redirect is specified after a query. No default.

User Option: prompt

The dbsh prompt. Default ‘d l> ’.

Table of Contents

About This Document

This document was generated by Ben Spencer on October 2, 2010 using texi2html 1.82.

The buttons in the navigation panels have the following meaning:

where the Example assumes that the current position is at Subsubsection One-Two-Three of a document of the following structure:

• 1. Section One
  • 1.1 Subsection One-One
    • ...
  • 1.2 Subsection One-Two
    • 1.2.1 Subsubsection One-Two-One
    • 1.2.2 Subsubsection One-Two-Two
    • 1.2.3 Subsubsection One-Two-Three     <== Current Position
    • 1.2.4 Subsubsection One-Two-Four
  • 1.3 Subsection One-Three
    • ...
  • 1.4 Subsection One-Four

This document was generated by Ben Spencer on October 2, 2010 using texi2html 1.82.