SQL::Shell::Manual(3) User Contributed Perl DocumentationSQL::Shell::Manual(3)NAMESQL::Shell::Manual - user guide for sql shell
SYNOPSIS
sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
DESCRIPTION
This is a guide to using sqlsh. sqlsh is an interactive shell run from
the command-line for workling with databases. It can also be run in
"batch mode" taking a list of commands from stdin (using the -i switch)
or you can pass a single command to it on the command-line.
Connecting
Either set a DSN in the environment as DBI_DSN, supply with the -d
option or use the connect command:
unixbox% sqlsh
unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
You can also connect from inside sqlsh:
unixbox% sqlsh
> connect DBI:Oracle:IFLDEV scott tiger
DBI:Oracle:IFLDEV> show $dbh Name
+--------+
| Name |
+--------+
| IFLDEV |
+--------+
and disconnect:
DBI:Oracle:IFLDEV> disconnect
> show $dbh Name
Not connected to database.
If you don't supply a password, sqlsh will prompt you:
unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott
Enter password for scott:
You can specify a blank password by passing -p:
unixbox% sqlsh -d DBI:Oracle:IFLDEV -u guest -p
From within sqlsh you can get a list of DBI drivers:
unixbox% sqlsh
> show drivers
CSV
DBM
ExampleP
Excel
File
Multiplex
Oracle
Proxy
SQLite
Sponge
mysql
and a list of possible data sources for a driver:
unixbox% sqlsh
> show datasources Oracle
dbi:Oracle:GISCPS
dbi:Oracle:IFL1
dbi:Oracle:IFLDEV
dbi:Oracle:IFLTEST
Common DBI DSNs include:
DBI:Oracle:<SID>
DBI:mysql:<DB>
DBI:ADO:<DSN>
DBI:Excel:file=<xls>
DBI:CSV:f_dir=<dir>
DBI:SQLite:dbname=<filename>
Exploring the schema
show tables
This lists the tables with a rowcount for each:
DBI:SQLite:dbname=test.db> show tables
+----------------------+------+
| table | rows |
+----------------------+------+
| "sqlite_master" | 1 |
| "sqlite_temp_master" | 0 |
| "commands" | 11 |
+----------------------+------+
For some database drivers this may include some system tables.
desc
Lists the columns in a table:
DBI:Oracle:IFLDEV> desc commands
+-------------+----------------+------+
| Field | Type | Null |
+-------------+----------------+------+
| COMMAND | VARCHAR2(200) | YES |
| DESCRIPTION | VARCHAR2(1020) | YES |
+-------------+----------------+------+
show schema
Lists the columns in a table, for each table in the schema:
DBI:Oracle:IFLDEV> show schema
schema dump
COMMANDS:
+-------------+----------------+------+
| Field | Type | Null |
+-------------+----------------+------+
| COMMAND | VARCHAR2(200) | YES |
| DESCRIPTION | VARCHAR2(1020) | YES |
+-------------+----------------+------+
Querying the database
DBI:SQLite:dbname=test.db> select * from commands
+------------------+--------------------------------------------------------------+
| command | desc |
+------------------+--------------------------------------------------------------+
| show drivers | Displays a list of DBI drivers |
| show datasources | Displays a list of available data sources for a driver |
| connect | Connects to a data source |
| disconnect | Disconnects from a data source |
| show tables | List the tables in the schema with a rowcount for each table |
| show schema | Lists the columns in each table in the schema |
| desc | List the columns in a table |
| set | Set a parameter |
| help | Displays sqlsh help in your $PAGER |
| reload | Reloads sqlsh |
| exit | Quits sqlsh |
+------------------+--------------------------------------------------------------+
BLOB values
You can control the amount of BLOB data fetched by setting the
"longreadlen" parameter.
DBI:Oracle:IFLDEV> set longreadlen 4096
LongReadLen set to '4096'
DBI:Oracle:IFLDEV> show $dbh LongReadLen
+-------------+
| LongReadLen |
+-------------+
| 4096 |
+-------------+
Note that the C<longtruncok> parameter should also be set (it is by default):
DBI:Oracle:IFLDEV> show $dbh LongTruncOk
+-------------+
| LongTruncOk |
+-------------+
| 1 |
+-------------+
Values containing non-word characters
Suppose we have values in our database which contain whitespace
characters (e.g. tabs):
DBI:Oracle:IFLDEV> set enter-whitespace on
Whitespace may be entered as \n, \r and \t
DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
INSERT commands: 1 rows affected
When we query the table we see these as literal values:
DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test | one two |
+---------+-------------+
We can instead chose to display them escaped:
DBI:Oracle:IFLDEV> set escape show-whitespace
DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test | one\ttwo |
+---------+-------------+
Alternatively we can use uri-escaping:
DBI:Oracle:IFLDEV> set escape uri-escape on
DBI:Oracle:IFLDEV> select * from commands
+---------+-------------+
| COMMAND | DESCRIPTION |
+---------+-------------+
| test | one%09two |
+---------+-------------+
Entering multi-line statements
To enable multiline mode:
DBI:Oracle:IFLDEV> set multiline on
You can then build up statements over multiple lines, ending with a
semicolon, e.g.:
DBI:Oracle:IFLDEV> select
DBI:Oracle:IFLDEV> count(*)
DBI:Oracle:IFLDEV> from
DBI:Oracle:IFLDEV> commands
DBI:Oracle:IFLDEV> ;
+----------+
| COUNT(*) |
+----------+
| 11 |
+----------+
To disable multiline mode, remember you need to end the statement in a
semicolon:
DBI:Oracle:IFLDEV> set multiline off;
Altering the display mode
The default ("box") display mode is similar to that used by the mysql
client - it works well for tables of fairly short values. The "record"
display mode is good for viewing single records:
DBI:SQLite:dbname=test.db> set display-mode record
DBI:SQLite:dbname=test.db> select * from commands where command='desc'
--------------------------------------------------------------------------------
command | desc
desc | List the columns in a table
--------------------------------------------------------------------------------
The "spaced" display mode (despite sounding like a description of
sqlsh's author) provides a minimum clutter view of the data. The
"tabbed" display mode generally looks horrendous but is useful for a
quick cut+paste of delimited values. The "sql" display mode generates
insert statements using a $table placeholder for where the data is to
be inserted. The "xml" display mode generates element-only XML which
can be parsed into a list of hashes with XML::Simple.
Altering the database
By default transactions are not automatically committed so you must
explicitly commit them:
DBI:Oracle:IFLDEV> insert into commands(command, description) values ('dump','Writes a table or query results to a delimited file')
INSERT commands: 1 rows affected
DBI:Oracle:IFLDEV> commit
and you can roll back mistakes:
DBI:Oracle:IFLDEV> delete from commands
DELETE commands: 11 rows affected
DBI:Oracle:IFLDEV> rollback
DBI:Oracle:IFLDEV> select count(*) from commands
+----------+
| COUNT(*) |
+----------+
| 11 |
+----------+
If you prefer to live dangerously you can switch autocommit on:
set autocommit on
insert ...
update ...
Clearing the database
The "wipe tables" command can be used to remove all the data each of
the tables in the database:
DBI:Oracle:IFLDEV> wipe tables
Wipe all data from:
COMMANDS
Are you sure you want to do this? (type 'yes' if you are) yes
Wiped all data in database
It prompts you to confirm before anihilating your database.
Dumping delimited data
"dump" can either be used to dump an entire table:
dump mytable into export.txt
or the rowset resulting from a query:
dump select type, count(*) from mytable group by type into histogram.txt delimited by :
An example:
DBI:SQLite:dbname=test.db> dump commands into commands.csv delimited by ,
Dumping commands into commands.csv
Dumped 11 rows into commands.csv
DBI:SQLite:dbname=test.db> more commands.csv
command,desc
show drivers,Displays a list of DBI drivers
show datasources,Displays a list of available data sources for a driver
connect,Connects to a data source
disconnect,Disconnects from a data source
show tables,List the tables in the schema with a rowcount for each table
show schema,Lists the columns in each table in the schema
desc,List the columns in a table
set,Set a parameter
help,Displays sqlsh help in your $PAGER
reload,Reloads sqlsh
exit,Quits sqlsh
You can also dump all the tables in a database into a directory:
dump all tables into dumpdir/
Logging
You can chose to log commands:
log commands logfile.txt
or query results:
log queries dumpfile.txt
or both:
log all history.log
Exporting data as XML
DBI:Oracle:IFLDEV> set log-mode xml
DBI:Oracle:IFLDEV> log queries export.xml
Logging queries to export.xml
DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
+------------------+--------------------------------------------------------------+
| COMMAND | DESCRIPTION |
+------------------+--------------------------------------------------------------+
| show drivers | Displays a list of DBI drivers |
| show datasources | Displays a list of available data sources for a driver |
| show tables | List the tables in the schema with a rowcount for each table |
| show schema | Lists the columns in each table in the schema |
+------------------+--------------------------------------------------------------+
DBI:Oracle:IFLDEV>> more export.xml
<rowset>
<record>
<COMMAND>show drivers</COMMAND>
<DESCRIPTION>Displays a list of DBI drivers</DESCRIPTION>
</record>
<record>
<COMMAND>show datasources</COMMAND>
<DESCRIPTION>Displays a list of available data sources for a driver</DESCRIPTION>
</record>
<record>
<COMMAND>show tables</COMMAND>
<DESCRIPTION>List the tables in the schema with a rowcount for each table</DESCRIPTION>
</record>
<record>
<COMMAND>show schema</COMMAND>
<DESCRIPTION>Lists the columns in each table in the schema</DESCRIPTION>
</record>
</rowset>
DBI:Oracle:IFLDEV>> no log
Stopped logging queries
Exporting data as SQL
DBI:Oracle:IFLDEV> set log-mode sql
DBI:Oracle:IFLDEV> log queries export.sql
Logging queries to export.sql
DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
+------------------+--------------------------------------------------------------+
| COMMAND | DESCRIPTION |
+------------------+--------------------------------------------------------------+
| show drivers | Displays a list of DBI drivers |
| show datasources | Displays a list of available data sources for a driver |
| show tables | List the tables in the schema with a rowcount for each table |
| show schema | Lists the columns in each table in the schema |
+------------------+--------------------------------------------------------------+
DBI:Oracle:IFLDEV>> more export.sql
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show drivers','Displays a list of DBI drivers');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show datasources','Displays a list of available data sources for a driver');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tables','List the tables in the schema with a rowcount for each table');
INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show schema','Lists the columns in each table in the schema');
DBI:Oracle:IFLDEV>> no log
Stopped logging queries
You can then replace $table with the table name you want the INSERT
stataments to be issued against:
unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql
Loading data
Loading a tab-delimited text file is simple:
load export.txt into mytable
Here's an example:
DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))
CREATE table commands: 0 rows affected
DBI:SQLite:dbname=test.db> load commands.tsv into commands
Loaded 11 rows into commands from commands.tsv
As with "dump" you can change the delimiter character:
load export.csv into mytable delimited by ,
You can also specify character set translations:
load export.txt into mytable from CP1252 to UTF-8
if your database engine cannot do the character set conversions itself.
See Locale::Recode for a list of character set names.
Manipulating the command history
You can dump out the history to a file:
save history to history.txt
You can also load in a set of commands into the history:
load history from handy_queries.sql
This can be useful in conjunction with "log commands". You can clear
the history at any time with:
clear history
and display it with:
show history
Running batches of commands
You can execute a sequence of sqlsh commands from a file:
> execute commands.sqlsh
that might have been generated by "save history" or "log commands".
You can also pipe commands into sqlsh on STDIN if you call it with the
"-i" switch:
unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh
VERSION
$Revision: 1.6 $ on $Date: 2006/08/02 12:01:15 $ by $Author: johna $
AUTHOR
John Alden
perl v5.14.1 2006-12-05 SQL::Shell::Manual(3)
