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.

Commands

When you start up jsqsh it will drop you straight into it's 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.

Getting command help

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.

Command options

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:

Redirection and pipes

The SQL Buffer

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 commands to 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.

Editing the SQL Buffer

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!

Buffer history

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 \history command
• !-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)

Variables

The .sqshrc file