This chapter takes a look at two essential additions to the Perl DBI armory: a command-line shell for databases, and the proxying drivers that provide network access to remote database drivers.
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.
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
3. 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 1.
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.
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.[65]
As dbish is a fairly fully featured command-line
shell,[66] 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!
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.
[65] 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.
[66]
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.