[ Master documentation index ]
Introduction
The editing support in DbVisualizer Personal is used to insert, update
or remove single rows in a database table. Editing is performed using
two different editors:
- Inline Editor
- Form Editor
The Inline Editor is convenient in situations when fast edits of single
columns need to be made. The Form Editor presents all columns in a form
and some users prefer it since it is easier to get an overview of the
data.
The following figure shows what buttons in the Data tab and in a result set grid that are used specifically for editing.
Figure: The buttons in the Data tab used to control the inline and form editors
Permissions
All of the insert, update and delete requests performed by the inline
and form data editors may be confirmed before being executed by the
database server. Specify in Tool Properties->Permissions
the confirmation state. The default behavior is that delete operations
must be confirmed while insert and update need no confirmation.
Features that
support editing
Editing of table data can be performed in the Database Objects->Data tab or in
the results from an SQL statement in the SQL Commander.
There are a few rules that must be fulfilled in order to enable editing
in the SQL Commander:
- It is a result set
- The SQL is a SELECT command
- Only one table is referenced in the FROM clause
- All current columns exist by name (case sensitive) in the
identified table
The editing tool bar is hidden if these rules are not met.
Edits might be denied
The editing features in DbVisualizer ensure that only one row in the
table will be affected by update and delete requests. This prevents
the user from doing changes in one row that might also silently affect
the data in other rows. DbVisualizer uses the following strategies to
determine the uniqueness of the edited row:
- Primary Key
- Unique Index
- Selected Columns
- All Previous Values
The Primary Key concept
is
widely used in databases to uniquely identify the key columns in
tables.
If the table has a primary key then DbVisualizer will use it. There
are
situations when primary keys are not supported by a database or when
primary keys are supported but not used. If no primary key is defined
then DbVisualizer will check if there is a unique index. If there are
several unique indices then DbVisualizer will pick one of them. So why
not only try to
identify
the row by all its previous values? The reason Selected Columns is searched before
checking All Previous Values
is
because not all data types are allowed to be used in a where clause.
Typical
examples are BLOB and CLOB data types. The Selected Columns check simply uses the
selected columns in the inline editor or checked columns in the form
editor to find out if the values identify a unique row. If that check
fails then All Previous Values
is searched.
The following dialog appears when none of these strategies can uniquely
identify the row.
Figure: Warning dialog when a unique row could not be identified
Commit
DbVisualizer issues an implicit database commit after each successful
edit operation (the value of the Auto
Commit property in Tool Properties is bypassed during editing).
The commit request is per database connection and it commits all
pending updates
even
those that have been executed in the SQL Commander.
The following example explains the effect of the implicit commit:
- The Auto Commit property
is disabled in Tool Properties.
- An SQL statement that deletes some rows is executed in the SQL
Commander but it is not explicitly committed.
- We now go to the Data tab for a table and edit the value of a
column.
- Once the update in the grid is performed the update is committed.
This commit will also commit the delete statement that was executed
earlier in the SQL Commander. The reason is that all commits
are performed per Database Connection.
Error Log
If the insert, update or delete request is successfully executed by the
database then the row in the grid will be updated accordingly. There
are
however situations when errors occur during the database execution
phase. These messages are presented in the execution log.
Figure: Error Log view
The log keeps information of all edits that have been executed in both
the inline and form editors. It presents not just failed operations
(red
color) but also all successful edits. All error messages that appear in
the log are produced by either the JDBC driver or the database. Refer
to
the driver or database documentation for explanation of the error
messages.
Binary data/BLOB and
CLOB
Binary data/BLOB and CLOB data can only be edited in the form editor.
Read more about it in Editing Binary/BLOB data and
CLOB.
Inline Editor
The inline editor is handy when fast edits need to be made.
The editor is simple to use since it is activated by typing characters
in the cell (column in a row) that is to be modified. The inline editor
is data type aware and checks that the entered values are valid for each
column's data type. Any errors are reported in the error log. Only
those
cells that have been modified (indicated by a yellow color) during the
editing session will be propagated to the database.
The inline editor keeps all edits that have been made in a single row
until the edit is implicitly committed by selecting another row in the
grid or by using the update tool bar button (see below). All other
operations such as selecting another table in the Database Objects tree will silently
revert any edit.
The following tool bar buttons are used to control the inline editor:
Figure: Tool bar buttons to control the inline editor
Description of the buttons (from left):
- Insert row
- Duplicate row
- Delete row
- Perform the current edit
- Revert the currently edited row
A row is edited by typing characters in a cell. All cells that have been
edited are indicated by a yellow background color. Only these columns
will be updated once the final SQL is executed to perform the change.
All cells irrespective of whether they have been edited or not are highlighted
with a yellow border to indicate which row is being edited.
It is not possible to edit binary
data, BLOB and CLOB values in the inline editor. Use the form editor to
manipulate values for these data types.
Insert a new row
To insert a new row just press the Insert
row tool bar button. All columns in the table that don't allow
nulls are by default switched to the yellow background color. Now
just start typing in the cells to set the values of the new row.
Figure: Initial
view of the inline editor when a new row is about to be edited
If a value is entered that is not valid for a cell then an error dialog
is displayed. The color of the invalid cell is at the same time
switched
to red.
Figure: Error dialog when the data type is invalid for a column
Follow the instructions in the dialog to either correct the value to
match the data type of the column or revert to its original value.
If the newly added row is the only row in the table then there is no
other row to select in order to perform the insertion. You must in this
situation explicitly press the Perform
the current edit button in the tool bar.
Update an existing row
To update the value of a cell then just select the cell and start
typing. The same checks as when inserting a new row are done here as
well. To perform the edit just press the Perform the current edit button in
the tool bar or select a cell in another row.
DbVisualizer use three different strategies
to determine the row that will be updated. In order to let
DbVisualizer
use Selected Columns then
first
edit the cells that should be updated. Now select each cell that should
be
part of the selected columns list.
Press the Perform the current edit
button in the tool bar to let DbVisualizer use the selected columns in
the final where clause.
Delete a row
To delete a row then select at least one cell in the row to be
removed. DbVisualizer will use one of the strategies in order to determine the row
that will be deleted. Selecting one or several cells in the row will
form the where clause that
optionally will be used if the Selected
Columns strategy is used to identify the row.
The default behavior when deleting a row is that it has to be
confirmed. You may modify whether the confirmation dialog should appear
or not in the Permission category in Tool Properties.
Figure:
Confirmation dialog when deleting a row
Cell pop up menu
The cell pop up menu is active while editing a cell value and it is
displayed using the right mouse button.
Figure: Cell pop up menu
The list of operations in the cell pop up
menu is different depending on what data type the column is. Common for
all data types are the Set to Null
and Uncheck Column operations.
The Uncheck Column is used to remove the editing state of the cell
which
means that it will not be included in the update of the row (the yellow
background color is removed).
Remember that the format of time stamp, date or time values must match
the format settings in Tool Properties.
Insert current Timestamp (can
also be Insert current Time
and Insert current Date) menu
choice is
valid only for the appropriate data types and simply inserts the
current
time stamp. The format of these values matches
the format settings in Tool Properties.
Form Editor
The Form Editor enables editing of data in a form based dialog. This
editor is useful when inserting new rows and when it is important to
get
a more balanced overview of all the data. The form editor and the inline
editor are
based on the same set of rules and the visual appearance is similar.
The following tool bar buttons are used to start the form editor:
Figure: Tool bar buttons to control the form editor
Description of the buttons (from left):
- Insert row
- Edit row (update, delete or insert copy)
An alternative way to start the form editor in edit mode is to
double click on the row header.
Form editor controls
The following explains how the form editor is organized and what
controls are available.
Figure: The form editor
The form editor is composed of two main views (at the bottom of the
dialog), they are the Edit and
Log views. The edit view is as
its
name implies the place where the actual edits take place. The log view
maintains a list of log messages for all edits that have been performed.
The log view is displayed automatically if an error occurs during the
database operation.
The tool bar buttons are used to Insert,Update and Delete the current row. (Only the
insert button is enabled if the form editor has been launched to insert
a new row).
The Wrap Lines check box is used to control whether long values should be wrapped automatically.
The Reload Data Grid check box
determines whether the Data tab grid should be automatically re-loaded
once the form editor is closed. If this box is disabled then make sure
to reload the data tab grid manually to reflect the changes that have
been made in the form editor.
Row Values
The Row Values section in the
form editor lists all columns and values (fields) as they appeared in
the data tab grid when the form editor was launched (i.e if a column
was
manually removed from the grid then it wont show in the form
editor).
Each field is composed of a label which is the column name and the
value. Any primary key columns are indicated with a key-like icon to
the
left of the label.
The check box between the label and the value indicates whether the
column will be part of the SQL that is finally executed to perform the
edit.
The checked state is automatically enabled if the field of the value is
being edited and the color of the value field is automatically switched
to yellow to indicate what fields will be part of the database
request.
Additional information about a column is listed below the list of
fields. This information is updated when the pointer is in a value
field.
All text fields allow data to be entered in multiple lines (press the
enter keyboard button). Boolean and Bit fields are managed by toggle
buttons. The state of these fields can be either true, false or null.
All fields except boolean fields have a pop up menu associated with
them
similar to the one in the inline editor. For all fields it contains the
Set to Null operation. For
time stamp,
date and time columns it also contains Insert
current Timestamp, Insert
current Time or Insert current
Date. The formats of time stamp, date and time are the same as
specified in Tool Properties.
Remember to use the same format for time stamp, date and time fields as
specified in the formats section in Tool Properties.
Each of the fields limits the amount of data that can be entered.
Entering more characters than allowed will be denied. Some JDBC drivers
report invalid column lengths such as 0 or a negative size. The form
editor tries to figure out if the length is invalid and adjust the
width
accordingly.
Insert a row
The following dialog is displayed when choosing to insert a new row
using the Form Editor.
Figure: The form
editor as it appears when a new row is about to be edited
The form editor automatically enables the checked state for all columns
that do not accept nulls. All other fields are unchecked with the value
of (null) (or whatever the text representation of null has been set to
in Tool Properties). Now specify the values accordingly and press the Insert button to perform the
insertion. The form editor is closed if the execution was successful.
The Data->Insert (Keep Window)
is an alternate insert operation that is used to insert the new row.
The
difference is that the form editor dialog is kept. This is useful if
successive inserts need to be made.
Edit a row (update,
delete or insert copy)
Do the following in the Data tab grid to edit a row using the form
editor:
- Double click on the row header for the row that is to be edited
- Select one or more cells in the row and press the Edit row in a form tool bar button
The first choice is useful if the table has primary keys or if the
database table accepts an update request based on all current
values for the row. The second choice is useful when the table has
primary keys or when one must be able to select the columns (cells)
that will form the Selected Columns
where clause. See Edits might be
denied for more information.
Figure: The form editor as it appears when edit of an existing row has
been requested
Update the row
All fields are automatically unchecked when the form editor appears in
edit mode. Change the desired values and press the Update button to perform the update
request.
The form editor is closed if the update operation is successful.
Delete the row
To delete the current row in the form editor just press the Delete tool bar button. A
confirmation dialog might be displayed (the appearance of this dialog
can be specified in Tool Properties) in which the deletion must be
confirmed.
Figure:
Confirmation dialog when deleting a row
The form editor will be closed if the delete operation is successful.
Insert a copy of a row
The form editor allows a new row to be inserted based on the values
that are currently in the editor. Make sure to set each fields check
state or use the Edit->Check All Values
menu choice to enable the checked state for all fields. Use the Insert tool bar button or Data->Insert (Keep Window) menu
operation to perform the insertion.
Import from File
The File->Import From File
operation
can be used to load any file into the currently selected field.
Importing from file is exactly the same as manually entering the data
i.e the max field sizes are considered and it might not be possible to
load a file for which content does not fit into the actual field.
Imported binary data with a recognized binary viewer will be displayed
accordingly.
Export from File
The File->Export To File is
used to export the content of any field including binary data to a file.
Editing Binary data/BLOB and CLOB
There are a few constraints specifically for editing of BLOB and CLOB
data types in DbVisualizer:
- They can only be edited in the form editor
- A primary key is recommended to successfully update these data
types
- Note: A primary key is required for update of BLOB and CLOB
in Oracle
Binary data in DbVisualizer is
the generic term for several common binary database types:
- LONGVARBINARY
- BINARY
- VARBINARY
- BLOB
Read the following sections about CLOB and Binary/BLOB data for
specific information
CLOB
CLOB data appears (apart from other data types) in a multi line text
field in the form editor. Data can be entered manually or imported
using the File->Import From File
operation.
Figure: The CLOB text editor
Binary data/BLOB
Binary data can by its nature not be manually edited in
DbVisualizer, it can only be
imported from a file.
The form editor recognizes some common formats and presents them in an
appropriate viewer.
GIF, JPEG and PNG
viewer
Figure: Binary
data viewer for common image formats
Serialized Java
objects viewer
Figure: Binary
data viewer for serialized Java objects
Hex/Ascii viewer
The generic hex/ascii viewer is used if the data format is not
recognized.
Figure: Hex/Ascii
viewer for unrecognized binary data
Copyright © 2006 Onseven Software AB. All rights reserved.
