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 in your database along with other attributes that
90       may be provided by your platform and driver:
91
92           DBI:SQLite:dbname=test.db> show tables
93           +-----------+-------------+--------------------+--------------+---------+
94           | TABLE_CAT | TABLE_SCHEM | TABLE_NAME         | TABLE_TYPE   | REMARKS |
95           +-----------+-------------+--------------------+--------------+---------+
96           | NULL      | main        | sqlite_master      | SYSTEM TABLE | NULL    |
97           | NULL      | temp        | sqlite_temp_master | SYSTEM TABLE | NULL    |
98           | NULL      | main        | commands           | TABLE        | NULL    |
99           +-----------+-------------+--------------------+--------------+---------+
100
101       For some database drivers this may include some system tables.
102
103       show tablecounts
104
105       This lists the tables with a rowcount for each:
106
107           DBI:SQLite:dbname=test.db> show tablecounts
108           +-----------------------------+------+
109           | table                       | rows |
110           +-----------------------------+------+
111           | "main"."sqlite_master"      | 2    |
112           | "temp"."sqlite_temp_master" | 0    |
113           | "main"."commands"           | 12   |
114           +-----------------------------+------+
115
116       For some database drivers this may include some system tables. This
117       command does a "SELECT COUNT(*) FROM" on every table in your database.
118       You may not want to do this on databases with large numbers of tables,
119       and/or tables with large numbers of rows.
120
121       show catalogs
122
123       If your platform supports it, this shows a listing of available
124       database catalogs.
125
126           DBI:ODBC:localdb> show catalogs
127           +----------------+
128           | TABLE_CAT      |
129           +----------------+
130           | AdventureWorks |
131           | master         |
132           | msdb           |
133           | tempdb         |
134           +----------------+
135
136       show schemas
137
138       This command will list the schemas available in your database.  Note
139       that this is different from "show schema" (singular), which shows table
140       descriptions for every table in your schema (see below).
141
142           DBI:SQLite:dbname=test.db> show schemas
143           +-------------+
144           | TABLE_SCHEM |
145           +-------------+
146           | main        |
147           | temp        |
148           +-------------+
149
150       show tabletypes
151
152       List the available table-types in your database.
153
154           DBI:SQLite:dbname=test.db> show tabletypes
155           +-----------------+
156           | TABLE_TYPE      |
157           +-----------------+
158           | LOCAL TEMPORARY |
159           | SYSTEM TABLE    |
160           | TABLE           |
161           | VIEW            |
162           +-----------------+
163
164       desc
165
166       Lists the columns in a table:
167
168               DBI:Oracle:IFLDEV> desc commands
169               +-------------+----------------+------+
170               | Field       | Type           | Null |
171               +-------------+----------------+------+
172               | COMMAND     | VARCHAR2(200)  | YES  |
173               | DESCRIPTION | VARCHAR2(1020) | YES  |
174               +-------------+----------------+------+
175
176       show schema
177
178       Lists the columns in a table, for each table in the schema:
179
180               DBI:Oracle:IFLDEV> show schema
181
182               schema dump
183               COMMANDS:
184               +-------------+----------------+------+
185               | Field       | Type           | Null |
186               +-------------+----------------+------+
187               | COMMAND     | VARCHAR2(200)  | YES  |
188               | DESCRIPTION | VARCHAR2(1020) | YES  |
189               +-------------+----------------+------+
190
191   Current shell settings (show settings)
192       To list some "sqlsh" internal settings:
193
194           DBI:SQLite:dbname=test.db> show settings
195           +------------------+-------+
196           | PARAMETER        | VALUE |
197           +------------------+-------+
198           | auto-commit      | on    |
199           | delimiter        | \t    |
200           | enter-whitespace |       |
201           | escape           | off   |
202           | longreadlen      | 512   |
203           | longtruncok      | on    |
204           | multiline        | off   |
205           | verbose          | on    |
206           | width            | 80    |
207           +------------------+-------+
208
209       Note that not all settings are yet included in this output.
210
211   Querying the database
212               DBI:SQLite:dbname=test.db> select * from commands
213               +------------------+--------------------------------------------------------------+
214               | command          | desc                                                         |
215               +------------------+--------------------------------------------------------------+
216               | show drivers     | Displays a list of DBI drivers                               |
217               | show datasources | Displays a list of available data sources for a driver       |
218               | connect          | Connects to a data source                                    |
219               | disconnect       | Disconnects from a data source                               |
220               | show tables      | List the tables in the schema                                |
221               | show tablecounts | List the tables in the schema with a rowcount for each table |
222               | show schema      | Lists the columns in each table in the schema                |
223               | desc             | List the columns in a table                                  |
224               | set              | Set a parameter                                              |
225               | help             | Displays sqlsh help in your $PAGER                           |
226               | reload           | Reloads sqlsh                                                |
227               | exit             | Quits sqlsh                                                  |
228               +------------------+--------------------------------------------------------------+
229
230       BLOB values
231
232       You can control the amount of BLOB data fetched by setting the
233       "longreadlen" parameter.
234
235               DBI:Oracle:IFLDEV> set longreadlen 4096
236               LongReadLen set to '4096'
237
238               DBI:Oracle:IFLDEV> show $dbh LongReadLen
239               +-------------+
240               | LongReadLen |
241               +-------------+
242               | 4096        |
243               +-------------+
244
245        Note that the C<longtruncok> parameter should also be set (it is by default):
246
247               DBI:Oracle:IFLDEV> show $dbh LongTruncOk
248               +-------------+
249               | LongTruncOk |
250               +-------------+
251               | 1           |
252               +-------------+
253
254       Values containing non-word characters
255
256       Suppose we have values in our database which contain whitespace
257       characters (e.g. tabs):
258
259               DBI:Oracle:IFLDEV> set enter-whitespace on
260               Whitespace may be entered as \n, \r and \t
261
262               DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
263               INSERT commands: 1 rows affected
264
265       When we query the table we see these as literal values:
266
267               DBI:Oracle:IFLDEV> select * from commands
268               +---------+-------------+
269               | COMMAND | DESCRIPTION |
270               +---------+-------------+
271               | test    | one two     |
272               +---------+-------------+
273
274       We can instead chose to display them escaped:
275
276               DBI:Oracle:IFLDEV> set escape show-whitespace
277               DBI:Oracle:IFLDEV> select * from commands
278               +---------+-------------+
279               | COMMAND | DESCRIPTION |
280               +---------+-------------+
281               | test    | one\ttwo    |
282               +---------+-------------+
283
284       Alternatively we can use uri-escaping:
285
286               DBI:Oracle:IFLDEV> set escape uri-escape on
287               DBI:Oracle:IFLDEV> select * from commands
288               +---------+-------------+
289               | COMMAND | DESCRIPTION |
290               +---------+-------------+
291               | test    | one%09two   |
292               +---------+-------------+
293
294       Entering multi-line statements
295
296       To enable multiline mode:
297
298               DBI:Oracle:IFLDEV> set multiline on
299
300       You can then build up statements over multiple lines, ending with a
301       semicolon, e.g.:
302
303               DBI:Oracle:IFLDEV> select
304               DBI:Oracle:IFLDEV> count(*)
305               DBI:Oracle:IFLDEV> from
306               DBI:Oracle:IFLDEV> commands
307               DBI:Oracle:IFLDEV> ;
308               +----------+
309               | COUNT(*) |
310               +----------+
311               | 12       |
312               +----------+
313
314       To disable multiline mode, remember you need to end the statement in a
315       semicolon:
316
317               DBI:Oracle:IFLDEV> set multiline off;
318
319       Altering the display mode
320
321       The default ("box") display mode is similar to that used by the mysql
322       client - it works well for tables of fairly short values.  The "record"
323       display mode is good for viewing single records:
324
325               DBI:SQLite:dbname=test.db> set display-mode record
326               DBI:SQLite:dbname=test.db> select * from commands where command='desc'
327               --------------------------------------------------------------------------------
328               command | desc
329               desc    | List the columns in a table
330               --------------------------------------------------------------------------------
331
332       The "spaced" display mode (despite sounding like a description of
333       sqlsh's author) provides a minimum clutter view of the data.  The
334       "tabbed" display mode generally looks horrendous but is useful for a
335       quick cut+paste of delimited values.  The "sql" display mode generates
336       insert statements using a $table placeholder for where the data is to
337       be inserted.  The "xml" display mode generates element-only XML which
338       can be parsed into a list of hashes with XML::Simple.
339
340   Altering the database
341       By default transactions are not automatically committed so you must
342       explicitly commit them:
343
344               DBI:Oracle:IFLDEV> insert into commands(command, description) values ('dump','Writes a table or query results to a delimited file')
345               INSERT commands: 1 rows affected
346
347               DBI:Oracle:IFLDEV> commit
348
349       and you can roll back mistakes:
350
351               DBI:Oracle:IFLDEV> delete from commands
352               DELETE commands: 11 rows affected
353
354               DBI:Oracle:IFLDEV> rollback
355               DBI:Oracle:IFLDEV> select count(*) from commands
356               +----------+
357               | COUNT(*) |
358               +----------+
359               | 12       |
360               +----------+
361
362       If you prefer, you can switch autocommit on:
363
364               set autocommit on
365               insert ...
366               update ...
367
368       This is the preferred mode of operation when connecting to some
369       database platforms like SQL Server.  Depending on the platform, not all
370       commands work within a "transaction", and some platforms prefer that
371       they be run with autocommit on. Your mileage may vary.
372
373       Clearing the database
374
375       The "wipe tables" command can be used to remove all the data each of
376       the tables in the database:
377
378               DBI:Oracle:IFLDEV> wipe tables
379               Wipe all data from:
380
381                 COMMANDS
382
383               Are you sure you want to do this? (type 'yes' if you are) yes
384
385               Wiped all data in database
386
387       It prompts you to confirm before anihilating your database.
388
389   The send and recv commands
390       These commands were added in v1.16. Their purpose is to give the user
391       more control over how commands are interpreted by the shell before they
392       are sent to the DB without the need to make the shell identify the
393       commands to determine whether to expect output or not.  They are
394       intended to deal with platform-specific variations of SQL syntax that
395       aren't covered by the generic command-matching process in "sqlsh".
396
397       "send" is used when you don't expect output from the command, and
398       "recv" is used for cases where the command provides output, and you'd
399       like it rendered and displayed, as if it had come from a "select".
400
401       Example 1
402
403       On IBM Netezza, you can query previously deleted records by giving the
404       database the following command:
405
406           set show_deleted_records=TRUE
407
408       If you try to do that from "sqlsh", it will tell you that the command
409       is not recognized, because "sqlsh" has a built-in "set" command, and it
410       is trying to match it to what you have typed at the prompt.
411
412       The solution is to use the "send" command to submit the "set"
413       expression to the DB without having it intercepted by "sqlsh":
414
415           send set show_deleted_records=TRUE
416
417       Example 2
418
419       On SQL Server there are a number of procedure calls that provide
420       output.  For instance:
421
422           exec xp_cmdshell 'dir *.exe'
423
424       or simply
425
426           xp_cmdshell 'dir *.exe'
427
428       While "sqlsh" supports the "execute" command, this is intended to run
429       commands from a local SQL file.  In this case, "sqlsh" would just reply
430       that the command is not recognized.  The solution is to use the "recv"
431       command:
432
433           recv exec xp_cmdshell 'dir *.exe'
434
435       or
436           recv xp_cmdshell 'dir *.exe'
437
438       This will make "sqlsh" submit the exec expression to the DB as if it
439       were a "select" command, so that any output is rendered and displayed.
440
441       Example 3
442
443       Several database platforms allow giving the "use" command to query a
444       different database.  You are in effect switching databases without
445       disconnecting.  The command looks as follows:
446
447           use MY_DB_NAME
448
449       If I give this command while in "sqlsh", it will not be recognized,
450       however, I could pass it on to the DB by using "send":
451
452           send use MY_DB_NAME
453
454       In this case we type "send" instead or "recv" because we don't expect
455       any output from the "use" command.
456
457       Note that if your DB platform supports the "use" command, you may also
458       need a command to tell you which database you're currently using. This
459       is platform-dependent, but I will provide an example from SQL Server:
460
461           select dbname() as current_database
462
463   Dumping delimited data
464       "dump" can either be used to dump an entire table:
465
466               dump mytable into export.txt
467
468       or the rowset resulting from a query:
469
470               dump select type, count(*) from mytable group by type into histogram.txt delimited by :
471
472       An example:
473
474               DBI:SQLite:dbname=test.db> dump commands into commands.csv delimited by ,
475               Dumping commands into commands.csv
476               Dumped 12 rows into commands.csv
477
478               DBI:SQLite:dbname=test.db> more commands.csv
479               command,desc
480               show drivers,Displays a list of DBI drivers
481               show datasources,Displays a list of available data sources for a driver
482               connect,Connects to a data source
483               disconnect,Disconnects from a data source
484               show tables,List the tables in the schema with a rowcount for each table
485               show schema,Lists the columns in each table in the schema
486               desc,List the columns in a table
487               set,Set a parameter
488               help,Displays sqlsh help in your $PAGER
489               reload,Reloads sqlsh
490               exit,Quits sqlsh
491
492       You can also dump all the tables in a database into a directory:
493
494               dump all tables into dumpdir/
495
496   Logging
497       You can chose to log commands:
498
499               log commands logfile.txt
500
501       or query results:
502
503               log queries dumpfile.txt
504
505       or both:
506
507               log all history.log
508
509   Exporting data as XML
510               DBI:Oracle:IFLDEV> set log-mode xml
511
512               DBI:Oracle:IFLDEV> log queries export.xml
513               Logging queries to export.xml
514
515               DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
516               +------------------+--------------------------------------------------------------+
517               | COMMAND          | DESCRIPTION                                                  |
518               +------------------+--------------------------------------------------------------+
519               | show drivers     | Displays a list of DBI drivers                               |
520               | show datasources | Displays a list of available data sources for a driver       |
521               | show tables      | List the tables in the schema                                |
522               | show tablecounts | List the tables in the schema with a rowcount for each table |
523               | show schema      | Lists the columns in each table in the schema                |
524               +------------------+--------------------------------------------------------------+
525
526               DBI:Oracle:IFLDEV>> more export.xml
527               <rowset>
528                       <record>
529                               <COMMAND>show drivers</COMMAND>
530                               <DESCRIPTION>Displays a list of DBI drivers</DESCRIPTION>
531                       </record>
532                       <record>
533                               <COMMAND>show datasources</COMMAND>
534                               <DESCRIPTION>Displays a list of available data sources for a driver</DESCRIPTION>
535                       </record>
536                       <record>
537                               <COMMAND>show tables</COMMAND>
538                               <DESCRIPTION>List the tables in the schema</DESCRIPTION>
539                       </record>
540                       <record>
541                               <COMMAND>show tablecounts</COMMAND>
542                               <DESCRIPTION>List the tables in the schema with a rowcount for each table</DESCRIPTION>
543                       </record>
544                       <record>
545                               <COMMAND>show schema</COMMAND>
546                               <DESCRIPTION>Lists the columns in each table in the schema</DESCRIPTION>
547                       </record>
548               </rowset>
549
550               DBI:Oracle:IFLDEV>> no log
551               Stopped logging queries
552
553   Exporting data as SQL
554               DBI:Oracle:IFLDEV> set log-mode sql
555
556               DBI:Oracle:IFLDEV> log queries export.sql
557               Logging queries to export.sql
558
559               DBI:Oracle:IFLDEV>> select * from commands where command like 'show%'
560               +------------------+--------------------------------------------------------------+
561               | COMMAND          | DESCRIPTION                                                  |
562               +------------------+--------------------------------------------------------------+
563               | show drivers     | Displays a list of DBI drivers                               |
564               | show datasources | Displays a list of available data sources for a driver       |
565               | show tables      | List the tables in the schema                                |
566               | show tablecounts | List the tables in the schema with a rowcount for each table |
567               | show schema      | Lists the columns in each table in the schema                |
568               +------------------+--------------------------------------------------------------+
569
570               DBI:Oracle:IFLDEV>> more export.sql
571               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show drivers','Displays a list of DBI drivers');
572               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show datasources','Displays a list of available data sources for a driver');
573               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tables','List the tables in the schema');
574               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show tablecounts','List the tables in the schema with a rowcount for each table');
575               INSERT into $table (COMMAND,DESCRIPTION) VALUES ('show schema','Lists the columns in each table in the schema');
576
577               DBI:Oracle:IFLDEV>> no log
578               Stopped logging queries
579
580       You can then replace $table with the table name you want the INSERT
581       stataments to be issued against:
582
583               unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql
584
585   Loading data
586       Loading a tab-delimited text file is simple:
587
588               load export.txt into mytable
589
590       Here's an example:
591
592               DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))
593               CREATE table commands: 0 rows affected
594
595               DBI:SQLite:dbname=test.db> load commands.tsv into commands
596               Loaded 12 rows into commands from commands.tsv
597
598       As with "dump" you can change the delimiter character:
599
600               load export.csv into mytable delimited by ,
601
602       You can also specify character set translations:
603
604               load export.txt into mytable from CP1252 to UTF-8
605
606       if your database engine cannot do the character set conversions itself.
607       See Locale::Recode for a list of character set names.
608
609   Manipulating the command history
610       You can dump out the history to a file:
611
612               save history to history.txt
613
614       You can also load in a set of commands into the history:
615
616               load history from handy_queries.sql
617
618       This can be useful in conjunction with "log commands".  You can clear
619       the history at any time with:
620
621               clear history
622
623       and display it with:
624
625               show history
626
627   Running batches of commands
628       You can execute a sequence of sqlsh commands from a file:
629
630               > execute commands.sqlsh
631
632       that might have been generated by "save history" or "log commands".
633       You can also pipe commands into sqlsh on STDIN if you call it with the
634       "-i" switch:
635
636               unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh
637

VERSION

639       v1.17
640

AUTHOR

642           John Alden
643           Miguel Gualdron
644
645
646
647perl v5.30.1                      2020-01-30             SQL::Shell::Manual(3)
Impressum