1SQL::Shell::Manual(3) User Contributed Perl DocumentationSQL::Shell::Manual(3)
2
3
4
6 SQL::Shell::Manual - user guide for sql shell
7
9 sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
10
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
639 v1.17
640
642 John Alden
643 Miguel Gualdron
644
645
646
647perl v5.30.0 2019-07-26 SQL::Shell::Manual(3)