1SQL::Shell::Manual(3) User Contributed Perl DocumentationSQL::Shell::Manual(3)
2
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 Connecting
18 Either set a DSN in the environment as DBI_DSN, supply with the -d
19 option or use the connect command:
20 unixbox% sqlsh
22 unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger
23 You can also connect from inside sqlsh:
25 unixbox% sqlsh
27 > connect DBI:Oracle:IFLDEV scott tiger
28 DBI:Oracle:IFLDEV> show $dbh Name
29 +--------+
30 | Name |
31 +--------+
32 | IFLDEV |
33 +--------+
34 and disconnect:
36 DBI:Oracle:IFLDEV> disconnect
38 > show $dbh Name
39 Not connected to database.
40 If you don't supply a password, sqlsh will prompt you:
42 unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott
44 Enter password for scott:
45 You can specify a blank password by passing -p:
47 unixbox% sqlsh -d DBI:Oracle:IFLDEV -u guest -p
49 From within sqlsh you can get a list of DBI drivers:
51 unixbox% sqlsh
53 > show drivers
54 CSV
56 DBM
57 ExampleP
58 Excel
59 File
60 Multiplex
61 Oracle
62 Proxy
63 SQLite
64 Sponge
65 mysql
66 and a list of possible data sources for a driver:
68 unixbox% sqlsh
70 > show datasources Oracle
71 dbi:Oracle:GISCPS
73 dbi:Oracle:IFL1
74 dbi:Oracle:IFLDEV
75 dbi:Oracle:IFLTEST
76 Common DBI DSNs include:
78 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 Exploring the schema
87 show tables
88 This lists the tables with a rowcount for each:
90 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 For some database drivers this may include some system tables.
101 desc
103 Lists the columns in a table:
105 DBI:Oracle:IFLDEV> desc commands
107 +-------------+----------------+------+
108 | Field | Type | Null |
109 +-------------+----------------+------+
110 | COMMAND | VARCHAR2(200) | YES |
111 | DESCRIPTION | VARCHAR2(1020) | YES |
112 +-------------+----------------+------+
113 show schema
115 Lists the columns in a table, for each table in the schema:
117 DBI:Oracle:IFLDEV> show schema
119 schema dump
121 COMMANDS:
122 +-------------+----------------+------+
123 | Field | Type | Null |
124 +-------------+----------------+------+
125 | COMMAND | VARCHAR2(200) | YES |
126 | DESCRIPTION | VARCHAR2(1020) | YES |
127 +-------------+----------------+------+
128 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 BLOB values
148 You can control the amount of BLOB data fetched by setting the
150 "longreadlen" parameter.
151 DBI:Oracle:IFLDEV> set longreadlen 4096
153 LongReadLen set to '4096'
154 DBI:Oracle:IFLDEV> show $dbh LongReadLen
156 +-------------+
157 | LongReadLen |
158 +-------------+
159 | 4096 |
160 +-------------+
161 Note that the C<longtruncok> parameter should also be set (it is by default):
163 DBI:Oracle:IFLDEV> show $dbh LongTruncOk
165 +-------------+
166 | LongTruncOk |
167 +-------------+
168 | 1 |
169 +-------------+
170 Values containing non-word characters
172 Suppose we have values in our database which contain whitespace
174 characters (e.g. tabs):
175 DBI:Oracle:IFLDEV> set enter-whitespace on
177 Whitespace may be entered as \n, \r and \t
178 DBI:Oracle:IFLDEV> insert into commands(command,description) values('test', 'one\ttwo')
180 INSERT commands: 1 rows affected
181 When we query the table we see these as literal values:
183 DBI:Oracle:IFLDEV> select * from commands
185 +---------+-------------+
186 | COMMAND | DESCRIPTION |
187 +---------+-------------+
188 | test | one two |
189 +---------+-------------+
190 We can instead chose to display them escaped:
192 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 Alternatively we can use uri-escaping:
202 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 Entering multi-line statements
212 To enable multiline mode:
214 DBI:Oracle:IFLDEV> set multiline on
216 You can then build up statements over multiple lines, ending with a
218 semicolon, e.g.:
219 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 To disable multiline mode, remember you need to end the statement in a
232 semicolon:
233 DBI:Oracle:IFLDEV> set multiline off;
235 Altering the display mode
237 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 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 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 Altering the database
258 By default transactions are not automatically committed so you must
259 explicitly commit them:
260 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 DBI:Oracle:IFLDEV> commit
265 and you can roll back mistakes:
267 DBI:Oracle:IFLDEV> delete from commands
269 DELETE commands: 11 rows affected
270 DBI:Oracle:IFLDEV> rollback
272 DBI:Oracle:IFLDEV> select count(*) from commands
273 +----------+
274 | COUNT(*) |
275 +----------+
276 | 11 |
277 +----------+
278 If you prefer to live dangerously you can switch autocommit on:
280 set autocommit on
282 insert ...
283 update ...
284 Clearing the database
286 The "wipe tables" command can be used to remove all the data each of
288 the tables in the database:
289 DBI:Oracle:IFLDEV> wipe tables
291 Wipe all data from:
292 COMMANDS
294 Are you sure you want to do this? (type 'yes' if you are) yes
296 Wiped all data in database
298 It prompts you to confirm before anihilating your database.
300 Dumping delimited data
302 "dump" can either be used to dump an entire table:
303 dump mytable into export.txt
305 or the rowset resulting from a query:
307 dump select type, count(*) from mytable group by type into histogram.txt delimited by :
309 An example:
311 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 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 You can also dump all the tables in a database into a directory:
331 dump all tables into dumpdir/
333 Logging
335 You can chose to log commands:
336 log commands logfile.txt
338 or query results:
340 log queries dumpfile.txt
342 or both:
344 log all history.log
346 Exporting data as XML
348 DBI:Oracle:IFLDEV> set log-mode xml
349 DBI:Oracle:IFLDEV> log queries export.xml
351 Logging queries to export.xml
352 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 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 DBI:Oracle:IFLDEV>> no log
384 Stopped logging queries
385 Exporting data as SQL
387 DBI:Oracle:IFLDEV> set log-mode sql
388 DBI:Oracle:IFLDEV> log queries export.sql
390 Logging queries to export.sql
391 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 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 DBI:Oracle:IFLDEV>> no log
409 Stopped logging queries
410 You can then replace $table with the table name you want the INSERT
412 stataments to be issued against:
413 unixbox% perl -p -i -e 's/\$table/show_commands/' export.sql
415 Loading data
417 Loading a tab-delimited text file is simple:
418 load export.txt into mytable
420 Here's an example:
422 DBI:SQLite:dbname=test.db> create table commands(command varchar(50), desc varchar(255))
424 CREATE table commands: 0 rows affected
425 DBI:SQLite:dbname=test.db> load commands.tsv into commands
427 Loaded 11 rows into commands from commands.tsv
428 As with "dump" you can change the delimiter character:
430 load export.csv into mytable delimited by ,
432 You can also specify character set translations:
434 load export.txt into mytable from CP1252 to UTF-8
436 if your database engine cannot do the character set conversions itself.
438 See Locale::Recode for a list of character set names.
439 Manipulating the command history
441 You can dump out the history to a file:
442 save history to history.txt
444 You can also load in a set of commands into the history:
446 load history from handy_queries.sql
448 This can be useful in conjunction with "log commands". You can clear
450 the history at any time with:
451 clear history
453 and display it with:
455 show history
457 Running batches of commands
459 You can execute a sequence of sqlsh commands from a file:
460 > execute commands.sqlsh
462 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 unixbox% sqlsh -d DBI:Oracle:IFLDEV -u scott -p tiger -i < commands.sqlsh
468
470 $Revision: 1.6 $ on $Date: 2006/08/02 12:01:15 $ by $Author: johna $
471
473 John Alden
474perl v5.28.0 2015-08-06 SQL::Shell::Manual(3)