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.

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)

Commands

The .sqshrc file