Getting Started

This page covers everything you need to get jsqsh connected to a database and querying!

Table of Contents

• Entering setup
• Driver setup
• Connection setup
• Connecting
• Querying
• Advanced setup

Entering setup

The first time you start jsqsh you will be presented with the setup wizard screen.

If you want to re-enter this wizard later, you can do so either by lanching jsqsh with:

$ jsqsh --setup

or at the jsqsh prompt by doing:

1> \setup

To work with jsqsh, there are two basic steps you need to do first, go into the Driver setup to ensure that the JDBC driver that you want to use is defined and can be loaded, then enter Connection setup to define a connection using the driver.

Driver setup

The world of JDBC drivers can be a bit difficult if you aren't used to them (well, and frankly, sometimes even if you are). To assist with this jsqsh has a built-in database of JDBC drivers for a variety of different database vendors, and tries to make the configuration as easy as possible.

Before you can use a particular JDBC driver, you should enter the Driver Management Wizard by choosing (D) on the setup wizard. This will present you with a screen like:

If your driver is shown with an asterisk (*) next to it then the driver is available and you have nothing to do! JSqsh locates JDBC drivers according to the following rules:

1. If you have the driver in the CLASSPATH environment variable
2. By looking in the directory share/jsqsh (if you are using jsqsh from a .tar.gz or .zip package)
3. By looking in the directory /usr/share/jsqsh
4. By looking at the classpath that is associated with the JDBC driver (see Editing). This is the recommended way in which to make a JDBC driver available.

Editing driver settings

The main time that you will want to edit a JDBC driver is probably to set the classpath for it (the path to the Java jar file that contains the driver), however this is also a good place to set defaults for the driver that you want to share across all connections. To edit a given driver, enter the number that is shown next to the driver in the Driver wizard. In this example, I will edit the db2 driver:

You'll note under Status that the driver cannot be located. To fix this, type 6 to edit the Classpath for the driver, which will present:

A classpath may contain a list of one or more of the following, each separated by a colon (":") character (or a semicolon if you are running on windows)

• A path to a specific jar file, like /usr/share/java/mysql-connector-java.jar
• A path to a directory containing one or more jar files, like: /usr/share/java. All jar files contained in that directory will be automatically picked up
• Any of these entries may refer to environment variables as well, like ${MYSQL_HOME}/java/mysql-connector-java.jar

In my particular case, I know that the DB2 JDBC driver lives in ${DB2_HOME}/sqllib/java/db2jcc4.jar, which results in:

Note that the driver status now indicates Available.

Connection setup

The Connection Setup Wizard is used to define a connection to a data source using a particular JDBC driver.

The first time you enter the connection setup wizard, it will appear like so, because you have no connections defined yet:

Here, choose which JDBC driver you wish to use. If your chosen driver does not have an asterisk next to it or does not appear in this list, then go back to the Driver setup wizard.

For this example, I am going to define a connection to my DB2 instance by choosing driver #2 (the "db2") driver, which then takes me to this screen:

The Connection URL Variables section lists variables that are defined in the JDBC URL, along with the following settings:

• user - This is the default username to use to connect to the data source. JSqsh will default this to your username. If you leave this field blank, then jsqsh will prompt you each time you connect for the username.
• password - This is the password to use to connect to the data source. This password is encrypted in the jsqsh connection configuration file $HOME/.jsqsh/connections.xml, however it is generally safest to leave this field blank which will cause jsqsh to prompt you for the password to connect.
• Autoconnect - If set to true jsqsh will automatically connect using this connection when launched (unless the name of another connection has been explicitly provided). Only one connection may have Autoconnect enabled, turning it on for one connection will turn it off on any other connection that previously had it enabled.

The JDBC Driver Properties section allows you to manage JDBC connection properties. Almost all JDBC drivers have a large number of configuration properties, that control anything from security settings to language and locale information. This section allows you to set default values for these properties for your driver. Note that when adding a new property (the "P" option, above), jsqsh will ask the JDBC driver for a list of properties that are available, but not all drivers publish any or all of their configuration properties, so you may need to find a reference guide for your JDBC driver to know what properties can be set.

Upon choosing (S)ave the wizard will prompt you to name the connection. After filling everything in and choosing the name "db2", you are presented with the following:

Once your connection has been defined, you can choose (Q)uit to go back to the jsqsh prompt.

Connecting

Let's say that you defined a new connection called mydb using the directions above, you can use your connection when starting jsqsh with:

$ jsqsh mydb
JSqsh Release 2.1.0
Type \help for available help topics. Using JLine.
[localhost][gray] 1>

or if you are already at the jsqsh prompt, you can do:

1> \connect mydb
[localhost][gray] 1>

to quit out of jsqsh just do:

1> quit

Querying

Running a query in jsqsh is easy, there are two different wants to do it, using the statement terminator at the end of a line, or using the "go" command.

The statement terminator

Every time you hit enter, jsqsh checks the line that you just typed to see if it ends with the statement terminator character, which is a semicolon (";") by default, if it is then it assumes you want to execute the current statement:

[localhost][gray] 1> select 'hello world' from sysibm.dual;
+-------------+
| 1           |
+-------------+
| hello world |
+-------------+
1 row in results(first row: 0.4s; total: 0.4s)

JSqsh also takes care to avoid trying to execute the statement you are typing if the terminator character occurrs within a quoted string or within a comment:

[localhost][gray] 1> -- This line will not execute;
[localhost][gray] 2> select 'This line will not execute;
[localhost][gray] 3>   either' from sysibm.dual;
+-----------------------------+
| 1                           |
+-----------------------------+
| This line will not execute; |
|   either                    |
+-----------------------------+
1 row in results(first row: 0.0s; total: 0.0s)

In addition, for the case of a semicolon terminator, jsqsh even contains some logic to attempt to determine of a semicolon contained at the end of a line is part of the SQL itself, or is to be used to execute the current statement. For example, in SQL PL blocks, a semicolon can be used to indicate the end of a statement, but not the end of the SQL itself. In this case, jsqsh will attempt to be "smart" and figure out where the real end of the statement is:

[localhost][gray] 1> CREATE PROCEDURE P1 (IN V1 INT, OUT V2 INT)
[localhost][gray] 2>   LANGUAGE SQL 
[localhost][gray] 3>   BEGIN 
[localhost][gray] 4>      -- Note the trailing semicolon. This will not cause jsqsh to execute
[localhost][gray] 5>      SET V2 = V1 * 2;
[localhost][gray] 6>      -- But the one after the END will
[localhost][gray] 7>   END;
0 rows affected (total: 0.3s)

IMPORTANT NOTE: This SQL parsing logic is crude, and it can often get things wrong, which can be seen if jsqsh attempts to execute a block before you are done typing it in, or it ignores the semicolon that you intended to actually run the procedure. There are two ways you can deal with this problem.

To avoid the ambiguity of the semicolon as a command terminator, jsqsh allows the terminator character to be changed via the \set command:

[localhost][gray] 1> \set terminator=@
[localhost][gray] 1> select 'hello world' from sysibm.dual@
+-------------+
| 1           |
+-------------+
| hello world |
+-------------+
1 row in results(first row: 0.0s; total: 0.0s)

The "go" command

The statement terminator is really just a shortcut mechanism for executing a command in jsqsh called "go".

[localhost][gray] 1> select 'hello world' from sysibm.dual
[localhost][gray] 1> go
+-------------+
| 1           |
+-------------+
| hello world |
+-------------+
1 row in results(first row: 0.0s; total: 0.0s)

The advantage of using the go command over the statement terminator is that go does not suffer from the ambiguity problem that the statement terminator does, and go provides a number of additional objects that allows you to control many aspects of how the statement is executed. For example, if you want to change the display style:

[localhost][gray] 1> select 'hello world' from sysibm.dual
[localhost][gray] 2> go -m isql
 1          
 -----------
 hello world

1 row in results(first row: 0.0s; total: 0.0s)

For more details on the options that can be provided to the go command, run:

[localhost][gray] 1> \help go

Advanced Setup

This section covers advanced topics about setting up jsqsh.

Manual Setup

In all of the previous sections (and even below) the setup process is shown to be using the jsqsh setup wizards, however it should be noted that all these wizards are doing is editing certain configuration files that reside in your home directory, namely:

• Driver configuration - $HOME/.jsqsh/drivers.xml
• Connection configuration - $HOME/.jsqsh/connections.xml

Advanced Driver Setup

This section covers details behind either editing or creating new JDBC driver definitions.

When adding or editing a JDBC driver, the following configuration options are available on the main driver editor screen:

• Name - Change the name of the driver. Note that changing the name of a driver that existing connection definitions are using, will stop those connections from working.
• Description - Human readable description of the driver
• Class - The name of the java class that provides the JDBC driver
• URL - The JDBC URL that is to be used to utilize the driver. This URL may reference a number of variables that will be automatically set when attempting to connect using the driver. These variables are:
  • ${server} The name of the server the user provided at connect time
  • ${port} The port for the server that the user provided at connect time
  • ${db} The name of the database provided by the user
  • ${SID} The instance ID for the database (used usually by Oracle)
  • ${domain} The domain to use for authenticaion (used usually by SQL Server)
  • The above variables are inherent to jsqsh. That is, the ${server} variable is automatically set if a server name is provided with the -S (or --server) command line argument when starting jsqsh, however each driver may also define additional variables that can be set when defining a connection with the Connection Manager Wizard.
• SQL Parser - A driver may also specify the "flavor" of SQL that it understands. This information is used by jsqsh to help with tab completion and also to help it determine if, say, a semicolon is to be used to execute a statement or is just be used to terminate a line in a block of statements. Note that this is not a full SQL parser, but is just a crude set of rules that it uses to help with these activites.
• Classpath - This is covered in depth under Editing.
• Name normalizer - For jsqsh commands that look up database metadata, such as \describe (which describes a table), jsqsh is using a standard JDBC call to do the metadata lookup. For some drivers, however, the name of the object must exactly match how the object is stored in the database catalogs. For example, if the database stores the name as MYTABLE, you ran \describe mytable you would get no results. For drivers such as these, Name normalizer may be used to let jsqsh know how the database platforms stores its object names, in UPPER CASE or LOWER CASE.
• Schema query - The jsqsh metadata lookup functions, such as \show tables will attempt to be sensitive to the schema context of the current session, so only show objects in the current schema. JSqsh does this by asking the JDBC driver what the current schema context is, however this feature was added only in very recent versions of JDBC, so many drivers do not support this. For such drivers, a query may be provided

The URL Variable Defaults section lists variables that are referenced in the URL of the JDBC driver and allows you to specify defaults that will be used if the user doesn't specify any.

JDBC URL magic

A typical JDBC URL is defined in a JDBC driver referring to a number of variables that will be replaced at connect time based upon the connection definition that you have defined, for example:

jdbc:db2://${server}:${port}/${db}

will replace the server, port, and db variables with the values provided by the connection. However, sometimes, you may want to define a URL with properties that the user may not necessarily supply. For example, maybe you want to allow for a database to be provided, but only optionally, and if the user doesn't provide it, you want them to connect to the default database.

To allow for this, jsqsh does the variable processing on a JDBC URL using a tool called Velocity. Velocity doesn't just do simple variable expansion but is a full "macro" language, meaning that it allows for conditional expressions and a whole variety of other features. Thus, using the velocity "if" syntax, you can default URL's with components that only exist if the user provided a value, such as:

jdbc:sybase:Tds:${server}:${port}#if($db)/${db}#end

In this case, if the user provided a database, the URL would appear as:

jdbc:sybase:Tds:myhost.mydomain.com:4100/mydb

however, if they did not provide a database, it would be:

jdbc:sybase:Tds:myhost.mydomain.com:4100

Driver advanced options

The JDBC driver Advanced Options screen:

Allows you to set a number of additional JDBC driver settings:

• Session variables to set upon connect - This section allows you to set jsqsh variables within the session once a connection has been established. The typical use case for this session is to set one of jsqsh's special configuration variables (run \help vars for a list). For example, if you wanted to ensure that the database connection was always created with autocommit turned off, you could set the autocommit variable to false for the driver.

• JDBC driver connection properties - This section allows you to set driver configuration properties. Almost all JDBC drivers have a large number of configuration properties, that control anything from security settings to language and locale information. This section allows you to set default values for these properties for your driver. Note that when adding a new property (the "P" option, above), jsqsh will ask the JDBC driver for a list of properties that are available, but not all drivers publish any or all of their configuration properties, so you may need to find a reference guide for your JDBC driver to know what properties can be set.