Tips and Tricks - scgray/jsqsh GitHub Wiki

This page is intended to just be a dumping ground for interesting details and features of jsqsh that are probably pretty easy to miss unless you take the time to read all of the built-in help (which you should, I spent a lot of time writing it!)

Table of Contents

Configuration variables

This section covers some of the more interesting configuration variables in jsqsh.

prompt

The contents of the $prompt variable is used to construct the jsqsh prompt. This variable may, itself, contain references to jsqsh variables and Velocity macros, which are expanded immediately prior to display. For example, the default value for the jsqsh prompt is:

#if($connected==true)[$server][$user] #end$lineno>

so what does this confusing mess do?

  • The #if($connected==true)...#end is a velocity macro. It checks the jsqsh $connected variable (see \help connected for details), and if the variable has a value of true then the contents up to the #end are left in place, otherwise they are removed.
  • The $server will expand to the hostname that was used for the current connection and $user will refer to the username used for the connection (see \help server and \help user)
  • The $lineno variable always expands to be the line number you are typing into in the current SQL Buffer.

All of the above means that when you are not connected to a data source, you'll have a prompt of:

1>

but after connection you'll see:

[mydb2.mydomain.com][gray] 1> 

JSqsh has a number of additional variables that are useful to display in the prompt:

  • $database - The current working database
  • $driver - The driver you are connected with
  • $histid - The id of the current SQL buffer you are editing (useful for history recall)
  • $jdbc_url - The JDBC URL currently in use
  • $name - The logical name used for the connection
  • $port - The port on which the connection was established

When you are setting the prompt, do not forget to wrap the value in single quotes, otherwise jsqsh will expand the variables before executing the \set command, which isn't what you want:

1> \set prompt='$histid:$lineno>'
31:1> 

Also, you can set the value for the prompt in a persistent fashion by adding your set command to your $HOME/.jsqsh/sqshrc file.

timer

The $timer variable is used to control jsqsh's display of a running timer of the current query's execution. The timer doesn't appear until the current query has been running for 5 seconds or longer:

[db2inst1][gray] 1> \set timer=true
[db2inst1][gray] 1> select count(*) from really_large_table
Elapsed time: 01:31:15

Calling stored procedures

JSqsh provides special support for calling stored procedures that require output (OUT) or input/output (INOUT) parameters. Such procedures can be executed in one of two fashions:

  • Using the JDBC escape syntax
  • Using the CALL statement

These are detailed below.

JDBC escape syntax

The JDBC standard defines a canonical way of executing stored procedures in a manner that abstracts away the specifics of how procedures are invoked for a given platform. The following example executes a procedure passing one input argument and retrieving one output argument using this syntax:

1> { call my_test_proc(10, ?) };
+-------------+
| Param #1    |
+-------------+
| Fred        |
+-------------+

here, a question mark ("?") is used to denote an output parameter. After execution, the contents of that output parameter is displayed as shown.

In addition, many platforms allow you to also get a return value from the stored procedure. To do this in JDBC escape syntax do:

1> { ?= call my_test_proc(10, ?) };
+-------------+-------------+
| Return Code | Param #2    |
+-------------+-------------+
|           0 | Fred        |
+-------------+-------------+

In the case of input/output parameters, jsqsh slightly extends the JDBC standard with "?=" which indicates that the parameter is an INOUT parameter, and that the initial input value should be "", like so:

1> { ?= call double_my_inout_param_value(10, ?=321) };
+-------------+-------------+
| Return Code | Param #2    |
+-------------+-------------+
|           0 | 642         |
+-------------+-------------+

The CALL statement

The JDBC escape syntax above is awkward to work with, so jsqsh will attempt to automatically recognize when you are trying to call a procedure. It does so according to the following rules:

  1. The first keyword in your statement is CALL
  2. There are one or more parameter markers ("?") following the CALL keyword ("?" characters contained in comments or string constants are ignored)

If both of these are true, then jsqsh automatically takes your statement and attempts to make it a JDBC escape syntax statement. For example, executing the following:

1> call my_proc(10, ?);

will cause jsqsh to turn it into:

{ ?= call my_proc(10, ?) }

and execute the statement.

Output cursors

Unfortunately, there is no standard in JDBC for the handling of CURSOR output parameter types, so each JDBC driver deals with them differently. As a result, jsqsh currently only has an understanding of how to handle CURSOR output parameters for IBM DB2 and Informix, and Oracle.

When faced with an OUTPUT parameter of type cursor, jsqsh will display the results like so:

1> call my_cursor_output(?);
  
Parameter #2 CURSOR:
+---+
| 1 |
+---+
| 5 |
+---+

+-------------+
| Return Code |
+-------------+
|           0 |
+-------------+
⚠️ **GitHub.com Fallback** ⚠️