DBI Shell and Database Proxying

dbish -- The DBI Shell

The DBI Shell, or dbish , is a command-line tool that allows you to run arbitrary SQL statements and diagnostics against databases without needing to write a complete Perl program.

For example, let's say we wanted to get a quick list of all the megaliths in Wiltshire. We could write a complete Perl program that connects to the database, prepares and executes the appropriate SQL statement, fetches the data back, formats it, and disconnects from the database.

With the DBI, this process is easy, but it's a bit tedious if you just want some quick information.

This is where the dbish comes into play. dbish allows you to connect to a data source and type an SQL statement straight into it. dbish handles all the underlying connecting, preparing, and executing, and also gives you the results right away.

Starting Up dbish

dbish is an executable program bundled with the DBI. You should be able to start it up by typing:

dbish

which will return a prompt in the following manner:

DBI::Shell 10.5 using DBI 1.14 WARNING: The DBI::Shell interface and functionality are ======= very likely to change in subsequent versions! Available DBI drivers: 1: dbi:ADO 2: dbi:ExampleP 3: dbi:Oracle 4: dbi:Proxy Enter driver name or number, or full 'dbi:...:...' DSN:

Some drivers require real username and password authentication to connect to databases. To support this requirement, you can supply additional arguments to dbish in the form of:

dbish <data_source> [username] [password]

For example:

dbish '' stones stones

or:

dbish dbi: stones stones

In this case, we haven't specified a driver, and so we'll choose one interactively through the menus. We can also bypass the menus by putting in the data source name for the desired database:

dbish dbi:Oracle:archaeo stones stones

If you don't specify a driver on the command line, the displayed menus allow you to select a type of database by listing the various drivers available. For example, if an Oracle database contained the megalithic database, you would select the dbi:Oracle data source by typing . This will result in that specific database driver being queried for available data sources. For example:

Enter data source to connect to: 1: dbi:Oracle:archaeo 2: dbi:Oracle:sales Enter data source or number, or full 'dbi:...:...' DSN:

This example shows that the underlying Oracle database driver is aware of two locally configured Oracle databases. Our megalithic database is stored in the archaeo database, so type .

At this stage, dbish will attempt to connect to the database. Once you have connected successfully to a data source, you will see a prompt such as:

stones@dbi:Oracle:archaeo>

telling you that you are connected to the data source dbi:Oracle:archaeo as the user stones, and that dbish is ready for you to issue commands to it.

You can make a connection to another database from within dbish by using the /connect command. For example:

stones@dbi:Oracle:archaeo> /connect dbi:Oracle:sales dbusername Disconnecting from dbi:Oracle:archaeo. Connecting to 'dbi:Oracle:sales' as 'dbusername'... Password for 'dbusername' (not echoed to screen): ...... stones@dbi:Oracle:sales>

Unfortunately, connecting to multiple databases simultaneously is not yet supported by dbish.

Handling Statements

In general, the most common reason for using dbish is to issue ad-hoc SQL statements to a database, either to check that the statement works before including it in a Perl program, or just to get some quick answers. This task is exactly what dbish was designed for.

dbish commands are entered as a forward slash (/) followed by a command name and optionally some extra arguments. For example:

/help

Anything entered that doesn't start with a forward slash is considered to be part of an SQL statement and is appended to a ``statement buffer.'' Once the SQL statement is complete, you can execute it, and the results, if any, will be returned to your screen.

For example, to query the names of all sites in the megalithic database, type:

stones@dbi:Oracle:archaeo> SELECT name FROM megaliths Current statement buffer (enter '/' to execute or '/help' for help): SELECT name FROM megaliths stones@dbi:Oracle:archaeo> / 'Avebury' 'Stonehenge' 'Lundin Links' ... [132 rows of 1 fields returned] stones@dbi:Oracle:archaeo>

Note that a forward slash by itself can be used to execute statements. After executing a statement, the statement buffer is cleared. But suppose we start typing in a new query and then change our minds about what we want to return:

stones@dbi:Oracle:archaeo> SELECT name FROM megaliths Current statement buffer (enter '/' to execute or '/help' for help): SELECT name FROM megaliths stones@dbi:Oracle:archaeo> SELECT name, mapref FROM megaliths Current statement buffer (enter '/' to execute or '/help' for help): SELECT name FROM megaliths SELECT name, mapref FROM megaliths stones@dbi:Oracle:archaeo>

This is totally wrong! Fortunately, you can clear the statement buffer of old statements and start new ones afresh with the /clear command. Statements that have been executed are automatically cleared from the statement buffer, but can be recalled with the /history command. You can even use the /edit command to start up an external editor for editing your SQL.

The way in which results of SELECT statements are displayed is also configurable using the /format command. The two options currently available are /format neat and /format box. The default option is neat, which uses the DBI::neat_list() function to format the data. For example, the statement:

stones@dbi:Oracle:archaeo> SELECT name, mapref FROM megaliths /

has the following output:

'Avebury', 'SU 102 699' 'Stonehenge' 'SU 123 422', 'Lundin Links', 'NO 404 027' ... [132 rows of 1 fields returned]

The box option is prettier:

+--------------+------------+ | name | mapref | +--------------+------------+ | Avebury | SU 102 699 | +--------------+------------+ | Stonehenge | SU 123 422 | +--------------+------------+ | Lundin Links | NO 404 027 | +--------------+------------+

It's also possible to issue non-SELECT statements from dbish with the / command. Want to delete all the rows from a table? Simply type:

stones@dbi:Oracle:archaeo> delete from megaliths / [132 rows affected] stones@dbi:Oracle:archaeo>

Quick, easy, and very deadly! Any non-SELECT statement can be issued in this way, including CREATE TABLE statements or even stored procedure calls, if your database supports them.[67]

[67]There's a /do command that forces the do() method to be used instead of a prepare() followed by an execute(). In practice, it's rarely needed.

Some Miscellaneous dbish Commands

As dbish is a fairly fully featured command-line shell,[68] it has some convenient commands defined within it that allow you to commit and roll back database changes, recall statements and commands that you'd executed in the past, and even execute arbitrary Perl statements!

[68]dbish's powerful command-line editing functionality comes courtesy of the Term::Readline and Term::Readline::Gnu modules. You don't need to install them to use dbish, but it helps.

One of the most useful of the miscellaneous statements is /table_info , which lists the tables in the database that you are currently connected to. This statement is indispensable when you're trying to remember exactly what that pesky table name is!

A full list of these commands can be seen by typing the all-important /help command.

dbish is currently a handy tool for performing quick tasks on a database. It should continue to evolve over time into an indispensable part of the database administrator's and database developer's armory, much like proprietary tools such as Oracle's SQL*Plus utility.

DBI Shell and Database Proxying

Contents:

dbish -- The DBI Shell

Starting Up dbish

Handling Statements

Some Miscellaneous dbish Commands