DBI::Shell(3)

💖 NAME 💖

Okay bestie, welcome to the DBI::Shell(3) page from User Contributed Perl Documentation ✨ The module is DBI::Shell — Interactive command shell for the DBI. That's the whole vibe: a command line interface for Perl's DBI. 💅

✨ SYNOPSIS ✨

You can launch it either way, no cap:

perl -MDBI::Shell -e shell [<DBI data source> [<user> [<password>]]]

or

dbish [<DBI data source> [<user> [<password>]]]
dbish --debug [<DBI data source> [<user> [<password>]]]
dbish --batch [<DBI data source> [<user> [<password>]]] < batch file

🦋 DESCRIPTION 🦋

The DBI::Shell module — and the dbish command, if it's installed — gives you a simple but effective command line interface for the Perl DBI module. Lowkey iconic. ✨

🎀 TO DO 🎀

So the docs are still a work in progress: proper docs, but not yet, because too much is changing. 🙈

• A "source file" command to read a command file.
• Allow nesting via a stack of command file handles.
• Add a command log facility to create batch files.
• Use Data::ShowTable if available.
• Define DBI::Shell plug-in semantics.
• Implement import/export as a plug-in module.
• Clarify meaning of batch mode.
• Completion hooks.
• Set/Get DBI handle attributes.
• Portability.
• Emulate popular command shell modes (Oracle, Ingres etc)?

🌸 ARGUMENTS 🌸

• debug — dbish --debug enables debug messages.
• batch — dbish --batch < batch_file.

💅 COMMANDS 💅

Many commands — few documented, yet! Here's the whole lineup, bestie:

• help — prints help:

help

• chistory — shows the history of all commands entered. chistory | YourPager pages that history:

chistory
chistory | YourPager

• clear — clears the current command buffer:

clear

• commit — commits changes to the database:

commit

• connect — picks from available drivers and sources. connect dbi:Oracle picks a source based on that driver, and connect dbi:YourDriver:YourSource is like dbi:Oracle:mysid:

connect
connect dbi:Oracle
connect dbi:YourDriver:YourSource

Use this to change userid or password.

• count — runs a select count(*) from table on each table you list:

count table1 [...]

• current — displays the current statement in the buffer:

current

• do — executes the current (non-select) statement:

do

Examples:

dbish> create table foo ( mykey integer )
dbish> /do

dbish> truncate table OldTable /do (Oracle truncate)

• drivers — displays available DBI drivers:

drivers

• edit — edits the current statement in an external editor:

edit

The editor is defined using the environment variable $VISUAL or $EDITOR, or the default is vi. Use option editor=new editor to change it in the current session. To read a file from the operating system, invoke the editor (edit) and read the file into the editor buffer.

• exit — exits the shell:

exit

• get — retrieves a previous command into the current buffer:

get
get 1
get -1

get retrieves a previous command. get 1 retrieves the 1 command executed into the current buffer. get -1 retrieves the second to last command executed into the current buffer.

• go — executes the current statement:

go

It runs (executes) the statement in the current buffer. This is the default action if the statement ends with /.

Examples:

dbish> select * from user_views/

dbish> select table_name from user_tables
dbish> where table_name like 'DSP%'
dbish> /

dbish> select table_name from all_tables/ | more

• history — displays combined command and result history. history | more pages it:

history
history | more

• load — load file name reads the contents of a file into the current buffer. The contents of the file are loaded as the current buffer:

load file name

• option — shows or sets options:

option [option1[=value]] [option2 ...]

option displays the current options. option MyOption displays the value, if it exists, of MyOption. option MyOption=4 defines and/or sets a value for MyOption.

• perl — evaluates the current statement as Perl code:

perl

• quit — quits the shell. Same as exit:

quit

• redo — re-executes the previously executed statement:

redo

• rhistory — displays result history:

rhistory

• rollback — rolls back changes to the database:

rollback

For this to be useful, turn autocommit off with option autocommit=0.

• run — run file name loads and executes a file:

run file name

This command loads the file (it may include a full path) and executes it. The file is loaded and replaces the current buffer. Only 1 statement per file is allowed (at this time).

• save — save file name writes the contents of the current buffer to a file:

save file name

The contents of the current buffer are written to the file. Currently, this command will overwrite a file if it exists.

• spool — controls output spooling:

spool file name
spool on
spool off
spool

spool file name starts sending all output to file name. spool on starts sending all output to on.lst. spool off stops sending output. spool displays the status of spooling. When spooling, everything seen in the command window is written to a file (except some of the prompts).

• table_info — displays all tables that exist in the current database. table_info | more is for paging:

table_info
table_info | more

• trace — sets the DBI trace level for the current database:

trace

Adjust the trace level for DBI from 0 to 4. 0 is off. 4 gives lots of information. Useful for determining what is really happening in DBI. See DBI.

• type_info — displays data types supported by the current server:

type_info

🧚 CONFIGURATION 🧚

The configuration file — ~/.dbish_config by default, overridden by $DBISH_CONFIG — is eval'd as Perl code and should return a hash ref containing the configuration items. Notable keys include:

• options — the value is another hashref containing the options and their values. Type /option at the prompt to get a list of acceptable options.
• plug-ins — plugins to load. This is a rather complex data structure which is recursively walked to determine which plugins should be loaded. At the top level, there are the phases "pre-init", "post-init", "pre-connect", and "post-connect". At lower levels plugins can be selected by the name of the database driver, and individual options can be set. Finally, at some point an arrayref containing the package names of the plugins to load must be found.

Since the config file can contain arbitrary Perl code, it can do anything which can be done in Perl. One rather useful hack is to call binmode to set the encoding for standard input and output.

🍓 Example 🍓

binmode STDIN,  ":utf8";
binmode STDOUT, ":utf8";

{
    options => {
        format => 'partbox',
    },
    'plug-ins' => {
        'pre-init' => [
            'DBI::Shell::Timing',
            'DBI::Shell::Spool',
        ],
    },
};

This config file first sets the :utf8 layer for input and output to match the terminal (if you don't use UTF-8, you could use :encoding() here), and then returns the hashref with the configuration items: the format is set to 'partbox', and the two plugins 'DBI::Shell::Timing' and 'DBI::Shell::Spool' are loaded.

🌈 ENVIRONMENT 🌈

• DBISH_TMP — where to write temporary files.
• DBISH_CONFIG — which configuration file is used. Unset it to not read any additional configurations.

👑 AUTHORS and ACKNOWLEDGEMENTS 👑

The DBI::Shell has a long lineage, and honestly the lore is real. 👑

It started life around 1994-1997 as the pmsql script written by Andreas König. Jochen Wiedmann picked it up and ran with it (adding much along the way) as dbimon, bundled with his DBD::mSQL driver modules. In 1998, around the time I wanted to bundle a shell with the DBI, Adam Marks was working on a dbish modeled after the Sybase sqsh utility.

Wanting to start from a cleaner slate than the feature-full but complex dbimon, I worked with Adam to create a fairly open modular and very configurable DBI::Shell module. Along the way Tom Lowery chipped in ideas and patches. As we go further along more useful code and concepts from Jochen's dbimon is bound to find its way back in.

⭐ COPYRIGHT ⭐

The DBI::Shell module is Copyright (c) 1998 Tim Bunce. England. All rights reserved. Portions are Copyright by Jochen Wiedmann, Adam Marks, Tom Lowery, Kent Fredric, Rafael Kitover and Mike Pomraning. It is currently maintained by Dave Lambley.

You may distribute under the terms of either the GNU General Public License or the Artistic License, as specified in the Perl README file.

---

Source/version: perl v5.42.0 • date: 2025-12-14 🫶 That's the whole DBI::Shell(3) page, bestie — go be iconic in your terminal. 💖