Users Guide
This page is intended to cover the basic-day-to-day usage of JSqsh. It assumes that you have already performed the steps covered in Getting Started.
When you start up jsqsh it will drop you straight into its prompt (well, the first time, it will enter a setup wizard and then drop you into a prompt):
JSqsh Release 2.1.0, Copyright (C) 2007-2014, Scott C. Gray
Type \help for available help topics. Using JLine.
1>
each time you type something into the prompt, jsqsh looks at the line you just entered and does the following:
- It looks at the first word you entered on the line to see if it is a command that it recognizes, if it is then it executes the command
- It looks at the end of the line you just entered to see if it ends with the statement terminator (a semicolon by default), if it is and if you are connected to a database, then the statement is executed (this behavior is covered in detail here).
- If neither of the above is true, then jsqsh assumes that you are in the middle of typing a statement that will eventually be sent to the database server and sticks the line you just entered into its current SQL Buffer (this is covered in the next section, The SQL Buffer).
An interesting thing to note is that jsqsh doesn't understand SQL at all! In fact, frankly, it doesn't even know or care if the thing you are connected to speaks SQL or some other language. As a result, jsqsh tries to take care that most of its commands start with a leading backslash (\) to try to make sure that it doesn't try to interpret something that you intended to send to the database.
For example, jsqsh has a command called \set used to set a jsqsh configuration variable (see Variables, below), like so:
1> \set prompt='hi $lineno> '
hi 1>
many database SQL dialects have a statement called SET, so it would have been a problem if jsqsh had a command with the same name.
To see a list of commands supported by jsqsh, use \help commands:
1> \help commands
Available commands. Use "\help <command>" to display detailed help for a given command
+--------------+----------------------------------------------------------------------+
| Command | Description |
+--------------+----------------------------------------------------------------------+
| \alias | Creates an alias |
| \buf-append | Appends the contents of one SQL buffer into another |
| \buf-copy | Copies the contents of one SQL buffer into another |
| \buf-edit | Edits a SQL buffer |
| \buf-load | Loads an external file into a SQL buffer |
...
And, each command has detailed built-in help as well:
1> \help \set
SYNOPSIS
\set [[-x] [-l] name=value]
DESCRIPTION
Sets a variable or displays all variables.
OPTIONS:
If arguments are provided to \set, then the value of
all variables is displayed.
-x, --export Will cause the variable to be exported to the environment
of any processes that are spawned by jsqsh during its
execution.
-l, --local Sets the value of the variable local to the current session
only. No other sessions will see it and it will mask
similarly named variables in other session.
All jsqsh commands take one or more command line options. Besides using \help <command> to see the detailed help for a given command, you can see the options a given command accepts by passing the -h or --help option to the command:
1> \set --help
Use: \set [-x] [-l] [var=value]
-x, --export Export the variable
-g, --gui Send all command output to a graphical window
-h, --help Display help for command line usage
-l, --local Define the variable locally to this session
All command options take either a short form (e.g. -l) or a long form (e.g. --local). In addition all commands take a --gui (or -g) option, that causes a window to pop up, displaying the output of the command in a separate graphical window instead of in the current terminal window. This is useful if you want to retain the output of one command while you are working, for example to keep a list of jsqsh commands handy, you can do:
1> \help --gui commands
will pop up a window containing the output:
Sometimes the command names in jsqsh can be rather verbose (like \buf-edit) or typing the backslash character can be anger some people. To alleviate the such anxieties, jsqsh allows an alias to be established for a given command. For example:
1> alias sayonara=\quit
establishes an alias for the \quit command called "sayonara". So, now to quit out of jsqsh, you can do:
1> sayonara
shell $
An alias can also include command line options for the original command, so if you wanted to have a simple way to run a query but display the output in CSV format, you could do something like:
1> alias cgo='\go -m csv'
JSqsh comes pre-wired with a number of convenience aliases:
1> \alias
+-------+---------+------------+
| Alias | Global? | Text |
+-------+---------+------------+
| :e | N | \eval |
| :r | N | \buf-load |
| \dbs | N | \databases |
| \edit | N | \buf-edit |
| \s | N | \session |
| emacs | N | \buf-edit |
| go | N | \go |
| quit | N | \quit |
| vi | N | \buf-edit |
+-------+---------+------------+
Alias definitions are not remembered after you query out of jsqsh, so you typically would add them to your sqshrc file.
The "sh" in jsqsh is short for shell. This is because jsqsh likes to pretend that it is like a unix shell, supporting the two big features that all good shells provide: the ability to redirect the output of a command to a file, or to pipe the output of a command to another program.
For example, if you want to run a query and send its output to a file, rather than to your screen, you can do:
1> select * from sysibm.systables
2> go -m csv > systables.csv
The "go" command will execute the select select, the -m csv option to "go" will cause the output to be rendered in comma separated value format, and the > systables.csv will send all output to a file called systables.csv.
As with a unix shell, all programs send most of their output to stdout ("standard output", the "normal" place to send output), and errors go to stderr (the "error output"), so if you did the above but, say, your SQL contained syntax errors you'd see:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv > systables.csv
SQL Exception(s) Encountered:
[State: 42601][Code: -104]: An unexpected token "." was found following "select * from sysibm". Expected tokens may include: "FROM".. SQLCODE=-104, SQLSTATE=42601, DRIVER=3.67.33
[State: 56098][Code: -727]: An error occurred during implicit system action type "2". Information returned for the error includes SQLCODE "-104", SQLSTATE "42601" and message tokens ".|select * from sysibm|FROM".. SQLCODE=-727, SQLSTATE=56098, DRIVER=3.67.33
You'll note that even though the output of the command was redirected to a file, the errors were still sent to the screen. This is because only the stdout was redirected to the file. If you wanted to capture both the output and the errors to the same file, you'd do the same thing you'd do in a unix shell and use "2>&1" (which means: send stderr to stdout), like so:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv > systables.csv 2>&1
For detailed help on how such redirection works, run \help redirection.
Along with the ability to redirect the output of a command to a file, jsqsh can instead send that output off to an external program to process using a pipe ("|"). So, for example, let's say you wanted to send the results of a query to a file off on another system, you could do something like:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go -m csv | ssh [email protected] 'cat > systables.csv'
or if you are like me and cannot always be bothered with typing those pesky WHERE clauses, you can do:
[localhost][gray] 1> select select * from sysibm.systables
[localhost][gray] 2> go | grep SYSIBM
As you type text at the prompt of jsqsh, each line you type is accumulated into a SQL buffer (strictly speaking JSqsh has no idea that you are even typing SQL) until one of the following conditions occurs:
- It looks at the first word you entered on the line to see if it is a command that it recognizes, like go or \help, if it is then it executes the command (run
\help commandsto see a list of all available jsqsh commands). - It looks at the end of the line you just entered to see if it ends with the statement terminator (a semicolon by default), if it is then the statement is executed (this behavior is covered in detail here).
For example, in the following:
1> select count(*)
2> from sysibm.systables
3> go
+--------+
| 1 |
+--------+
| 236 |
+--------+
1 row in results(first row: 0.0s; total: 0.0s)
The first two lines were accumulated into the SQL buffer until the "go" command was executed. The "go" command takes whatever was in the buffer and executes it through the current database connection.
It is important to note that jsqsh tries to be pretty smart about what you are doing, and does attempt to recognize if you in the middle of a comment or a quoted string and will not recognize commands or the statement terminator while within them, for example:
1> select 'in an open string
2> go
3>
the "go" will not execute the statement because you are currently inside of a string literal, similarly,
1> select count(*) from sysibm.systables /*
2> go
3>
the "go" will not run because you are inside of a comment.
While jsqsh supports command line editing (using the arrow keys to move around the current line or to recall previous lines), sometimes you want to edit the entire query that you are in the middle of entering. For this you can use the jsqsh \buf-edit command to pull up the current buffer in an external editor:
1> CREATE PROCEDURE P1 (IN V1 INT, OUT V2 INT)
2> LANGUAGE SQL
3> BEGIN
4> SET V2 = V5 * 2; -- Oops, "V5" isn't a valid variable
5> \buf-edit
The \buf-edit command figures out which editor to launch with your SQL by:
- It looks at the environment variable called EDITOR
- It that isn't set, it looks at the environment variable VISUAL
- If that isn't set, it runs "notepad.exe" on windows, or "vi" on all other systems
The editor will then be pulled up with the contents of the current SQL buffer, like so:
At which point you can edit and even finish your statement and save and quit out of the editor, at which point you will find yourself back at the jsqsh prompt, with the current SQL Buffer reflecting the change you just made:
1> CREATE PROCEDURE P1 (IN V1 INT, OUT V2 INT)
2> LANGUAGE SQL
3> BEGIN
4> SET V2 = V1 * 2; -- All better now
5>
Because \buf-edit it is a bit of a handful to type, jsqsh comes in with two built-in aliases for the command called vi and emacs
1> select kount(*) from
2> vi
note that whether or not you use vi or emacs you are really calling \buf-edit and it is going through the steps described above to figure out which editor to launch!
Every time a statement is executed via the go command or via the statement terminator, jsqsh saves that statement away, remembering the last 50 statements executes (by default). You can view these previously executed statements using the \history command:
1> \history
(1) { call test_inout(?='scott is here') }
(2) { call test_inout(?='scott') }
(3) drop procedure test_inout
(4) create procedure test_mixed (IN i1 INT, OUT i2 DOUBLE, INOUT i3 varchar(30))
result sets 1
language sql
begin
declare c1 cursor with return to client
for select 'result set', i1, i3 from sysibm.dual;
set i2 = 34.2;
set i3 = 'fred was here';
open c1;
...
(5) { call test_mixed (5, ?, ?='blah') }
Note that for very long statements, like statement (4), above, jsqsh will only show the first 10 lines in order to keep the output of getting huge.
At any point, you can recall a previously executed statement into the current SQL buffer, using jsqsh's history recall syntax. For example, to retrieve statement #4 above, you can enter !4, like so:
1> !4
1> create procedure test_mixed (IN i1 INT, OUT i2 DOUBLE, INOUT i3 varchar(30))
2> result sets 1
3> language sql
4> begin
5> declare c1 cursor with return to client
6> for select 'result set', i1, i3 from sysibm.dual;
7>
8> set i2 = 34.2;
9> set i3 = 'fred was here';
10> open c1;
11> end
12>
JSqsh's history syntax recall syntax is:
-
!N: Recalls statement #N as listed in the output of the
\historycommand - !-N: Recalls the N'th previously executed buffer
- !..: Recalls the previously executed buffer. Additional dots may be provided to go further back
- !.: Refers to the current SQL buffer (examples of where this is useful below)
A number of jsqsh commands work with SQL buffers. The following table provides a brief summary of these commands and how they interact with buffers.
| Command | Description |
|---|---|
| go | Executes the statement in the current SQL buffer, then moves that buffer into the history list |
| \buf-append | Copies the contents of one buffer onto the end of another buffer |
| \buf-copy | Copies the contents of one buffer over the contents of another buffer |
| \buf-edit | Pulls up the current buffer in an external editor |
| \buf-load | Reads a file into a buffer |
| \history | Displays the most recently executed buffers |
| \reset | Discards the current buffer |
Many of these commands take argument that is a form of the history recall syntax. For example, to load a file into the current buffer, you can do:
1> \buf-load myquery.sql !.
1> select * from sysibm.systables
2>
or, if you wanted to copy the contents of that file to the last SQL buffer:
1> \buf-load myquery.sql !-1
1>
As usual, use \help <command> to get detailed help and examples on these commands.