el-sql.doc.3.3.0.source-code.quick_start.html Maven / Gradle / Ivy
Show all versions of doc Show documentation
SQuirreL SQL Client Help
SQL Client Help File
Introduction
SQuirreL SQL Client is a graphical Java program that will allow
you to view the structure of a JDBC compliant database, browse the data
in tables, issue SQL commands etc. You need to be running at least
version 1.5 of Java for SQuirreL version 2.4 and higher. See the Old Versions
page for SQuirreL downloads that will work with older versions of Java.
It is free software, and you are welcome to redistribute it under
the terms of the GNU Lesser
General Public License.
Installation
The installer has been created using the IzPack Java Installer. Download
the file squirrel-sql-<version>-install.jar and execute
it with the command:
java -jar squirrel-sql-<version>-install.jar
On some operating systems (currently Windows NT, 2000 and XP) a
shortcut will be created in the menu system. For Windows the file squirrel-sql.bat
will run the application. For Unix the file is squirrel-sql.sh.
The squirrel-sql.jar file is executable so on those operating
systems that support it you can run SQuirrel by double clicking squirrel-sql.jar.
You can also run it by issuing the following command:
java -jar squirrel-sql.jar
Just remember that when executing the jar file directly you don't
use your current class path ($CLASSPATH on Unix or %CLASSPATH% on
Windows) so if you have your JDBC drivers in your class path then
SQuirreL won't see them. See The Driver for
more information.
Some people have reported core dumps with the Sun JDK1.3.1
running under Linux. If this happens passing the -classic flag to the
java executable appears to fix the problem. No similar problems have
been reported with any other JDK on Linux.
Command Line Options
The behavior of SQuirreL can be changed by passing various
arguments to it.
squirrel-sql.sh [options]
or
squirrel-sql.bat [options]
Options:
-userdir,--user-settings-dir <userdir> User settings directory
-l,--log-config-file <config-file> Logging configuration file
-home,--squirrel-home <home> SQuirreL home directory
-n,--native-laf Use native look and feel
-nop,--no-plugins Don't load plugins
-nos,--no-splash Don't display splash screen
-m,--use-default-metal-theme Use default metal theme
-h,--help Display Help and exit
-userdir specifies where SQuirreL stores its per-user
settings such as application preferences etc. This defaults to <home>/squirrel-sql.
-home specifies the directory that SQuirreL was
installed into. It is setup during the installation process. You should
never need to change this argument.
How to Connect to a Database
There are two pieces of information you need to supply to connect to a
database; the driver definition and the alias. The driver definition
specifies the JDBC driver to use and the alias specifies the connection
parameters.
The Driver
A number of default driver definitions ship with SQuirreL. These
are added to the Drivers List window (View Drivers option on
the Windows menu) when you first start up SQuirreL.
The 
icon
next to a driver definition indicates that it has been successfully
loaded while the 
icon indicates that the JDBC driver could not be loaded and so cannot be
used to connect to a database.
The 
toolbar button launches an external web browser with the website URL
associated with the currently selected driver definition.
All of the default driver definitions assume that the JDBC driver
classes are in the current class path or in your JRE extensions
directory (consult the documentation for your Java implementation for
more information on this). If this is not the case you will need to
modify the driver definition to point to the jar file or the classes
directory that contains these classes.
You can create new driver definitions using the New
Driver option on the Drivers menu, or by clicking the 
toolbar icon
This is an example of a driver definition. This dialog can be
displayed by selecting a driver definition in the Drivers List window
and then selecting the Modify Driver option from the Drivers
menu.
The Name text field specifies an easy to remember name
for the database driver definition and is the text shown in the Drivers
List window.
The Example URL shows an example URL for this driver.
This can be found in the documentation that comes with the driver. This
is only an example URL, the actual URL is defined in the alias.
The Website URL allows you to store a link to the
database vendor's website for JDBC driver documentation.
The entries in the Java Class path tab show the
directories and jars in the current class path. Pressing the List
Drivers button will place the class names of all the JDBC drivers found
in the class path into the Class Name dropdown control.
The Class Name specifies the class name of the JDBC
driver. Either select an existing entry in the dropdown or key in the
class name of the JDBC driver (it can normally be found in the
documentation that comes with the driver).
The Extra Class Path tab allows you to select a JDBC
driver that is in a jar file or a directory that is not in the current
class path.
The Add button will display a File Open dialog allowing
you to select one or more jar files or directories. The Delete
button allows you to remove a jar file or directory from the list. The Up
and Down buttons allow you to change the sequence of the
entries. The List Drivers button will place the class names of
all the JDBC drivers into the Class Name dropdown.
Press the OK button to save the driver definition and
close the window or the Close button to close the window
without saving any changes.
The Alias
Now you need to create an alias for your database. An alias is a
descriptive name that is associated with connection details necessary to
login to an interactive session with your database.
Display the Aliases List window (View Aliases option on
the Windows menu). When you run SQuirreL for the first time
this window will be empty.
You can create new aliases using New Alias option from
the Aliases menu, or by clicking the toolbar icon. The
following "Add Alias" dialog will be displayed :
Enter an easy to remember name in the Name field for
this database connection Alias. This is the text that will be displayed
in the Aliases List Window.
Select the driver for this alias from the Driver
dropdown or click on the New button to create a new driver
definition.
Enter the JDBC connection URL that is used to connect to
your database (Consult the documentation for the JDBC driver that you
are using. Each driver's JDBC connection URL has a slightly different
form. You will see an error message when you attempt to connect to your
database if the JDBC URL isn't correct.)
Optionally enter a user name if you want the alias to default to
a specific user. A password is also optional here. Caution: if a
password is entered, although it is obscured (****) when displayed here,
it is saved in an unencrypted file in clear text in the user's home
directory. Also note that username and password are required if "Auto
Logon" is selected.
The Test button will allow you to attempt to connect to
your alias to ensure that the parameters you have entered are correct.
Press the OK button to save the alias definition and
close the window or the Close button to close the window
without saving any changes.
The Properties button ( 
) in the alias dialog window allows you to configure alias properties
such as which schemas to load into the Object Tree. This is will reduce
the time that it takes to populate the Object Tree after
connecting/refreshing for databases like Oracle. There are three types
of objects that can be selected to be loaded, loaded and cached, or not
loaded at all for each schema. The Alias Properties dialog is displayed
below:
The
Connect database to refresh Schema table
button will load all schemas from the database into the table for
customization.
Driver Properties Tab - this tab allows you to configure the driver properties that
will be used when an alias connects to the database. Driver properties are a way that
JDBC vendors can give access to database-specific settings that alter the characteristics
of the database connection and the session resulting from that connection. For example, Oracle
provides properties that allow you to configure an SSL protected connection to keep your data
from traversing a network in the clear. The set of available properties shown in this tab is
reported to SQuirreL by the JDBC driver, so if any appear to be missing, check the vendor's
documentation and/or file a bug report with that vendor. Of course, you may also add and remove
properties by right-clicking anywhere in the table and accessing the context menu. The
following image shows an alias for Oracle and some of the driver properties that it supports:
Color Tab - this tab allows you to configure a custom background color for some parts
of SQuirreL to give a visual clue about the session. This is primarily useful when you have
multiple aliases to the same type of database, for example, one for test and one for production.
SQuirreL makes it easy to connect to multiple databases simultaneously, and since the sessions
can appear to be very similar, it is easy to mistake one for another. Using this tab, you can,
for example, give your production database alias a background color of red or orange to denote
caution, whereas a development database alias could be given a color of green or blue to
indicate that it's data is safe to modify/destroy. The following image shows an alias whose
background color is light red to indicate that caution should be used:
The following image shows what the color configuration above looks like for a new session created
from this alias:
Connection Tab - this tab allows you to configure an SQL statement to execute periodically
in the background. This can help keep the session connection alive for firewalls that terminate
connections due to inactivity. There is no support for working with data returned from the
SQL statement so any data returned will be ignored. Therefore, the SQL statement should be a valid,
idempotent one that is not resource intensive (Execution errors will be reported in the squirrel-sql.log).
Note that the statement separator (";" or "GO") should be omitted here. The period between executions of
the SQL statement is specified in seconds. The following image shows an example configuration for the
Oracle database with a commonly used SQL statement for testing Oracle connections:
Oracle Tab - this tab serves to customize schema loading for Oracle. The Oracle database driver
returns metadata that lists all objects (schemas and their objects) in the database, even those that the
connected user doesn't have permission to access in any meaningful way. This can cause a session connection
to take a long time to initialize as SQuirreL runs through all of the objects reported by the driver to
populate the object tree. SQuirreL employs a strategy to help reduce this initialization time by giving
you the ability to limit the schemas being loaded to just the ones that the user has access to. This tab
provides customizations that work together with the Schemas tab previously introduced above. Instructions
are provided that describe each setting's behavior. The following image shows this tab:
Alias Organization
As more aliases are added, it can become difficult to locate the
desired alias to use or modify. To help organize the aliases in the
alias window, they can be grouped into folders or even nested folders.
In order to turn this capability on, you must right-click anywhere in
the alias window and select the menu item "View Aliases in tree". The
following image shows what the popup menu in the aliases window looks
like:
After enabling alias tree mode, you can add folders to contain the
aliases and nest folders within folders. For example:
Connecting
The new alias should now appear in the Connect To
dropdown. Select it in the dropdown and enter your user name and
password in the connection dialog, press OK and you should be
connected.
Session Window
When you first connect to a database a Session window will be
shown.
The Object Tree on the left shows the structure of the database.
Clicking on various nodes within this object tree will show further
information in the detail panel on the right. In this example the database
metadata is shown. Clicking on the title of a column will sort the
display by the data in that column. This is true of all tabular displays
in SQuirreL.
This shows the data displayed when a table is selected in the
Object Tree.
Executing SQL
SQL can be executed from the SQL tab in the window that opens
once you have connected to an alias. Multiple statements can be executed
and the results will be displayed in multiple tabs below the SQL entry
area.
When the SQL tab is selected pressing <ctrl><enter>,
taking the Execute SQL option from the Session menu or pressing the
Execute SQL button in the session window tool bar will execute the
entered SQL.
If a single statement is entered then pressing <ctrl><enter>
will execute just that statement.
If you only want to execute part of the SQL entered then
highlight the SQL that you want to execute and press <ctrl><enter>.
To only execute a single statement amongst several statements
(separated from the other statements by at least one blank line) within
the SQL entry area then click on the line containing the statement that
you want to execute and press <ctrl><enter>.
The characters -- at the beginning of a line will turn it into a
comment.
As can be seen in the above screen shot, double clicking in a
cell in the query results will open up a new window showing the entire
contents of the cell.
Plugins can add many functions to the SQL editor. The tools popup
shows all of these functions and allows the user to execute them. The
functions in the popup may be filtered by typing the name of the filter
into the filter text field. The tools popup can be opened by pressing
<Ctrl>-t. A picture of the tools popup dialog is show below.
The 
icon on the tabbed folder containing the SQL results allow you to "tear
off" the query results from the tabbed folder and display it in its own
window.
SQL History
Every SQL statement that is successfully executed is also added
to the drop-down history list, which is shown below.
This list can then be used to find and re-execute previously
executed statements. The drop-down list allows you to pick the SQL
statement, and clicking the blue arrow ( 
) will copy
the statement into the editor. Additionally, the history list can be
searched using the SQL History dialog which is accessed by the icon ( 
) which is next to the blue arrow. The SQL History window is shown
below.
Editing Data in the SQL Results Tables
The data displayed within the SQL Results may be edited in the
same manner as the data within the Contents
Tab. You should read that section to understand the capabilities and
limitations of data editing, including the use of the Popup window to
import and export data. In addition, there are two things that you need
to know about editing in the SQL Results:
- To enable editing in the SQL Results tables, you must set the
"SQL Results" entry in the Session Properties to "Editable Table".
- Editing is only allowed when the SQL that was executed
consists of:
"SELECT <column list> FROM <single_table_name>"
or
"SELECT <column list> FROM <single_table_name> WHERE ..."
In addition, the name of the table must be unique within the entire DB.
This means that if you have multiple catalogs or schemas which have
that same table name defined in them, Squirrel cannot determine which
to use and thus will not allow data to be updated in the DB. (Squirrel
is just a bit stupid on this point: it will let you edit the data, and
then tell you that it cannot update the DB when you exit the cell being
changed.)
The Contents Tab and Editing Data
SQuirreL allows you to easily view and change the data in a
single table. Data may be changed just by typing the new values when you
are viewing the table in the Contents tab under the Objects view. The
data may also be viewed and edited in a larger popup window, which has
more capabilities, by double-clicking on the cell. There are some small
differences between the in-cell editing and editing in the Popup, so be
sure to read the Using the Popup Window
information.
Viewing Data in the Contents Tab
After connecting to a database, you may view the data in a single table
by using the Contents Tab.
To get to the contents tab, first select the Objects view. Next,
look under the tree in the window on the left. The exact organization of
this window is different for each DBMS, so you may have to do a bit of
exploring. What you are looking for is a list of table names, and they
are usually found under a heading labeled "TABLE" or "TABLES". The
"TABLE" heading may itself be under another heading, such as the name of
the database instance. When you find the list of table names, click on
the name of the table that you wish to view, and then in the window on
the right click on the tab labeled "Contents".
The data in the table will be displayed in rows and columns based
on the format selected (see Setting
the Format and Editing Mode).
The column ordering is the order of the rows in the table
definition. That order may be changed by drag-and-drop, which means
clicking on the column header and holding the mouse button down while
dragging the column horizontally to another location. Columns are
initially displayed with a moderate width so that you can see a
reasonable amount of data in as many columns as possible within the
limited window space. You may increase or decrease a column's width by
click-and-hold on the space between two columns, then moving the mouse
left or right to change the column widths.
The rows are initally unordered. However, after retrieving the
data, you may sort the rows based on the values in a column by
single-clicking on a column header. That will sort the rows into an
order with the values in the selected columns starting with the
"smallest" value. Clicking on the same column header again will reverse
the order ("largest" first). You can limit the number of rows retrieved
(see the Session Properties, Object Tree).
There are a few special cases for how data is displayed.
- If a cell contains Newlines (which is possible in the various
VARCHAR and CLOB data types), the cell background is colored Cyan and
the Newline characters are explicitly shown as "\n" (unless changed in
the Data Type Controls). If the table
is editable, the Cyan background means that the cell contents cannot be
edited ina the cell as displayed in the table, but can be edited using
the Popup window (see Using the Popup Window).
- BLOB (Binary Large OBject) and CLOB (Character Large Object)
fields are initially displayed as "<Blob>" and "<Clob>"
respecively. This is done for performance reasons, since those data
types are often quite large. When you select the Blob or Clob field,
the entire data is read in and displayed in the cell.
Alternatively, you may use the Session Properties -> Format screen
to limit the amount of the Blob/Clob data read and displayed when the
Contents Tab is first displayed, in which case the number of characters
you have selected will be displayed in each Blob or Clob cell. This may
be necessary when the Blob or Clob contains data needed to identify
specific rows in the table. When there is more Blob/Clob data for a
cell than is displayed in that cell, an elipsis ("...") is added to the
end of the displayed data so you know that the data has been truncated
for display. As before, when you select the cell, the whole Blob or
Clob will be read and displayed.
One word of warning. Some DBMSs that claim to have BLOB and CLOB
data types actually implement those types using BINARY, VARCHAR, or
other types of fields. In those cases, the cells will act as they would
for the BINARY, VARCHAR, etc., data types, and not as described above
for the BLOB/CLOB types. (There is a functional difference in that
"real" BLOB/CLOB types return a "locator" or "reference" object rather
than the actual data. Using the locator allows SQuirreL to defer reading
the actual data until you need it rather than reading it all during the
initial display of the Contents Tab.) To see if your DBMS uses real
BLOB/CLOB types, go to the Objects view, then select the top element in
the tree in the left window and "Data Types" in the right window. In the
column labeled "DATA_TYPE", real BLOBs will have the value 2004 and real
CLOBs will show 2005. If your DBMS does not have 2004 and 2005 listed
under the "DATA_TYPE" field, then it does not support the BLOB/CLOB
functionality provided by SQuirreL. As an example, the MySQL DBMS claims
to support BLOBS, but in the "Data Types" table it shows that the
"TYPE_NAME" of "BLOB" is actually using the "DATA_TYPE" of "-4
[LONGVARBINARY]", so a "BLOB" field in MySQL will be treated by SQuirreL
as a LONGVARBINARY and not as a "BLOB".
Enabling Editing
When SQuirreL is initially set up, the Contents Tab will be set
in a read-only mode. This is a safety precaution. Once a table is made
editable, changing the contents of a cell causes the database to be
immediately updated. While extremely convenient, this is dangerous. With
this capability enabled, it becomes very easy to accidentally destroy
data, violate some consistancy checks, mis-direct references between
tables, etc. On the other hand, the author of this feature runs with
editing always enabled. It is simpler that way, but has the potential
for disaster. You have been warned!
You can set SQuirreL so that tables are editable all the time, or
you can enable editing on a specific table as you are working on it.
Either option may be set as your default mode of operation, and either
option may be selected for use on a specific table.
Editing may only be done in Contents tab in the Object view, not
on the results of manually entered SQL queries run under the SQL view.
Setting the Format and
Editing Mode
To set the default format and edting mode for the Contents Tab
for new sessions take the New Session Properties option from
the File and click on the General tab. The Table
Contents dropdown list gives the following options:
- Table
- The data in the Contents tab is a read-only table. You may
click in any cell or tab through the table and any accidental typing
you do will be ignored and not change the database. To enable editing
on this table, click on the right mouse button and select the Make
Editable option. This makes that table and only that table
editable.
- Text
- The data in the Contents tab is shown as simple formatted text
and is read-only. To enable editing on this table, ;click on the right
mouse button and select the Make Editable option. This makes the
table and only that table editable
- Editable Table
- The data in the Contents tab is an editable table. Changes
made to the data in this view are made to the database.
After you have connected with a database, you may change the
format or editing mode for the data in the Contents tab by the Session
Properties option from the Session and clicking on the General
tab and changing the selection for Table Contents.
Note that the individual data items may be displayed in different
formats as described in
"Data Type Controls"
.
How To Edit
Here is how to edit data in the table:
- Connect to the database.
- Select a table in the object tree and select the Contents
tab to view the data.
- Make sure the table is editable. See Setting the Format and Editing
Mode above.
- Select the cell that you want to edit. This may be done either
by using the keyboard (tab and enter keys) to move around the table to
reach the cell, or you may click on the cell with the mouse. Note that
if a cell cannot be edited (see Limitations)
you may select the cell, but you will not be allowed to edit the data.
When a cell is being edited, its background will change to Yellow to
let you know that you may be changing the contents of the database.
Cells with a Cyan background may be edited only by using the Popup
window (see Using the Popup Window).
- If you select the cell using the mouse, the background
immediately turns Yellow and the cursor is set to the place where you
clicked in the cell. If you select the cell by using the tab or enter
keys, the background remains white until you type the first character
to be added (or use the delete key). That character is added to (or
deleted from) the end of the text displayed in the cell, and the cell
background changes to Yellow.
- Change the data in the cell to look the way that you want it
to. Editing is done in the usual way by typing characters, using delete
to erase, and using the mouse to select a point to insert or characters
to delete. There are a couple of specialized features that are
described later in this section.
- When the data in the cell looks the way you want, leave the
cell by pressing the tab key, the enter key, or clicking on another
cell with the mouse. This causes the data in that cell to be updated in
the DB.
If the data in the cell gets really messed up and you are
confused about what is going on, press the <delete> key until the
original data appears (see below). If that doesn't work then immediately
close the database connection without leaving the cell (<ctrl>W or
click the close button). This will abort the operation and avoid
changing the DB.
If the cell contains a NULL value, that is shown as the string
"<null>". To change that to a non-null value, just type the new
data. When you enter the first character of the data, the "<null>"
will be replaced by that character.
NOTE: This means that SQuirreL will not allow you to enter a string with
the value "<null>" into a nullable column, since that string will
be interpreted by SQuirreL to be the null value. Also, if you have a
field in the database containing the string "<null>", do not edit
that field using SQuirreL since the value will be replaced by the null
value.
To change the value of a nullable cell to NULL, just delete all
of the data in that cell. When you delete all of the data in a cell, the
contents of the cell will be set to "<null>". Since the VARCHAR
data types allow a non-null empty string, for those cells you must
delete all of the data so that the cell is blank, and then press delete.
The VARCHAR cell will then be set to <null>.
If a cell is not nullable, deleting the last character in the
cell will leave the cell blank. You may then enter new data for the
cell.
To restore the cell to its original value, delete all of the data
in the cell, then press the delete key one more time. This will cause
the cell to be reset to its original value. For cells that allow nulls,
continually pressing the delete key causes a cycle:
original data -> delete data ->
<null> -> original data
and for cells that do not allow nulls:
original data -> delete data -> blank
cell -> original data
Thus, if you mess up the contents of a cell, repeatedly press the
delete key until you see the original data reappear.
When leaving the cell, if the cell data is identical to the
original contents of the cell, the DB will not be updated.
Inserting and Deleting Rows
You may easily insert or delete rows in the table using the Contents Tab
For both operations the table must be editable (see
Enabling Editing
)
To insert a row into the table, click the right mouse button, and
choose the "Insert row" option from the menu This brings up a separate
window showing the columns of the table with two rows The first row
contains the data to be filled i with default values as defined for the
table's column already filled in The second row shows information about
the field including the type of data, the field length (if known), and
so forth You should edit the values in the first row in the same way
that you would if that row already existed in the table When the data in
the row is the way you want it, click the "OK" button to have the row
entered into the table
If there are no rows in the table when you open the Contents Tab
clicking the right mouse button brings up a menu with only the "Insert
Row option You may use this in the normal manner to enter the first row
in the table
To delete one or more rows from the table select the rows to be
deleted, then click the right mouse button an choose the "Delete Rows"
option from the menu
Using the Popup Window
Editing within the Popup
When you double-click in a cell, a Popup window is displayed. For
simple data (e.g. integers, floats, or simple strings) you will be able
to edit the data directly in the cell, but there are some times when you
will need to use the Popup:
- when the data is too long to comfortably work with in the
space provided by the cell
- when the data contains newlines or tabs (which are used as
control characters by the in-cell editor and are not displayed
correctly there)
- when the data must be represented in a complex way, such as
needing multiple lines (e.g. an Array type)
- when the data is potentially so large or complex that loading
it into the table cells in a printable form might significantly delay
the display of the table. In this case, the cell may display just the
name of the data type, something like "<Blob>", while the Popup
window would show the actual contents of that field.
For these reasons the data may not be editable in the cell but still be
editable in the Popup. If the data is not editable in the cell (i.e. the
background does not change color when you click on it), you will need to
bring up the Popup window to know whether the data can be edited there.
To use the Popup, double-click in the cell to start the Popup,
then edit the text in the text window. Editing uses the same mechanism
as the in-cell editing where repeatedly typeing the backspace key
eventually restores the original data in the window. One difference from
the in-cell editing is that, depending on the data type being displayed,
you may enter newline and tab characters into strings when in the Popup.
When you are done editing, click on the "Update DB" button to put the
changed data into the Database. The "Cancel" button closes the Popup
without changing the Database.
For Binary data types, the data may be changed from the default
Hex representation into Decimal, Octal or Binary formats. You may also
choose to view bytes of data that correspond to printable ASCII
characters as the character rather than in their numeric form. This may
be useful if the file contains strings, in which case you could see
"ContentsTab.java" instead of "43 6f 6e 74 65 6e 74 73 54 61 62 2e 6a 61
76 61".
The Popup window provides line-wrap and word-wrap functions.
These are accessed in the menu brought up by right-clicking in the text
area. Both of these options are toggle-switches that flip from on to off
or off to on each time you click on them. The word-wrap function
operates only when the line-wrap function is on.
Import, Export and Editing with an
External Command
The Popup window also allows you to import data into a cell,
export data from the cell to a file, and operate on the data using an
external command.
- Importing data into a cell:
- Enter the name of the file containing the data in the "File:"
field, then click on "Import".
- Exporting data from the cell into a file:
- Enter the name of the target file in the "File:" field, then
click on "Export". Note that the default value of the "File:" field is
"<Temp File>", and if you do not change that entry the system
will put the data into a new temp file and tell you the name of that
file after it has been written.
- Operating on the data with an External Command:
- This function provides a single-click operation to export the
data to a file, operate on that file with an external process (e.g. MS
Word, VI, various graphical editors, etc.), then read the result back
from the file when editing is completed. To do this you must:
- Enter a file name in the "File:"
- field or leave it set to "<Temp File>". If you leave it
set to "<Temp File>", a temporary file will be created for you.
If you enter your own file name, be warned that the file will be
deleted after the data is read back when editing is completed.
- Enter a command in the "Command:" field.
- This field is a Combo box where the drop-down list contains
all of the commands you have used for editing other fields, so you can
just choose one of them. The command will typically need the name of
the file to edit. This may be done by using "%f" where you want the
name of the file to go in that command, so, for example, instead of
typing:
xterm -e vi myfilename
you could type
xterm -e vi %f
which makes the command more general and avoids the need for you to
edit the command for each file.
- Click on "Execute".
- This causes the cell data to be exported to the selected file
and the command to be executed. When the external process exits,
SQuirreL imports the data from that same file back into the popup and
deletes the file. IMPORTANT: SQuirreL does NOT automatically update
the database with this data. To put the edited data into the database,
click on the "Update DB" button.
Note: SQuirrel does not provide a window for the command to
operate in, so you may need to provide for that as part of the command.
For example, to run VI on Linux you may need to enter the command:
xterm -e vi %f
to have VI run in a window visible to you. If you just enter "vi %f" as
the command, you will not get a window to operate in. Other commands
(e.g. gimp) provide their own windows, so the extra step will not be
necessary for them.
In all of the above cases, the entries you make in the "File:"
and "Command" fields will be associated with that column so that you do
not need to re-type them the next time that you edit that field. You
may, of course, change those values at any time.
Also, if you just want to set the default values for a particular
column, you can enter the "File:" and "Command:" field information and
click on "Apply".
Limitations
Some DBMSs implement "standard" data types in non-standard ways
For example, we know that many DBMSs claim to implement the BLOB data
type but they actually store that data using a BINARY or LONGVARBINARY
field SQuirreL uses the actual data type as defined in the
DATA_TYPE field in the Columns tab for the table, not the TYPE_NAME Thus
it is possible that you may think you are working with one type of data
but actually using something else so controls and limitations on
specific data type (e.g. limiting the data read when loading the
Contents tab may not work as expected.
SQuirreL allows editing only on cells that are defined using the
basic standard SQL data type (see Data Types)
If your DBMS provides other data types, you can create a plug-in that
supports the (see the file
net/sourceforge/squirrel_sql/fw/datasetviewer/cellcomponent/CellComponentFactory
for more information)
Some attempt has been made to validate user input for each of the
types. For example, INTEGER fields allow only digits and '-' as input.
However, not all of the necessary information is supplied by all DBMSs,
so we cannot do a complete validation of user input in all cases. For
example, we cannot check that a value entered for a SMALLINT field will
fit into a SMALLINT column in a specific DBMS. Also, the drivers for
each DBMS provide different amounts of information for the same field
type in different situation, and SQuirreL cannot validate the input when
such data is missing The "validation of last resort" is done when we try
to do an update on the DB. The DBMS either succeeds or it fails, and if
there is an error we display that message to the user with the
information that the DB has not been updated.
VARCHAR fields containing tabs or newlines may only be edited in
the Popup window Fields that can only be edited in the Popup are
displayed with a cyan background
VARCHAR fields that may be set to null cannot be set to the
string value "<null> " (without the quotes). VARCHAR fields in the
database may include that value as long as you do not try to edit them
Tables with a large number of columns or with a column containing a very
long value (not includeing BLOB/CLOB data types) may get an error when
trying to update the DB because the WHERE clause is too long. In this
case you should open the table in a session, go to the
Session
menu in the main window, and select the entry "Limit cell edit WHERE
clause size" That tool lets you specify which columns SQuirrel should
use in the WHERE clause when doing the requested update on the cell
contents See
Session Menu
.
Summary of How it works
(and why you may care)
The DB updates are based on the assumption that the list of DB
column names and their values for the current row are unique, or if they
are not unique, that all of the rows with those values are to be updated
in the DB. For example, if you have a database with columns a, b, and c,
and in a particular row you have the values
a=34 b='your name' c=55
and you change column c to be 66, then SQuirreL does the
following:
- Query the DB for how many rows contain a=34, b='your name',
and c=55. If there are more than one, then the update will affect all
of those rows, so SQuirreL asks if you want to do that before
continuing.
- Query the DB for how many rows contain a=34, b='your name' and
c=66. If there are any existing rows with those values, changing the
current row to c=66 will make the current row identical to the existing
row(s), so SQuirreL asks if you want to do that before continuing.
- Create and execute the following:
update <table> set c=66 where
a=34 and b='your name' and c=55
To help distinguish rows that are similar to each other, any DBMS
that provides a pseudo-column to uniquely identify each row (such as
Oracle's ROWID) has that pseudo-column included in all querys.
The problem with this is that some columns cannot be
differentiated in this way, most notably BLOBs and CLOBs. Thus, if your
table contains BLOB or CLOB columns, SQuirreL may think that rows are
identical because all of the non-BLOB/CLOB fields are identical, but the
rows are actually not the same because the contents of the BLOB/CLOB
fields differ. This could lead to "erroneous" warning messages.
Quirks, Oddities and Known Bugs and
work-arounds
- After error or warning messages, the focus is lost. You will
need to click on a cell in order to continue editing.
- When using the enter key to leave a cell, you must press it
two times. The first time causes the data to be updated in the DB, but
leaves you back in the same cell. Pressing enter again moves to the
cell below the current one.
- Displaying all records in large tables (> 10000 rows) is
memory and cpu intensive and can cause SQuirreL to attempt to allocate
more memory than it is configured to use by default (256 MB). If this
is a problem, you can increase the memory that SQuirreL is allowed to
use by editing the launch script (squirrel-sql.bat for Windows,
squirrel-sql.sh for Linux/MacOS X). Search for -Xmx256m in the script
and change it to something appropriately higher (For example, -Xmx512m
(512 MB) or -Xmx1024m (1 GB)). This setting should not be greater than
the physical RAM you have installed on the machine that SQuirreL is
being run on. Setting it too high can cause the Java VM that SQuirreL
runs in not to start.
- Records that contain large BLOBs (> 500KB) are currently a
big problem for SQuirreL. SQuirreL will attempt to render BLOB data as
text by converting it to HEX. The conversion process is memory
intensive, and so is displaying the HEX converted binary data. It is
currently not practical to work with data in tables that contain BLOBs
of size in excess of 500 KBs. When viewing/editing tables with small
BLOBs, but many records, the maximum memory given to SQuirreL should be
increased, using the procedure outlined in the previous bullet.
Printing
Any data that is displayed in a Table may be printed. This
includes all of the meta-data tables displayed in the Object View as
well as the Contents Tab and SQL Results tables. To print, first make
sure that the Session Properties for the type of information (Meta Data,
Table Contents, or SQL Results) has been set to either "Table" or
"Editable Table". Then, with the cursor over the table to be printed,
click on the right mouse button and click on "Print". This will bring up
a dialog box in which you can adjust the parameters of the print job.
When you tell the dialog to print, the selected table will be printed.
Tables that are too wide or too long to fit onto one page will be
printed across multiple pages that can be pieced together to show the
entire table.
If the Session Properties for "Table Contents" or "SQL Results"
have been set to "Text", then the data within the Contents Tab or SQL
Results tables (respectively) may be copied and pasted into a separate
window for printing.
Data Types
This is a quick description of how SQuirreL handles various data
types when displaying the results of SQL statements. SQuirreL uses the
column type (ResultSetMetaData.getColumnType(...)) from the metadata for
the result set to determine how to display the column.
- java.sql.Types.NULL
- <Null> is displayed.
- java.sql.Types.BOOLEAN and java.sql.Types.BIT
- If the retrieved column data is a java.lang.Boolean
object then the appropriate true/false is displayed. If the
retrieved column is a java.lang.Number then a non-zero value
will display true while a zero value will display false.
Otherwise the column data is converted to a string and if this string
equals (ignoring case) "true" then true is displayed else false.
- java.sql.Types.TIME
- A java.sql.Time object is retrieved from the result
set and displayed in hh:mm:ss format.
- java.sql.Types.DATE
- A java.sql.Date object is retrieved from the result
set and displayed in yyyy-mm-dd format.
- java.sql.Types.TIMESTAMP
- A java.sql.TimeStamp object is retrieved from the
result set and displayed in yyyy-mm-dd hh:mm:ss.fffffffff
format. WARNING: When updating any column in a row containing TIMESTAMP
fields, the MySQL DBMS will automatically set the first TIMESTAMP field
in the row to the current time. This is a "feature" of MySQL, and not a
bug in SQuirreL.
- java.sql.Types.BIGINT
- If the retrieved column type is a java.lang.Long or a
java.lang.Number then it is stored as a java.lang.Long
and displayed as a base ten signed long. Otherwise the column data is
converted to a string which is then parsed as a base ten signed long.
- java.sql.Types.DOUBLE, java.sql.Types.FLOAT,
java.sql.Types.REAL
- If the retrieved column type is a java.lang.Double or
a java.lang.Number then it is stored as a java.lang.Double
and displayed as a string representation of the double. Otherwise the
column data is converted to a string which is then parsed as a double.
- java.sql.Types.DECIMAL, java.sql.Types.NUMERIC
- If the retrieved column type is a java.math.BigDecimal
or a java.lang.Number then it is stored as a java.math.BigDecimal
and displayed as a string representation of the BigDecimal.
Otherwise the column data is converted to a string which is then parsed
as a BigDecimal.
- java.sql.Types.INTEGER, java.sql.Types.SMALLINT,
java.sql.Types.TINYINT
- If the retrieved column type is a java.lang.Integer
then it is stored as a java.lang.Integer and displayed as a
string representation of the Integer. Otherwise the column
data is converted to a string which is then parsed as a Integer.
- java.sql.Types.CHAR, java.sql.Types.VARCHAR,
java.sql.Types.LONGVARCHAR
- The column data is displayed as a string. Note that if the
data includes a newline character, that particular data item cannot be
edited in the cell within the table, but if you double-click on the
cell to get the Popup window, you will be able to edit the data there.
- java.sql.Types.BINARY, java.sql.Types.VARBINARY,
java.sql.Types.LONGVARBINARY
- In the SQL tab the column data is displayed as a string. The
ContentsTab in the Object view displays each byte using Hex format. In
the Popup editing window, you may choose to display these in Hex,
Decimal, Octal, or Binary, and you may choose to display values
representing printable ASCII characters as those characters (e.g. to
see strings embedded in the binary object).
- java.sql.Types.BLOB
- If the Global Preferences,
Data Type Controls says to retrieve blobs then the column data is
displayed as bytes. Note that the Global
Preferences, Data Type Controls can specify the number of bytes of the
blob to read in during the initial setup of the ContentsTab table. If
you do not specify a limit, the default operation is to not read in any
data and to display "<Blob>" in the column. Clicking on an
individual cell causes the entire BLOB data to be read and displayed at
that time. Note: BLOB fields may be very large, and this can cause the
Popup window to take some time to start up (e.g. 30 sec. for a BLOB
containing a relatively small sized photo).
- java.sql.Types.CLOB
- If the Global Preferences,
Data Type Controls says to retrieve clobs then the column data is
displayed as a string. Note that the Global
Preferences, Data Type Controls can specify the number of characters of
the clob to read in during the initial setup of the ContentsTab table.
If you do not specify a limit, the default operation is to not read in
any data and to display "<Clob>" in the column. Clicking on an
individual cell causes the entire CLOB data to be read and displayed at
that time.
- java.sql.Types.OTHER
- If the Global Preferences,
Data Type Controls says to retrieve columns of this type then the
column data is displayed as a string.
- Any other type
- If the Global Preferences,
Data Type Controls says to retrieve all other data types then the
column data is displayed as a string.
The JDBC specification (in file java.sql.Types) defines several
codes whose semantics are not understood by SQuirreL: JAVA_OBJECT,
DISTINCT, STRUCT, ARRAY, REF, and DATALINK. These are all handled as
"Any other type" as described above.
Global Preferences
Global Preferences specify configuration settings for the
application.
The descriptions included here are for the tabs created by the core
product and standard plugins (if standard plugins are installed).
Optional Plugins may add other tabs to this window. See the
Plugin-specific help for descriptions of the tabs added by that plugin.
General Tab
General Tab (Appearance)
- Use tabbed layout
- if selected each new session becomes a new tab, instead of
having it's own internal window. An example of two sessions with
SQuirreL configured for tabbed layout appears below (Note: in
particular, notice that only one session window tab can be selected at
a time leading to less clutter and zero internal window size
management):
- Use MDI Internal Frame layout
- if selected each new session becomes a new internal window
(Multiple Document Interface). We refer to this as the "Classic"
session layout interface as it has been around the longest. An example
of the same two sessions with SQuirreL configured for MDI layout
appears below:
- Show Window Contents While Dragging
- If checked this shows the contents of windows as they are
being dragged. If unchecked only the outline of the window will be
shown. Uncheck for a speed improvement on slow machines.
- Show Tooltips
- If checked then tooltips (or hints) will be shown when the
mouse "hovers" over some controls.
- Use Scrollable Tabbed Panes
- If checked will display the tabs in tabbed folders in a
scrollable region rather than wrapping them when all tabs will not fit
within a single run.
Stacked Tabs
Scrollable Tabs
- Show Main Window Tool Bar
- If checked then the tool bar will be displayed in the main
window.
- Show Main Window Status Bar
- If checked then the status bar will be displayed in the main
window.
- Show Drivers Tool Bar
- If checked then the tool bar will be displayed in the Aliases
Window.
- Show Aliases Tool Bar
- If checked then the tool bar will be displayed in the Aliases
Window.
- Maximize Session Sheet on Open
- If checked then when a session is opened it will automatically
maximize to the whole space available within the Squirrel window. If
not checked, it is opened using a smaller part of the space.
- Show Color Icons in Toolbars
- If checked then the icons in the toolbars are shown in color.
If not checked, the icons are in black-and-white.
General Tab (Logging)
- Execution Log File
- This (display only) setting tells you the name (and location)
of the logging file for SQuirreL. If the file name is too long for the
label then the full name will be displayed in a tooltip. For more
information see Logs.
- Configuration File
- This (display only) setting tells you the name (and location)
of the logging configuration file for SQuirreL. If the file name is too
long for the label then the full name will be displayed in a tooltip.
For more information see Logs.
SQL Tab
SQL Tab (General)
- Login Timeout
- Sets the maximum time in seconds that a JDBC driver will wait
while attempting to connect to the database.
- Large Scrip Statement Count
- This feature can dramatically improve UI responsiveness when
executing many statements in large scripts (especially those with
statement counts in the thousands). By default, a script is considered
large if it has more than 200 SQL statements selected for execution in
the script. When this is set to zero, large script execution support is
disabled. Large script support consists of the following behavior
during execution:
- Only one message is written in the message panel at the
bottom of the session window at the end of running many statements
summarizing the details of execution (timing, statement type and
count, etc).
- The message "SQL Statement x of y <some sql...>" is
still displayed in the results panel to indicate progress.
- The history bar isn't updated during large script execution.
SQL Tab (Debug)
- No Debug
- If checked then no debugging information is output from
Squirrel.
- JDBC Debug to Output Stream
- If checked tells the JDBC drivers to write debugging
information to the JDBC Debug File. Depending on the JDBC driver you
are using this may generate a lot of output and significantly slow down
SQuirreL.
- JDBC Debug to Writer
- If checked tells the JDBC drivers to write debugging
information to the JDBC Debug File. Depending on the JDBC driver you
are using this may generate a lot of output and significantly slow down
SQuirreL.
- JDBC Debug File
- If JDBC Debug is enabled then this (display only) setting is
the file that the debug information will be written out to. If the file
name is too long for the label then the full name will be displayed in
a tooltip.
The
JDBC Debug to Output Stream
has been deprecated by JavaSoft in favour of the
JDBC Debug To Writer
option. However some older JDBC drivers may use the
Output Stream
rather than the
Writer
. Other drivers (such as the oracle 8 JDBC driver) may write different
information to the
Output Stream
than they do to the
Writer
.
SQL Tab (SQL File)
Open in previous direcory When opening files in the SQL
editor, sets the location in the file chooser to be the last directory
that a script file was opened from.
Open in specified direcory When opening files in the SQL
editor, sets the location in the file chooser to be the directory that
is specified in the text field.
Proxy Tab
- Proxy Tab (HTTP Proxy)
-
- Use Proxy
- If checked then a HTTP proxy server will be used for
connecting to servers.
- Server
- The name (or IP address) of your proxy server.
- Port
- The port number to use on the proxy server.
- User
- The user name to use to log onto the proxy server.
- Password
- The password to use to log onto the proxy server.
- No Proxy For
- A list of servers (separated by |) that do not need to be
accessed through your proxy server.
- Proxy Tab (SOCKS Proxy)
-
- Use Proxy
- If checked then a SOCKS proxy server will be used for
connecting to servers.
- Server
- The name (or IP address) of your proxy server.
- Port
- The port number to use on the proxy server.
Data Type Controls Tab
The contents of this tab may change depending on what Plugins you
have loaded. Plugins are allowed to define new Data Types or override
the existing Data Types, and that may add or remove controls from this
window. The following describes the standard Data Type controls with no
changes made by any Plugins.
Even with only the default Data Type Controls defined, there are
still two screens of them to describe.
BLOB (SQL Type 2004)
Read contents when table is first loaded - If checked then
the actual contents of the BLOB is read when the table is first loaded.
This is usually not recommended because it usually degrades performance.
If this is not checked, then the data will initally display as
">Blob<" in the table, and the entire contents will be read in and
displayed when you click on that cell.
Read - If reading the contents during initial table load,
select whether to read the entire contents or just part of the data. If
only part of the data is read, then the entire contents will be read
when you click on that cell.
(number) - When reading part of the data during inital
table load, how many bytes to read.
CLOB (SQL Type 2005)
Read contents when table is first loaded - If checked then
the actual contents of the CLOB is read when the table is first loaded.
This is usually not recommended because it usually degrades performance.
If this is not checked, then the data will initally display as
">Clob<" in the table, and the entire contents will be read in and
displayed
Read - If reading the contents during initial table load,
select whether to read the entire contents or just part of the data. If
only part of the data is read, then the entire contents will be read
when you click on that cell.
(number) - When reading part of the data during inital
table load, how many characters to read.
Show newlines as \n within cells - If checked, when the
data contains newlines, the newlines will be displayed as "\n" within
the cell. When unchecked, the newlines are ignored within the cell. The
difference is between seeing:
first line\nsecond line\nthird line
and
first linesecond linethird line
Note that this option does not affect display in the Popup window,
where newlines actually become new lines.
CHAR, VARCHAR, LONGVARCHAR (SQL Types 1, 12, -1)
Show newlines as \n within cells - If checked, when the
data contains newlines, the newlines will be displayed as "\n" within
the cell. When unchecked, the newlines are ignored within the cell. The
difference is between seeing:
first line\nsecond line\nthird line
and
first linesecond linethird line
Note that this option does not affect display in the Popup window,
where newlines actually become new lines.
Allow LONGVARCHAR type to be used in WHERE clause - When
checked, the LONGVARCHAR type is used in WHERE clauses just as other
types. When unchecked, columns of type LONGVARCHAR are not used in the
WHERE clause when performing edits in the cell data. This is necessary
because some databases (e.g. Oracle, DB2) do not allow LONGVARCHAR data
types to be used in WHERE clauses and generate odd error messages when
they are seen.
Limit size of strings read during initial table load ... -
When checked, the amount of string data loaded during the initial table
read is limited to a maximum size (see next field). When unchecked, the
amount of data is not limited. (Note: This was originally built as a
performance improvement, but the implementation actually reads all of
the data from the DB in either case and just displays or does not
display all of it, and thus may not make an impact on performance.) If
the inital data read is limited, the entire data contents will be
displayed when the user clicks on the cell.
... to max of - When limiting the size of the data, this
defines how many characters to display.
Limit read only on columns with these names: - When
limiting the size of data during the initial table load, only apply the
limit to columns with the names listed in this box. All other columns
load all of the data. (This was built as a technology demo to show how
Data Type Controls, which are global to all columns of the given SQL
type(s), can be applied selectively to a limited set of columns.)
SQL Other (SQL Type 1111)
Read contents when table is first loaded and display as
string - If checked then columns of this SQL Type will be read by
Squirrel and displayed using the toString() function.
Warning: Since fields of this data type are not known to Squirrel and
have been explicitly defined as NOT being of a normal, known type,
attempting to display them as Strings may result in unintelligable
displays, and may cause exceptions during processing.
Unknown DataTypes (non-standard SQL type codes)
Read contents when table is first loaded and display as
string - If checked then columns of this SQL Type will be read by
Squirrel and displayed using the toString() function.
Warning: Since fields of this data type are not known to Squirrel and
have been explicitly defined as NOT being of a normal, known type,
attempting to display them as Strings may result in unintelligable
displays, and may cause exceptions during processing.
Date (SQL Type 91)
Use default format(yyyy-mm-dd) - If checked then dates
will be displayed in the JDBC default format as shown. If unchecked,
then the "locale-dependent" formats are made available.
or locale-dependent format: - When not using the default
format, this allows you to select from one of the other available
formats.
allow inexact format on input - If checked then Squirrel
allows some flexibility when you type a new value for the field.
Time (SQL Type 92)
Use default format(hh:mm:ss) - If checked then times will
be displayed in the JDBC default format as shown. If unchecked, then the
"locale-dependent" formats are made available.
or locale-dependent format: - When not using the default
format, this allows you to select from one of the other available
formats.
allow inexact format on input - If checked then Squirrel
allows some flexibility when you type a new value for the field.
Timestamp (SQL Type 93)
Use default format(yyyy-mm-dd hh:mm:ss.msec) - If checked
then timestamps will be displayed in the JDBC default format as shown.
If unchecked, then the "locale-dependent" formats are made available.
or locale-dependent format: - When not using the default
format, this allows you to select from one of the other available
formats.
allow inexact format on input - If checked then Squirrel
allows some flexibility when you type a new value for the field.
L&F Tab
This tab is installed by the Look and Feel (L&F) plugin which is
part of the "standard" plugin set. If you didn't choose to install the
"standard" plugins then this tab will not appear.
L&F Tab (General)
Allow L&F to set tilebar and window borders - allows
you to specify whether the Look and Feel can set these, otherwise the
Window Manager will set them. This setting only takes effect for newly
created windows.
L&F Tab (Look and Feel)
Look And Feel - allows you to specify which L&F family
to use. This setting only takes effect for newly created windows.
Theme - If the selected L&F supports themes, this
allows you to specify which one to use
Fonts Tab
Fonts Tab (Fonts)
Menus - If enabled this will override the default font
with the selected font for menus/menu items in the toolbar and the popup
menu. Pictured below is the main frame menubar with Ravie 12-point Bold
font.
Static Text - If enabled this will override the default
font with the selected font for labels on Tabs and Object Tree Nodes.
The picture below shows a session window with Ravie 12-point Bold font.
Status Bars - If enabled this will override the default
font with the selected font for labels on status bars. The picture below
shows a session window status bar and the main application status bar
with Ravie 12-point Bold font.
Other - If enabled this will override the default font
with the selected font for labels in many places not already covered by
the preceding settings. The picture below shows "other" labels with
Ravie 12-point Bold font.
The font tab is also installed by the Look and Feel (L&F) plugin
which is part of the "standard" plugin set. If you didn't choose to
install the "standard" plugins then this tab will not appear. There may
be other tabs present in the Global Preferences dialog (plugin-specific)
which are not listed here. See the plugin documentation for details. For
preference tabs installed by plugins, the tab name should match the
plugin name.
New Session and Session Properties
The "New Session Properties" dialog allows you to specify
settings for future session. To change the settings for existing
sessions use the "Session Properties" menu option. The descriptions
included here are for the tabs created by the core product. Plugins may
add other tabs to this window. See the Plugin-specific help for
descriptions of the tabs added by that plugin.
General Tab
General Tab (Appearance)
Show Tool Bar - If checked then display a toolbar on the
session window.
Main Tabs - Defines where the Main tabs (ie: "Objects" and
"SQL") in the session window go relative to the data being displayed.
This may be set to top, bottom, left or right.
Object Tabs - Defines where the tabs in the Object window
(eg: Metadata, Contents, Status, etc.) go relative to the data being
displayed. This may be set to top, bottom, left or right.
SQL Execution Tabs - Defines where the tabs for the sets
of results from SQL execution in the SQL window go relative to the data
being displayed. This may be set to top, bottom, left or right.
SQL Results Tabs - Defines where the tabs within a single
SQL result window ("Results", "Metadata" and "Info") go relative to the
data being displayed. This may be set to top, bottom, left or right.
General Tab (Output)
Meta Data - Specify the output type (text, read-only
table, or editable table) for meta data displays.
Table Contents - Specify the output type (text, read-only
table, or editable table) for the Contents tab in the Ojbect display.
SQL Results - Specify the output type (text, read-only
table, or editable table) for SQL result displays. (Note that there are
additional limits on what can or cannot be edited in the results of SQL
queries, so setting this to "Editable table" may not result in an
editable output for any given query. See Editing
the SQL Results for details.)
Object Tree Tab
Object Tree Tab (Object Tree)
Load Schemas/Catalogs into object tree - If checked then
the global DB information is automatically loaded into the Object Tree
view of the Database. This is useful for navigating the entire Database
contents, but it may cause an unacceptable performance penalty, e.g.
when using Squirrel for remote access through a low-bandwidth
connection.
Show Row Count for Tables (can slow application) - If
checked then when a table is opened the number of rows in the table is
included in parentheses after the name of the table in the message bar
at the bottom of the Squirrel windows. If not checked, then the number
is not shown. Showing the number may slow the application. The number is
still available by using the "Row Count" tab in the Object view.
Contents - Limit rows [number] rows - If checked then the
number of rows read from the table is limited to the number in the text
box, which may be adjusted as needed. If not checked, then Squirrel will
attempt to read the entire table.
Object Tree Tab (Filters)
Limit Schema Objects using these comma-delimited prefixes:
- Limit the list of objects shown in the Object Tree to only those with
Schema names that start with the entries in this box. If the box is
empty, then no limiting is done and all objects are shown.
Limit Catalog Objects using these comma-delimited
prefixes: - Limit the list of objects shown in the Object Tree to only
those with Catalog names that start with the entries in this box. If the
box is empty, then no limiting is done and all objects are shown.
Object Filter - Limit the list of objects shown in the
Object Tree to only those with names that match the pattern specified in
this box.
SQL Tab
SQL Tab (SQL)
SQL Tab (SQL)
Auto Commit - If checked then all SQL will be
automatically committed after it is executed. If unchecked then you will
be able to use the "Commit" and "Rollback" options on the "Session"
menu.
Show SQL Results MetaData (can slow application) - If
checked then the MetaData associated with the results of an SQL query
will be read and made available to the user. This may slow down Squirrel
operation, espcially when being used over low-speed connections. If
unchecked, then the MetaData will not be avilable for the SQL query
results.
SQL Results - Limit Rows [number] rows - If SQL -
Limit rows is checked then only the number of rows specified in the rows
will be displayed for an SQL query.
Abort on Error - If checked then when executing multiple
commands in a single sequence, if one of the commands returns an error,
then the sequence is aborted at that point. If not checked, then the SQL
commands following the one with the error are executed anyway.
Statement Separator - The character used to separate SQL
statements in the SQL entry area.
Start of Line Comment - The character that specifies that
the line in the SQL entry area is a comment and should not be passed to
the database for execution.
SQL Tab (SQL Entry Area)
Font - The Font button allows you to specify the
font to be used in the SQL entry area.
SQL Tab (SQL History)
Share SQL History - if checked then the SQL history is
shared accross all Sessions. If not, then the history is kept for this
Session only.
Limit SQL History Combo Size [number] - if checked then
limit the number of lines of SQL available in the SQL History Combo box
(pull down list above the SQL command entry area) to the (adjustable)
number given.
Plugins
A plugin is an application written in Java that runs within
SQuirreL. This allows developers to enhance the functionality of
SQuirreL without having to rebuild the SQuirreL application itself. It
also allows you to "pick and choose" the functionality that you want in
SQuirreL. For instance if you don't use Oracle then you don't need the
Oracle specific functionality supplied by the Oracle plugin.
All supported plugins are available in the install jar and can be
installed at the same time when installing SQuirreL, or at any time
afterward. Third-party plugins can be installed by unpacking the archive
file (*.zip, *.gz) into the plugins directory within the SQuirreL
application directory keeping the directory structure. To use the plugin
you will need to restart SQuirreL. The SQuirreL project team no longer
makes individual plugins available for download.
The "Summary" option on the "Plugins" menu will display a dialog
box showing the plugins currently installed.
Update Manager
Once SQuirreL has been installed, the Update Manager feature
allows the software to be updated in place when new releases become
available. Additionally, the installed optional plugins and translations
can be updated, removed or added at any time. This means that it is not
necessary to uninstall SQuirreL and re-install it when a new version
becomes available. To access the Update Manager, in the main Help menu,
select "Software Update" as shown in the figure below:
The following update dialog window is displayed:
To check with the update server for a new release, click the "Check"
button. Clicking on the "Settings" button will display the Update
Manager preferences dialog shown below:
The "Site Type" setting refers to what kind of update site to use and
changes the kinds of configuration items that are required :
- Remote
-
- Server
- the name of the HTTP server to consult for updates (default:
www.squirrel-sql.net)
- Port
- the numeric HTTP port that the server is listening on
(default: 80)
- Path
- the path on the server where the channels are found (default:
updates)
- Channel
- the type of release this is (snapshot gets updated almost
weekly and tends to have new but not thoroughly tested changes.
- Url
- This is a read-only representation of the values given above
in the form of an HTTP URL. This can be placed in a web-browser to see
the release.xml file before updating if desired.
- Test Connection
- This button allows you to check connectivity to the URL
specified by the values above and displays a message indicating
whether or not the connection was successful.
- Local
-
- Local Path
- the local directory containing the update files (release.xml
file is directly beneath this directory)
- Automatic Updates
- Enable Automatic Updates - checking this option cause
SQuirreL to periodically and in the background check to see if updates
have been posted using the Remote/Local settings above.
- How often to check for updates
- (At Startup / Daily / Weekly)
Clicking the "Check" button will display a dialog window with a
summary of available/installed artifacts that can be updated, installed
or removed :
This update summary dialog allows you to see what files will be
downloaded/installed when the changes are applied. An Artifact is simply
a file that can be one of three types. Core artifacts are always updated
to the latest version when changes are applied. However, plugin and i18n
(Internationalization) artifacts can be installed, updated or even
removed from your local installation with the Action column on the far
right. Each row in the table represents a single artifact and non-core
artifacts can have an action applied by clicking on the Action column
cell for that artifact. The drop-down list will have choices to INSTALL,
and REMOVE (if already installed). Selecting NONE results in no action
for that artifact. When core artifacts are automatically marked for
INSTALL, it means there is a new release available, which is also
signified by the top of the dialog which indicates the "Installed"
version id and the "Current" version id (that is, what is "currently"
available for download). In this case any installed artifacts of type
plugin or i18n have choices for INSTALL/REMOVE - that is they must be
either updated or removed so that when core artifacts change, the
version of all installed artifacts stays in sync. It is important to hit
apply changes when finished making changes to artifact actions -
clicking close with abort the update process.
Clicking "Apply Changes" will start the download process, which
retrieves new artifacts (artifacts whose bytesize and checksum haven't
changed will not be downloaded to prevent needless waste of bandwidth).
If you use a proxy to reach the Internet update site
(http://www.squirrel-sql.net:80/updates/...), you will need to configure
those settings in the Global Preferences -> Proxy -> HTTP Proxy
section. You may cancel the download at any time, and the next time the
update process is started, it will resume downloading the remaining
files that weren't previously downloaded. Also, if an error should
occur, you may want to check the log file to see what might cause the
error - and re-start the update process at a later time. Until all
artifacts have been successfully downloaded and verified, the next stage
of the software update (applying the updates after restart) will not
occur.
Once all updated artifacts have been successfully downloaded and
verified, you can re-start SQuirreL and the next stage of updating will
being by asking you if you want to apply the updates.
Logs
SQuirreL writes messages to a file called a log file as SQuirreL is
being used. These messages can provide additional details and clues
about what SQuirreL is doing internally and are very useful for
debugging problems. The main log file that SQuirreL writes to called
squirrel-sql.log, and is located in the user's home directory in a "log"
folder just below the private SQuirreL user preference folder called
".squirrel-sql". You may access this log file at any time from within
SQuirreL be opening the log viewer which can be done in one of two ways:
- Click on the menu sub-item located in "Windows" -> "View
SQuirreL Logs", or
- Click the Monitor icon located in the status bar at the bottom
of the window as shown in the following image:
The log status section (at the bottom of the window in the status bar)
displays a count of how many Errors, Warnings and Info (informational)
messages there are currently in the log. The icon to the left of the
Monitor icon discussed above is the last message display button. It's
color will change from light blue to red any time the last log message
contains an error. This button displays the last message when clicked
and for most errors it also provides a stack trace of the exception
encountered (if there was one). This information can be invaluable for
the developers in tracking down the cause of the error. An example of
this dialog window appears below:
In addition to the three types of error messages covered above, SQuirreL
can be configured to log Debug messages which are even more useful at
times then just the Error, Warning and Info messages. This configuration
is accomplished by editing the log4j.properties file which is located in
the SQuirreL install directory. This file has a line in it that looks
like:
log4j.rootLogger=info, SquirrelAppender
To enable Debug messages to be written to the log, edit this file in
your favorite text editor, changing the above line to:
log4j.rootLogger=debug, SquirrelAppender
The update feature uses HttpClient to manage downloads from the Update
website and this can produce a great volume of debug log messages. If
debugging is enabled according to the above instructions, the following
lines should also be added to log4j.properties at the same time that debug
is enabled:
log4j.logger.org.apache.commons.httpclient=WARN, SquirrelAppender
log4j.logger.httpclient=WARN, SquirrelAppender
Once this file has been saved, then SQuirreL should be restarted to
apply the change.
Menus
File Menu
- Global Preferences
- Displays the Global Preferences
dialog.
- New Session Properties
- Displays the New Session
Properties dialog.
- Dump Application
- This option will dump some internal structures of the
application to a text file along with a dump
of all the currently open sessions. This may be useful for debugging
problems with SQuirreL.
- Exit
- Exit the application after closing all sessions.
Drivers Menu
- New Driver
- Displays a dialog allowing you to specify a new JDBC driver.
- Modify Driver
- Displays a dialog allowing you to modify an existing JDBC driver.
- Delete Driver
- Allows you to delete an existing JDBC driver.
- Copy Driver
- Copies the JDBC driver currently
selected in the Drivers list and displays it as a new driver in the
driver maintenance dialog
- Install Default Driver Definitions
- Copies entries from the default JDBC driver definitions that
ships with SQuirreL to the Drivers List. If the driver definition is
already in the list then the default one will not be copied.
Aliases Menu
- Connect
- Display a connection dialog allowing you to connect to the specified alias.
- New Alias
- Displays a dialog allowing you to specify a new alias.
- Modify Alias
- Displays a dialog allowing you to modify an existing alias.
- Delete Alias
- Allows you to delete an existing alias.
- Copy Alias
- Copies the alias currently selected
in the ALiases list and displays it as a new alias in the alias
maintenance dialog.
Plugins Menu
- Summary
- Displays a dialog showing the installed plugins.
Session Menu
- Session Properties
- This option will display the Session Properties dialog
allowing you configure your current session. More.
- Dump Session
- This option will dump some internal structures of the current
session to a text file. This may be useful for debugging problems with
SQuirreL.
- Refresh Tree
- This option will refresh the object tree.
- Run SQL
- This option will run the current SQL in the SQL entry area. More.
- Commit
- This option will commit the current SQL transaction. This is
only applicable if "Auto Commit" has been turned off in the Session Properties.
- Rollback
- This option will rollback the current SQL transaction. This is
only applicable if "Auto Commit" has been turned off in the Session Properties.
- Goto Previous Results Tab
- This option will select the Results Set tab to the left of the
currently selected one. If the leftmost tab is currently selected then
the rightmost tab will be selected.
- Goto Next Results Tab
- This option will select the Results Set tab to the left of the
currently selected one. If the rightmost tab is currently selected then
the leftmost tab will be selected.
- Show Native SQL
- This option will append the native SQL for the current JDBC
SQL into the SQL entry area. E.G. If you enter the following in an
Oracle session
select * from table1 where entered_date = {d'2002-12-01'}
and take this option then the following will be appended to the SQL
entry area.
select * from table1 where entered_date = TO_DATE ('2002-12-01', 'YYYY-MM-DD')
- Reconnect
- Close the current connection to the database and reopen it
using the same user name and password.
- Close Session
- Close the current connection to the database and close the
session window.
- Close All SQL Result Tabs
- Close all SQL results displayed in the tabbed folder for the
current session.
- Close All SQL Result Windows
- Close all SQL results windows "torn off" from the tabbed
folder for the current session
- Limit cell edit WHERE clause size
- This allows you to select specific columns to use as the key
field when updating the DB after you edit a cell in a table. The
purpose of thi is to reduce the size of the WHERE clause, which
normally includes all field in the table. For tables with many columns,
or with columns containing lon data entries (not including BLOB/CLOB
fields, which are handled differently) the automatically generated
WHERE clause may exceed the DBMS length limitation You may move column
names between the "use columns" and "not use columns" boxe by selecting
the names and using the arrow buttons to move them to the other box You
must leave at least one column in the "use columns" box, and the column
in the "use columns" box must be sufficient to uniquely identify each
row in the table, e.g. the primary key fields for the table The "Reset"
button restores the column name lists to the way they were when you
opened the window Remember to click "OK" when you are done If you
define a set of columns to use in the WHERE clause and then edit some
other column, SQuirreL will warn you that "1 duplicate row" is about to
be created. Since you have told SQuirreL to not look at the edited
column it does no recognize that the new data is different than the old
data so it gives you a warning. In this case there is no problem and
you should just tell SQuirreL to go ahead with the update.
Windows Menu
- View Aliases
- Display the list of aliases that
define a connection to a database.
- View Drivers
- Display the list of drivers.
- View SQuirreL Logs
- Display the execution logs for SQuirreL.
- Tile
- Tile the open session windows.
- Cascade
- Cascade the open session windows.
- Maximize
- Maximize the open session windows.
- Close All Sessions
- Close all existing sessions.
Help Menu
- Help
- Displays this Help File.
- FAQ
- Displays the Frequently Asked Questions file.
- Change Log
- Displays the development history of SQuirreL.
- Licence
- Displays the licence for SQuirreL.
- About
- Displays the About Box for SQuirrel.