# Getting started

This section provides a whirl-wind tour of setting up jsqsh, connecting to a database and running queries. Please note that this tutorial assumes that you have jsqsh properly installed and have figured out how to run it. Compiling and package jsqsh yourself is covered in the text file INSTALL included with the jsqsh source code.

## Locating JDBC drivers

Before I go into actually using jsqsh, it is important to understand how jsqsh finds the JDBC drivers that you work with. Jsqsh searches for JDBC drivers using the following methods.

### JSqsh lib directory

When jsqsh starts, it loads a bunch of support java libraries (".jar" files) from its library directory. The location of this directory can vary based upon your installation, but is typically in one of the following locations (these are relative to where the jsqsh binary is located):

• ../lib
• ../share
• ../share/jsqsh

You can identify that you have the correct directory if it has the file jsqsh.jar located in it.
Any ".jar" file that you place in that directory will automatically be picked up by jsqsh when it starts, which means that placing any JDBC driver jar in that directory will make it available.

### CLASSPATH environment variable

Additionally, any JAR file contained in your CLASSPATH environment variable will be available to jsqsh on startup. On UNIX this variable is typically set like so:

$CLASSPATH=/path/to/my/jdbc-driver.jar:$CLASSPATH

After you start jsqsh for the first time, it will create a file called sqshrc in $HOME/.jsqsh on UNIX or %USERPROFILE%\.jsqsh on Windows. In this file you may specify the location(s) in which jsqsh can fine JDBC drivers in the same fashion that the PATH environment variable is set on your platform. For example, setting the following on UNIX: \set classpath=/drivers/jdbc/:/home/gray/drivers/mysql.jar or the following on WIndows: \set classpath=C:/drivers/jdbc/;C:/home/gray/drivers/mysql.jar will make all of the drivers contained in the directory /drivers/jdbc available, as well as the specific driver /home/gray/drivers/mysql.jar. This feature was added in release 0.9.4 of jsqsh. ## First time startup The first time you run jsqsh you will receive a screen similar to this: JSqsh Release 1.0, Copyright (C) 2007, Scott C. Gray Type \help for available help topics WELCOME TO SQSH! It looks like this is the first time that you've run sqsh (or you just typed '\help welcome' at the sqsh prompt). If this is the first time you have run sqsh, you will find that you have ... 1> At this point jsqsh should have created a configuration directory in your home directory ($HOME on UNIX or %USERPROFILE% on Windows) called ".jsqsh". Through the rest of this tutorial your home directory will be referred to as $HOME and I'll stick with UNIX pathing conventions (e..g forward slashes for directory separators). ## The prompt Every line you type into the jsqsh prompt is evaluated to determine if the line is a jsqsh command or is part of a query that you might be typing. To help avoid any confusion between your databases particular dialect of SQL (or whatever it understands) and a jsqsh command, almost all jsqsh commands are prefixed with a backslash ("\"): 1> \help +----------+-----------------+-----------------------------------------------------+ | Type | Name | Description | +----------+-----------------+-----------------------------------------------------+ | Command | \alias | Creates an alias | | Command | \buf-append | Appends the contents of one SQL buffer into another | | Command | \buf-copy | Copies the contents of one SQL buffer into another | | Command | \buf-edit | Edits a SQL buffer | ... If you don't like this convention, you can create aliases for jsqsh command using the \alias command (see \help alias). ## Getting help JSqsh has an extensive amount of help built into it. At any time you have a question about a command, variable, or other topic, you can use the the \help command to get more information. # Connecting to a server Unless you specified a set of connection properties on jsqsh's command line, you will not have an initial connection established to a database server. And your prompt will be a simple little: 1> Indicating that you are not connected to any server. ## Choosing a driver To establish a connection to a server you will first want to figure out which JDBC driver that you want to use. You can do this by using the \drivers command: 1> \drivers +-----------------+-------------+----------------------------------------------+----------------------------------------------+ | Target | Name | URL | Class | +-----------------+-------------+----------------------------------------------+----------------------------------------------+ | IBM DB2 | db2 | jdbc:db2://${server}:${port} | COM.ibm.db2.jdbc.net.DB2Driver | | MS SQL Server | mssql | jdbc:microsoft:sqlserver://${server}:${port} | com.microsoft.sqlserver.jdbc.SQLServerDriver | | * MS SQL Server | mssql-jtds | jdbc:jtds:sqlserver://${server}:${port} | net.sourceforge.jtds.jdbc.Driver | | MySQL | mysql | jdbc:mysql://${server}:${port} | org.gjt.mm.mysql.Driver | | Oracle | oracle | jdbc:oracle:thin:@${server}:${port}:${SID}   | oracle.jdbc.driver.OracleDriver              |
|   PostgreSQL    | pgsql       | jdbc:postgresql://${server}:${port}          | org.postgresql.Driver                        |
|   Sybase        | sybase      | jdbc:sybase:Tds:${server}:${port}            | com.sybase.jdbc2.jdbc.SybDriver              |
| * Sybase        | sybase-jtds | jdbc:jtds:sybase://${server}:${port}         | net.sourceforge.jtds.jdbc.Driver             |
+-----------------+-------------+----------------------------------------------+----------------------------------------------+

There's two parts of this output you want to pay attention to. The first is the Name column which tells you what name you are supposed to use when connecting to your database platform and Target will have an asterisk next to those drivers for which the JDBC implementation (".jar" file) is actually available.

• If you do not have an asterisk next to the driver you want, then read the section above on locating JDBC drivers.
• If you do not see any listing at all for the database platform you are targeting, then run '\help drivers' for more information.

Jsqsh compiles compiled with built-in support for a number of drivers, but if the driver you use is not in the list shown above, you can add additional driver definitions in one of three different ways:

• Your $HOME/.jsqsh directory will contain a file called drivers.xml. This file is automatically read upon startup of jsqsh. It contains lots of comments and a detailed description of how to define additional drivers. • You can use the \drivers --load option (see \help drivers for details) to explicitly load a driver definition file. By placing this command in your$HOME/.jsqsh/sqshrc file you can have those definitions loaded at jsqsh startup time.
• You can use the ---drivers command line option to explicitly load additional driver definition files upon jsqsh startup.

### Connecting without a jsqsh driver

It is also possible to connect to a database server without having pre-defined the driver in jsqsh, but it requires that you know more details about the JDBC driver than the built-in ones. To connect without a pre-defined driver, you must specify the JDBC class and URL to the \connect command. For example, the following is equivalent to the connection we created above:

1> \connect -c net.sourceforge.jtds.jdbc.Driver -U sa jdbc:jtds:sqlserver://sqlprd:1433
[sqlprd][sa][master] 1>

The following is a breakdown of the arguments that were passed to the \connect command:

• -c ... : Provides the JDBC driver class (implementation) that is necessary to create a connection
• -U sa : Indicates that we will be connecting as the user "sa"
• The remainder is the full JDBC URL utilized to connect to the server.

# Executing queries

Once you have a connection established to your database server, jsqsh will behave like most other command line based queries tools (like isql, plsql, or sqsh); you type in a SQL statement until you are finished and use the \go command to execute the statement:

[prod-db][sa][master] 1> select srvname from sysservers
[prod-db][sa][master] 2> \go
+------------+
| srvname    |
+------------+
| PROD-DB    |
| PROD-STBY  |
| QA-DB      |
| QUAL-DB    |
| SANDBOX-DB |
+------------+
6 rows in results (first row: 10ms; total: 11ms)

Note that jsqsh automatically comes with an alias for \go of just go (without the backslash) for compatibility with isql and sqsh.

## Semicolon terminator

JSqsh also supports executing queries using a semicolon at the end of the statement being executed, like so:

[prod-db][sa][master] 1> select srvname from sysservers;
+------------+
| srvname    |
+------------+
| PROD-DB    |
| PROD-STBY  |
| QA-DB      |
| QUAL-DB    |
| SANDBOX-DB |
+------------+
6 rows in results (first row: 10ms; total: 11ms)

### Oracle Note

With 1.0, JSqsh added rudimentary parsing of PL/SQL to support the use of semicolon in much the same fashion as Oracle's SQL*PLUS tool. That is, if the SQL being entered is a single statement the semicolon will be treated as a \go. If the SQL entered includes a PL/SQL block, then Jsqsh will require a double semicolon (;;).