Features
This page provides an overview of the major features available in jsqsh. It is not a comprehensive list, though, so you are highly encouraged to spend some time reading through the builtin documentation that comes with jsqsh via the \help command.
Table of contents
JSqsh supports several major line editing via the JLine library (it can utilize the Java-Readline library if you prefer). This library provides:
- The ability to scroll forward and back through and edit previously entered lines
- Built-in support for both Emacs and Vi key bindings (see readline)
- Configurable key bindings through the readline $HOME/.inputrc configuration file
- Object name completion while entering SQL
Along with line-editing functionality, jsqsh allows you to recall and edit entire queries that you have previously executed. The \history command displays the list 50 queries (configurable) that you have executed.
1> \history
(1) select count(*) as 'Count' from $t
(2) select 1
(3) select top 3 * from sysdatases
(4) select top 3 * fromsysdatabases
(5) select top 3 * from sysdatabases
(6) use mydb
(7) select * from STATISTIC_SAMPLE_TYPE
where SAMPLE_DATE >= '1/1/2012'
(8) exec sp_myproc @x = 10
(9) sp_who
(10) select * from syscolumns
You can then recall a previously executed statement using the !n where n is the history number of the command you wish to edit. You can edit the current you currently working on using your favorite editor using the vi or emacs command (see \buf-edit) which will launch the editor defined in the $EDITOR environment variable to provide full screen query editing.
1> !7
1> select * from STATISTICS_SAMPLE_TYPE
2> where SAMPLE_DATE >= '1/1/2012'
3> vi
Alternatively you can combine both steps in a single command:
1> vi !7
...edit in vi editor...
Most command line database tools provided by database vendors provide relatively limited functionality--allow you to type a SQL statement, execute it, and display the results. Along with this functionality. jsqsh provides a significant number of additional commands. Typically commands in jsqsh are prefixed with a '' to avoid collision with any SQL keywords. For example:
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 |
| \call | Call a prepared statement |
| \connect | Establishes a connection to a database. |
...
| \tables | Displays tables, views, synonyms, etc. |
| \tree | Shows a tree view of all the tables |
| \unset | Removes a jsqsh variable. |
| \wrap | Used internally for testing of jsqsh's word-wrapping facility. |
+-------------+----------------------------------------------------------------------+
JSqsh provides extensive built-in help for every command. To find out the command line arguments a command accepts, you can provide the --help (see options) flag:
1> \connect --help
Use: \connect [options] [named-connection]
-a, --add=val Save the command line as a named connection
-D, --database=db Database (catalog) context to use upon connection
-w, --domain=domain Windows domain to be used for authentication
-d, --driver=driver Name of jsqsh driver to be used for connection
-g, --gui Send all command output to a graphical window
-h, --help Display help for command line usage
-c, --jdbc-class=driver JDBC driver class to utilize
-u, --jdbc-url=url JDBC url to use for connection
-l, --list List all defined connections
-n, --new-session Create a new session for the connection
-P, --password=pass Password utilized for connection
-p, --port=port Listen port for the server to connect to
-O, --prop=name=val Set a driver connection property. Can be used more than once
-r, --remove Remove a connection definition
-S, --server=server Name of the database server to connect to
-x, --show-password Displays connect password in clear-text
-s, --sid=SID Instance id (e.g. Oracle SID) to utilize
-x, --update Update a named connection with the current settings
-U, --user=user Username utilized for connection
Additionally, every command has detailed documentation that can be viewed using the \help command with the name of the command you want help with:
1> \help connect
SYNOPSIS
\connect [options] [connection-name]
DESCRIPTION
The \connect command attempts to establish a JDBC connection to a database
server. If a connection is successfully established then the connection
becomes the active connection for the current session. If the session already
had a connection established, then the existing connection is closed and
replaced with the new connection.
CONNECTION OPTIONS
The following options may be used to specify information required to establish
a database connection (or whatever your chosen JDBC driver considers a
connection).
...
and online reference of all commands is available in the Command Reference.
Aliases are a way of creating alternative names for existing jsqsh commands. Aliases may even provide additional command line arguments for those commands. For example, if you wanted to use '?' instead of the \help command, you could use the \alias command like so:
1> \alias ?='\help'
1> ? alias
SYNOPSIS
\alias [-G] [name=text]
DESCRIPTION
An alias in jsqsh is slightly different (and potentially more dangerous
if misused) than the UNIX shell equivalent. In jsqsh, an alias defines a
piece of text that, when encountered in a user's input, is replaced with
another piece of text.
...
How many times have you watched a result set disappear from your screen because you didn't parse the screen fast enough or ran out of history in your terminal? Well, no more. Now, any command available in sqsh may be redirected to/from a file or pipelined to another process. For example:
1> SELECT * FROM sysobjects
2> go | grep test | more
You may also redirect output to files using standard shell redirection nomenclature:
1> SELECT * FROM sysobjects
2> go > sysobjects.dat 2>&1
see redirection for more details.
JSqsh supports variables in much the same way as a traditional shell. They can be used both as values that you may refer to within your SQL, and they can be used to view or configure the behavior of jsqsh.
Many aspects of jsqsh's behavior can be configured using variables. The list of such variables can be displayed using \help vars (online documentation is available in the Variables Reference:
1> \help vars
Available configuration variables. Use "\help <variable>" to display detailed help for a given variable
+---------------------+-----------------------------------------------------------+
| Variable | Description |
+---------------------+-----------------------------------------------------------+
| SID | Oracle instance to which connection is established |
| autocommit | Contains the connection's current auto-commit status |
| binstr_fmt | Changes the manner in which binary values are displayed |
| build_date | The jsqsh build date |
| classpath | Defines JDBC driver locations |
| connected | Indicates whether or not the current session is connected |
| database | Contains the connection's current database |
| dflt_autocommit | Controls the autocommit setting for new connections |
| dflt_database | Controls the default database for new connections |
| domain | Windows domain used to establish connection |
...
So, for examples, to change the format that jsqsh uses to display TIMESTAMP data types you can change the $fmt_datetime variable using the \set command:
1> select current_timestamp from dual;
+-------------------------+
| current_timestamp |
+-------------------------+
| 2012-12-09 15:59:09.000 |
+-------------------------+
1 row in results(first row: 0.0s; total: 0.0s)
1> \set fmt_datetime='MM/dd/yyyy HH:mm:ss'
1> select current_timestamp from dual;
+---------------------+
| current_timestamp |
+---------------------+
| 12/09/2012 15:59:56 |
+---------------------+
1 row in results(first row: 0.2s; total: 0.2s)
To display the value of a variable, you can use the \echo command with the variable name. Note that variables may be used within any jsqsh command, in the same way that they could be used on a UNIX shell command line:
1> \echo $fmt_datetime
MM/dd/yyyy HH:mm:ss
The \set command with no argument will display all configuration variables, and every configuration value has built-in help for it as well:
1> \help fmt_datetime
VARIABLE
fmt_datetime - Controls the representation of datatime values.
DESCRIPTION
The ${fmt_datetime} variable contains a format string that is utilized
when display columns of type DATETIME. The format string follows java
SimpleDateFormat conventions, which are documented here:
http://java.sun.com/j2se/1.5.0/docs/api/java/text/SimpleDateFormat.html
The default is 'MMM dd yyyy HH:mm:ss'.
SEE ALSO
fmt_date, fmt_time
If the $expand variable is set to true (it defaults to false) then queries that contain variable references will have them expanded prior to execution. For example:
1> \set expand=true
1> \set t="sysobjects"
1> select count(*) as 'Count' from $t;
+-------+
| Count |
+-------+
| 82 |
+-------+
Ever get tired of wading through isql's messy output when dealing with very wide result sets? JSqsh currently supports multiple separate display styles: perfect, pretty, graphical, json, isql, csv, vertical, tree and discard. The display style is switchable at any time while running via the style variable or by the -m flag to the \go command.
The perfect is the default display style samples a number of rows of data (configurable) before display, and attempts to pack the columns as tightly together as possible to fit in the least screen space:
:::text
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m perfect
+-------+-------+---------------------+
| DB_ID | OWNER | TBL_NAME |
+-------+-------+---------------------+
| 1 | gray | struct_simple1 |
| 1 | gray | struct_nested3 |
| 81 | gray | dist_inventory_fact |
+-------+-------+---------------------+
The pretty style is just like perfect except rows are not analyzed before display, so the columns will be roughly the width of the datatype that the columns are defined for. This mode does try to be nice and word wrap long strings so that the result set stands a better chance of fitting on the screen (see maxlen)
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m pretty
+--------------------+-----------------------------+---------------------------------+
| DB_ID | OWNER | TBL_NAME |
+--------------------+-----------------------------+---------------------------------+
| 1 | gray | struct_simple1 |
| 1 | gray | struct_nested3 |
| 81 | gray | dist_inventory_fact |
+--------------------+-----------------------------+---------------------------------+
Tight is identical to pretty (meaning it attempts to minimize the space required to display the results), except that it drops out outside border for a slightly tighter display:
[null][me] 1> select * from x
[null][me] 2> go -m tight
STATE | DAYOFWEEK | SALES
------+-----------+---------
NJ | Mon | 14.20000
NJ | Tue | 11.40000
NJ | Wed | 19.30000
CA | Mon | 4.10000
CA | Tue | 8.30000
CA | Wed | 44.20000
NJ | Thu | 17.10000
AR | Tue | 4.30000
Simple is the same as pretty except that it drops the outer border as well:
[null][me] 1> select * from x
[null][me] 2> go -m simple
STATE | DAYOFWEEK | SALES
------+------------+-----------------------
NJ | Mon | 14.20000
NJ | Tue | 11.40000
NJ | Wed | 19.30000
CA | Mon | 4.10000
CA | Tue | 8.30000
CA | Wed | 44.20000
NJ | Thu | 17.10000
AR | Tue | 4.30000
The graphical display style displays each result set as a popup graphical table that supports sorting by clicking on the column headers of the table:
1> select * from sysobjects
2> go -m graphical
results in a window that looks like:
In addition to the graphical table format shown here, every jsqsh command takes an option argument of -g or --gui (see options) that causes the text of that command to be displayed in a graphical window. For example, passing the flag to the \help command, like so:
1> \help --gui vars
results in:
The json display style, presents rows as an array of JSON records.
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m json
[
{
"DB_ID": 1,
"OWNER": "gray",
"TBL_NAME": "struct_simple1"
},
{
"DB_ID": 1,
"OWNER": "gray",
"TBL_NAME": "struct_nested3"
},
{
"DB_ID": 81,
"OWNER": "gray",
"TBL_NAME": "dist_inventory_fact"
}
]
The isql display style is intended to mimic the output of Sybase or Microsoft SQL Server 'isql' commands:
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m isql
DB_ID
OWNER
TBL_NAME
----------------------
-----------------------------------------------
-----------------------------------------------
1
gray
struct_simple1
1
gray
struct_nested3
81
gray
dist_inventory_fact
The csv output format presents ye-olde comma delimited values:
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m csv
DB_ID,OWNER,TBL_NAME
1,gray,struct_simple1
1,gray,struct_nested3
81,gray,dist_inventory_fact
The vertical display style presents a simple set of name/value pairs:
1> select DB_ID, OWNER, TBL_NAME from TBLS LIMIT 3
2> go -m vertical
DB_ID: 1
OWNER: gray
TBL_NAME: struct_simple1
DB_ID: 1
OWNER: gray
TBL_NAME: struct_nested3
DB_ID: 81
OWNER: gray
TBL_NAME: dist_inventory_fact
The tree view is a graphical view that presents ordered data in a tree view so that you can drill down and sift through available information:
1> select DB_ID, OWNER, TBL_NAME from TBLS order by DB_ID, OWNER, TBL_NAME
2> go -m tree
Presents:
The discard display style displays no output, just the final row count and timing information about the query. This display style is intended to performance testing when you wish to exclude (as much as possible) the cost of displaying the output in your final query timing.
With jsqsh 2.3 and later you may ask jsqsh to produce a crosstab of your query using the --crosstab argument to the \go command. For example, given:
[null][me] 1> select * from x
[null][me] 2> go
+-------+-----------+----------+
| STATE | DAYOFWEEK | SALES |
+-------+-----------+----------+
| NJ | Mon | 14.20000 |
| NJ | Tue | 11.40000 |
| NJ | Wed | 19.30000 |
| CA | Mon | 4.10000 |
| CA | Tue | 8.30000 |
| CA | Wed | 44.20000 |
| NJ | Thu | 17.10000 |
| AR | Tue | 4.30000 |
+-------+-----------+----------+
A crosstab of day of week by state can be produced like so:
[null][me] 1> select * from x
[null][me] 2> go --crosstab dayofweek,state,sales
+-----------+---------+----------+----------+
| DAYOFWEEK | AR | CA | NJ |
+-----------+---------+----------+----------+
| Mon | [NULL] | 4.10000 | 14.20000 |
| Tue | 4.30000 | 8.30000 | 11.40000 |
| Wed | [NULL] | 44.20000 | 19.30000 |
| Thu | [NULL] | [NULL] | 17.10000 |
+-----------+---------+----------+----------+
For more details, see the \go command.
Beyond just running queries against the database server, jsqsh has a number of commands to automatically generate SQL statements for you, to save a little typing.
The \create command can be used to generate a table definition:
1> \create sysobjects
CREATE TABLE sysobjects
(
name sysname(128) NOT NULL,
id int NOT NULL,
xtype char(2) NOT NULL,
uid smallint NOT NULL,
info smallint NOT NULL,
status int NOT NULL,
base_schema_ver int NOT NULL,
replinfo int NOT NULL,
parent_obj int NOT NULL,
crdate datetime NOT NULL,
ftcatid smallint NOT NULL,
schema_ver int NOT NULL,
stats_schema_ver int NOT NULL,
type char(2) NULL,
userstat smallint NULL,
sysstat smallint NULL,
indexdel smallint NULL,
refdate datetime NULL,
version int NULL,
deltrig int NULL,
instrig int NULL,
updtrig int NULL,
seltrig int NULL,
category int NULL,
cache smallint NULL
)
Currently the \create command is limited to basic column definitions. Future versions of jsqsh will add support for RI related to the table as well (and possibly check constraints).
The \insert command can be used to generate INSERT statements for populating tables:
1> select * from pubs2..authors
2> \insert --batch-size=2 authors
INSERT INTO authors (au_id, au_lname, au_fname, ...) VALUES (...
INSERT INTO authors (au_id, au_lname, au_fname, ...) VALUES (...
go
INSERT INTO authors (au_id, au_lname, au_fname, ...) VALUES (...
INSERT INTO authors (au_id, au_lname, au_fname, ...) VALUES (...
go
In addition, using the --target-session argument to \insert the INSERT statements can directly executed against another connection being managed by jsqsh to provide a basic server-to-server table copy function.
The \select command can be used to generate SELECT statements based upon the definition of tables in the database. The command can generate WHERE clauses to join multiple tables based upon the RI between the tables or, optionally , by performing a "natural" join and joining columns of the same name and datatype.
1> \select --print titles titleauthor
SELECT a.title_id,
a.title,
a.type,
a.pub_id,
a.price,
a.advance,
a.num_sold,
a.notes,
a.pubdate,
a.contract,
b.au_id,
b.title_id,
b.au_ord,
b.royaltyper
FROM titles a, titleauthor b
WHERE a.title_id = b.title_id
Below is just a general list of other interesting features:
- Multiple active sessions You may have multiple connections open to multiple different servers simultaneously and easily switch between them. The output of one session can even be redirected into another session (see \session).
- Name connections You can name connections, so that if you ever need to reconnect to a given server you can refer to the server as, say, "testdb" rather than providing all of the necessary connection information
- Tab completion The tab key will attempt to complete the object name you are currently typing based upon the context of your SQL statement
- Batch SQL The \call command allows you to prepare a statement containing "?" markers and execute it repeatedly utilizing values from a delimited file.
- Stored procedure with return parameters Jsqsh has special handling for calling stored procedures with output parameters, see storedprocs for details.
- Much more! Please, please please. I spent a lot of time writing documentation. Use the \help command!!