1SQL::Shell::Manual(3) User Contributed Perl DocumentationSQL::Shell::Manual(3)
2
3
4

NAME

6       SQL::Shell::Manual - user guide for sql shell
7

SYNOPSIS

9       sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
10

DESCRIPTION

12       This is a guide to using sqlsh.  sqlsh is an interactive shell run from
13       the command-line for workling with databases.  It can also be run in
14       "batch mode" taking a list of commands from stdin (using the -i switch)
15       or you can pass a single command to it on the command-line.
16
17   Connecting
18       Either set a DSN in the environment as DBI_DSN, supply with the -d
19       option or use the connect command:
20
21               unixbox% sqlsh
22               unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
23
24       You can also connect from inside sqlsh:
25
26               unixbox% sqlsh
27               > connect DBI:Oracle:IFLDEV scott tiger
28               DBI:Oracle:IFLDEV> show $dbh Name
29               +--------+
30               | Name   |
31               +--------+
32               | IFLDEV |
33               +--------+
34
35       and disconnect:
36
37               DBI:Oracle:IFLDEV> disconnect
38               > show $dbh Name
39               Not connected to database.
40
41       If you don't supply a password, sqlsh will prompt you:
42
43               unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott
44               Enter password for scott:
45
46       You can specify a blank password by passing -p:
47
48               unixbox% sqlsh -d DBI:Oracle:IFLDEV -u guest -p
49
50       From within sqlsh you can get a list of DBI drivers:
51
52               unixbox% sqlsh
53               > show drivers
54
55                 CSV
56                 DBM
57                 ExampleP
58                 Excel
59                 File
60                 Multiplex
61                 Oracle
62                 Proxy
63                 SQLite
64                 Sponge
65                 mysql
66
67       and a list of possible data sources for a driver:
68
69               unixbox% sqlsh
70               > show datasources Oracle
71
72                 dbi:Oracle:GISCPS
73                 dbi:Oracle:IFL1
74                 dbi:Oracle:IFLDEV
75                 dbi:Oracle:IFLTEST
76
77       Common DBI DSNs include:
78
79               DBI:Oracle:<SID>
80               DBI:mysql:<DB>
81               DBI:ADO:<DSN>
82               DBI:Excel:file=<xls>
83               DBI:CSV:f_dir=<dir>
84               DBI:SQLite:dbname=<filename>
85
86   Exploring the schema
87       show tables
88
89       This lists the tables with a rowcount for each:
90
91               DBI:SQLite:dbname=test.db> show tables
92               +----------------------+------+
93               | table                | rows |
94               +----------------------+------+
95               | "sqlite_master"      | 1    |
96               | "sqlite_temp_master" | 0    |
97               | "commands"           | 11   |
98               +----------------------+------+
99
100       For some database drivers this may include some system tables.
101
102       desc
103
104       Lists the columns in a table:
105
106               DBI:Oracle:IFLDEV> desc commands
107               +-------------+----------------+------+
108               | Field       | Type           | Null |
109               +-------------+----------------+------+
110               | COMMAND     | VARCHAR2(200)  | YES  |
111               | DESCRIPTION | VARCHAR2(1020) | YES  |
112               +-------------+----------------+------+
113
114       show schema
115
116       Lists the columns in a table, for each table in the schema:
117
118               DBI:Oracle:IFLDEV> show schema
119
120               schema dump
121               COMMANDS:
122               +-------------+----------------+------+
123               | Field       | Type           | Null |
124               +-------------+----------------+------+
125               | COMMAND     | VARCHAR2(200)  | YES  |
126               | DESCRIPTION | VARCHAR2(1020) | YES  |
127               +-------------+----------------+------+
128
129   Querying the database
130               DBI:SQLite:dbname=test.db> select * from commands
131               +------------------+--------------------------------------------------------------+
132               | command          | desc                                                         |
133               +------------------+--------------------------------------------------------------+
134               | show drivers     | Displays a list of DBI drivers                               |
135               | show datasources | Displays a list of available data sources for a driver       |
136               | connect          | Connects to a data source                                    |
137               | disconnect       | Disconnects from a data source                               |
138               | show tables      | List the tables in the schema with a rowcount for each table |
139               | show schema      | Lists the columns in each table in the schema                |
140               | desc             | List the columns in a table                                  |
141               | set              | Set a parameter                                              |
142               | help             | Displays sqlsh help in your $PAGER                           |
143               | reload           | Reloads sqlsh                                                |
144               | exit             | Quits sqlsh                                                  |
145               +------------------+--------------------------------------------------------------+
146
147       BLOB values
148
149       You can control the amount of BLOB data fetched by setting the
150       "longreadlen" parameter.
151
152               DBI:Oracle:IFLDEV> set longreadlen 4096
153               LongReadLen set to '4096'
154
155               DBI:Oracle:IFLDEV> show $dbh LongReadLen
156               +-------------+
157               | LongReadLen |
158               +-------------+
159               | 4096        |
160               +-------------+
161
162        Note that the C<longtruncok> parameter should also be set (it is by default):
163
164               DBI:Oracle:IFLDEV> show $dbh LongTruncOk
165               +-------------+
166               | LongTruncOk |
167               +-------------+
168               | 1           |
169               +-------------+
170
171       Values containing non-word characters
172
173       Suppose we have values in our database which contain whitespace
174       characters (e.g. tabs):
175
176               DBI:Oracle:IFLDEV> set enter-whitespace on
177               Whitespace may be entered as \n, \r and \t
178
179               DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
180               INSERT commands: 1 rows affected
181
182       When we query the table we see these as literal values:
183
184               DBI:Oracle:IFLDEV> select * from commands
185               +---------+-------------+
186               | COMMAND | DESCRIPTION |
187               +---------+-------------+
188               | test    | one two     |
189               +---------+-------------+
190
191       We can instead chose to display them escaped:
192
193               DBI:Oracle:IFLDEV> set escape show-whitespace
194               DBI:Oracle:IFLDEV> select * from commands
195               +---------+-------------+
196               | COMMAND | DESCRIPTION |
197               +---------+-------------+
198               | test    | one\ttwo    |
199               +---------+-------------+
200
201       Alternatively we can use uri-escaping:
202
203               DBI:Oracle:IFLDEV> set escape uri-escape on
204               DBI:Oracle:IFLDEV> select * from commands
205               +---------+-------------+
206               | COMMAND | DESCRIPTION |
207               +---------+-------------+
208               | test    | one%09two   |
209               +---------+-------------+
210
211       Entering multi-line statements
212
213       To enable multiline mode:
214
215               DBI:Oracle:IFLDEV> set multiline on
216
217       You can then build up statements over multiple lines, ending with a
218       semicolon, e.g.:
219
220               DBI:Oracle:IFLDEV> select
221               DBI:Oracle:IFLDEV> count(*)
222               DBI:Oracle:IFLDEV> from
223               DBI:Oracle:IFLDEV> commands
224               DBI:Oracle:IFLDEV> ;
225               +----------+
226               | COUNT(*) |
227               +----------+
228               | 11       |
229               +----------+
230
231       To disable multiline mode, remember you need to end the statement in a
232       semicolon:
233
234               DBI:Oracle:IFLDEV> set multiline off;
235
236       Altering the display mode
237
238       The default ("box") display mode is similar to that used by the mysql
239       client - it works well for tables of fairly short values.  The "record"
240       display mode is good for viewing single records:
241
242               DBI:SQLite:dbname=test.db> set display-mode record
243               DBI:SQLite:dbname=test.db> select * from commands where command='desc'
244               --------------------------------------------------------------------------------
245               command | desc
246               desc    | List the columns in a table
247               --------------------------------------------------------------------------------
248
249       The "spaced" display mode (despite sounding like a description of
250       sqlsh's author) provides a minimum clutter view of the data.  The
251       "tabbed" display mode generally looks horrendous but is useful for a
252       quick cut+paste of delimited values.  The "sql" display mode generates
253       insert statements using a $table placeholder for where the data is to
254       be inserted.  The "xml" display mode generates element-only XML which
255       can be parsed into a list of hashes with XML::Simple.
256
257   Altering the database
258       By default transactions are not automatically committed so you must
259       explicitly commit them:
260
261               DBI:Oracle:IFLDEV> insert into commands(command, description) values ('dump','Writes a table or query results to a delimited file')
262               INSERT commands: 1 rows affected
263
264               DBI:Oracle:IFLDEV> commit
265
266       and you can roll back mistakes:
267
268               DBI:Oracle:IFLDEV> delete from commands
269               DELETE commands: 11 rows affected
270
271               DBI:Oracle:IFLDEV> rollback
272               DBI:Oracle:IFLDEV> select count(*) from commands
273               +----------+
274               | COUNT(*) |
275               +----------+
276               | 11       |
277               +----------+
278
279       If you prefer to live dangerously you can switch autocommit on:
280
281               set autocommit on
282               insert ...
283               update ...
284
285       Clearing the database
286
287       The "wipe tables" command can be used to remove all the data each of
288       the tables in the database:
289
290               DBI:Oracle:IFLDEV> wipe tables
291               Wipe all data from:
292
293                 COMMANDS
294
295               Are you sure you want to do this? (type 'yes' if you are) yes
296
297               Wiped all data in database
298
299       It prompts you to confirm before anihilating your database.
300
301   Dumping delimited data
302       "dump" can either be used to dump an entire table:
303
304               dump mytable into export.txt
305
306       or the rowset resulting from a query:
307
308               dump select type, count(*) from mytable group by type into histogram.txt delimited by :
309
310       An example:
311
312               DBI:SQLite:dbname=test.db> dump commands into commands.csv delimited by ,
313               Dumping commands into commands.csv
314               Dumped 11 rows into commands.csv
315
316               DBI:SQLite:dbname=test.db> more commands.csv
317               command,desc
318               show drivers,Displays a list of DBI drivers
319               show datasources,Displays a list of available data sources for a driver
320               connect,Connects to a data source
321               disconnect,Disconnects from a data source
322               show tables,List the tables in the schema with a rowcount for each table
323               show schema,Lists the columns in each table in the schema
324               desc,List the columns in a table
325               set,Set a parameter
326               help,Displays sqlsh help in your $PAGER
327               reload,Reloads sqlsh
328               exit,Quits sqlsh
329
330       You can also dump all the tables in a database into a directory:
331
332               dump all tables into dumpdir/
333
334   Logging
335       You can chose to log commands:
336
337               log commands logfile.txt
338
339       or query results:
340
341               log queries dumpfile.txt
342
343       or both:
344
345               log all history.log
346
347   Exporting data as XML
348               DBI:Oracle:IFLDEV> set log-mode xml
349
350               DBI:Oracle:IFLDEV> log queries export.xml
351               Logging queries to export.xml
352
353               DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
354               +------------------+--------------------------------------------------------------+
355               | COMMAND          | DESCRIPTION                                                  |
356               +------------------+--------------------------------------------------------------+
357               | show drivers     | Displays a list of DBI drivers                               |
358               | show datasources | Displays a list of available data sources for a driver       |
359               | show tables      | List the tables in the schema with a rowcount for each table |
360               | show schema      | Lists the columns in each table in the schema                |
361               +------------------+--------------------------------------------------------------+
362
363               DBI:Oracle:IFLDEV>> more export.xml
364               <rowset>
365                       <record>
366                               <COMMAND>show drivers</COMMAND>
367                               <DESCRIPTION>Displays a list of DBI drivers</DESCRIPTION>
368                       </record>
369                       <record>
370                               <COMMAND>show datasources</COMMAND>
371                               <DESCRIPTION>Displays a list of available data sources for a driver</DESCRIPTION>
372                       </record>
373                       <record>
374                               <COMMAND>show tables</COMMAND>
375                               <DESCRIPTION>List the tables in the schema with a rowcount for each table</DESCRIPTION>
376                       </record>
377                       <record>
378                               <COMMAND>show schema</COMMAND>
379                               <DESCRIPTION>Lists the columns in each table in the schema</DESCRIPTION>
380                       </record>
381               </rowset>
382
383               DBI:Oracle:IFLDEV>> no log
384               Stopped logging queries
385
386   Exporting data as SQL
387               DBI:Oracle:IFLDEV> set log-mode sql
388
389               DBI:Oracle:IFLDEV> log queries export.sql
390               Logging queries to export.sql
391
392               DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
393               +------------------+--------------------------------------------------------------+
394               | COMMAND          | DESCRIPTION                                                  |
395               +------------------+--------------------------------------------------------------+
396               | show drivers     | Displays a list of DBI drivers                               |
397               | show datasources | Displays a list of available data sources for a driver       |
398               | show tables      | List the tables in the schema with a rowcount for each table |
399               | show schema      | Lists the columns in each table in the schema                |
400               +------------------+--------------------------------------------------------------+
401
402               DBI:Oracle:IFLDEV>> more export.sql
403               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show drivers','Displays a list of DBI drivers');
404               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show datasources','Displays a list of available data sources for a driver');
405               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tables','List the tables in the schema with a rowcount for each table');
406               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show schema','Lists the columns in each table in the schema');
407
408               DBI:Oracle:IFLDEV>> no log
409               Stopped logging queries
410
411       You can then replace $table with the table name you want the INSERT
412       stataments to be issued against:
413
414               unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql
415
416   Loading data
417       Loading a tab-delimited text file is simple:
418
419               load export.txt into mytable
420
421       Here's an example:
422
423               DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))
424               CREATE table commands: 0 rows affected
425
426               DBI:SQLite:dbname=test.db> load commands.tsv into commands
427               Loaded 11 rows into commands from commands.tsv
428
429       As with "dump" you can change the delimiter character:
430
431               load export.csv into mytable delimited by ,
432
433       You can also specify character set translations:
434
435               load export.txt into mytable from CP1252 to UTF-8
436
437       if your database engine cannot do the character set conversions itself.
438       See Locale::Recode for a list of character set names.
439
440   Manipulating the command history
441       You can dump out the history to a file:
442
443               save history to history.txt
444
445       You can also load in a set of commands into the history:
446
447               load history from handy_queries.sql
448
449       This can be useful in conjunction with "log commands".  You can clear
450       the history at any time with:
451
452               clear history
453
454       and display it with:
455
456               show history
457
458   Running batches of commands
459       You can execute a sequence of sqlsh commands from a file:
460
461               > execute commands.sqlsh
462
463       that might have been generated by "save history" or "log commands".
464       You can also pipe commands into sqlsh on STDIN if you call it with the
465       "-i" switch:
466
467               unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh
468

VERSION

470       $Revision: 1.6 $ on $Date: 2006/08/02 12:01:15 $ by $Author: johna $
471

AUTHOR

473       John Alden
474
475
476
477perl v5.12.3                      2006-12-05             SQL::Shell::Manual(3)
Impressum