Introduction
The table data editing feature mimics how editing is performed in
standard spreadsheet applications; just click a cell value and edit.
Edits are saved in a single database transaction which ensures that all
or no changes are committed. The editing feature supports saving binary
and large text data and it even presents common data formats in their
respective viewers, such as image viewer, PDF, XML, HEX, etc.
A block of data can easily be interchanged via standard copy and
paste
operations between the grid editor and other applications, such as
Microsoft Excel, StarOffice and OpenOffice.
Editing is primarily performed in the grid editor. For some data, such
as
binary or large formatted text data, editing in the grid
editor is not optimal, so for these situations, we recommend that you
to use the form or
cell data editors. The form editor presents a single row of data in a
separate window, organized as a form with the column name in the first
column and the data in the second column. All editing capabilities in
the grid editor are also available in the form editor. The cell editor
is used to edit a single cell value in a separate window. This is
useful when editing formatted text data or to browse binary data.
Most of the editing functions have a key binding assigned. Check the
right-click menu in the data grid to find out what they are.
Features
that
support editing
Editing of table data can be performed in the
Database Objects->Data tab and in
a result set generated by a SELECT statement in the SQL Commander.
There are a few conditions that must be fulfilled for editing to be
enabled 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.
Update
and Delete must match one table row
The editing features in DbVisualizer ensure that only one row in the
table will be affected by update and delete edits. This prevents the
user from doing changes in one row that might also silently affect data
in other rows. DbVisualizer uses the following strategies to determine
the uniqueness of the edited row:
- Primary Key
- Unique Index
- Manually Selected Columns
The Primary Key concept is widely used in databases to uniquely
identify the key columns in tables. If the table has a primary key,
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, DbVisualizer will check if
there is a unique index. If there are several unique indexes,
DbVisualizer will pick one of them. If there is no primary key or any
unique indexes defined for the table, you need to manually choose what
columns to use. The key column chooser is automatically displayed if
the key columns can't be determined automatically.
Edit
Multiple Rows
The grid editor supports editing multiple rows and saving all changes
in one database transaction. Edited rows are indicated with an icon in
the row header:
Cell(s)
in
the
row
has
been
edited
Row
is
new
Row is duplicated from
another row
Row is marked for deletion
(edit is not allowed)
Edits are saved when explicitly saving changes via the
Edit Table Data->Save Edit(s)
right click menu choice or via the
Save
button in the tool bar.
Data
Type checking
When leaving an edited cell the new value is validated with the data
type for the column. If there is an error, the following dialog is
displayed.
Figure: Data type error
New
Line and Carriage Return
If a cell in the grid editor or form editor contains new line, carriage
return or tab
characters, these are not visually represented in the grid. Instead a
warning will be displayed whenever you try to edit such value:
Figure: Warning when editing a
value containing a carriage return, line feed
and tab characters
You may chose to edit the value in the
Cell
Editor,
which we recommend, as the control characters will then be preserved.
The other choice is to edit the value anyway and risk loosing the
control characters. This is
not
recommended.
The
Cell Editor is a
designated multi-line text editor suitable for editing large chunks of
text:
Figure: Editing multi lined text
data in the cell editor
Grid
Editor
The grid editor tool bar is decorated with buttons for editing and the
right-click menu contains all related operations.
Figure: Toolbar buttons to
control the grid editor
Cells that have
been
edited are indicated by a yellow background color. Only these cells
will be updated (part of the final SQL) when the changes are saved in
the database. To make sure a cell (column) is not part of the final SQL
you need to select the cell and chose the
Edit Table Data->Undo Edits in Selected
Cell(s) right click menu choice.
All cells in the edited row are highlighted
with a yellow border to indicate that some cell(s) in the row has been
edited.
Insert
row
To insert a new row, choose the
Edit
Table
Data->Insert New Row right-click menu choice or press the
Insert toolbar button. The new row
will be inserted after the selected row or at the top if no row is
selected. You can now start editing the cells in the grid or open the
form editor to insert values.
Update
row
To update a row, just double-click in the target cell and modify the
value.
Delete row(s)
One or multiple selected rows are marked for deletion via the
Edit Table Data->Delete Row(s)
right-click menu choice or by pressing the
Delete toolbar button. Each deleted
row will be highlighted with a red background color and no further
editing of the content is allowed.
Deleting a row that has been updated will automatically undo all edits
and show the original values. This is done so that it is obvious which
data will be deleted. Deleting a row that has been inserted (or
via duplication) will be removed from the grid.
Duplicate
row(s)
Duplicate a row or several rows by selecting the cells in the rows that
should be duplicated, then choose
Edit
Table
Data->Duplicate
Row(s) or press the
Duplicate
tool bar button. All cells
in the new row will be marked as being edited (yellow background
color). The exception is any
Auto
Increment/IDENTITY field, which will be assigned a value by the
database.
Copy/Paste
Copying selected cell values is accomplished via the
Copy Selection right-click menu
choice. The data on the clipboard may then be pasted either into
DbVisualizer or any external application. The copy and paste operations
in the grid editor are defined by the
Grid->Copy
category in Tool Properties. The default setting for column and newline
delimiters are sufficient for most uses.
Paste data from Excel
and OpenOffice
The grid editor supports pasting data from the major spreadsheet
applications. The grid editor support pasting single data as well as
block of data.
| Copy from Excel |
|
Paste into DbVisualizer Grid |
A single cell is copied in Excel
|
|
The selected cell is pasted into
one selected target cell
|
A single cell is copied in Excel
|
|
The selected cell is pasted into
multi selected target cells
|
A block of cells is copied from
Excel
|
|
The block is pasted into the
selected region
|
A block of cells copied from Excel
|
|
The block is pasted into a non equal number of target cells
|
Insert
pre-defined values (Set Selected Cells)
The
Edit Table Data->Set Selected
Cells right-click menu choice or the
Set Selected Cells tool bar button
lists a few pre-defined functions that will fill the selected cells
with data.
Figure: Set Selected cells functions
Use these to insert data into the selected cells. Note that the target
column type must accept the selected value type; nothing will happen if
if you choose, for example,
"Insert
Current
Time" for a DATE data type cell.
Undo Edit(s)
The
Edit Table Data->Undo Edit(s)
operation is used to revert all edits in the selected cell(s).
Reverting all cells in a row that are marked as
Insert or
Duplicate will remove the complete
row from the grid while a
Delete
marked row is cleared from its delete state. Undoing updated cells
simply reverts the changes to the original values.
Key Column(s)
Chooser
Normally database tables have a primary key or at least one unique
index. If this is the case, editing is no problem. If there is no way
to
uniquely identify rows in the table, you need to manually define what
columns DbVisualizer should use. While saving the changes DbVisualizer
will check that there is a way to identify unique rows in the table. If
it cannot be accomplished, the following window is displayed.
Figure: Key Column(s) Chooser
The
key column chooser can be manually opened via the Edit Table Data->Key Column Chooser
right-click menu choice.
If the database request to save the edits cannot uniquely identify the
single row that should be changed, the error dialog is displayed and
the
editing state is kept for that row in the grid editor.
Preview Changes
You may preview the SQL statements that will be executed when choosing
to
Save the edits. It is
displayed via the
Edit Table
Data->SQL Preview right-click menu choice.
Figure: SQL Preview
(The listed SQL statements may not be 100% compliant as the save
process use variable binding to pass values to the database).
Saving
Changes
To save table data edits, select the
Edit
Table
Data->Save
Edit(s) right-click menu choice or click the
Save toolbar button. If there
are rows that have been edited or deleted, these are first checked
so that there is only one database table row affected by each edited
row. If this pass is successful, DbVisualizer will save the changes in
the table. The progress is displayed in the status bar and Save may be
interrupted by pressing the
Stop
button in the toolbar. While save is in progress, no other operation
may
be performed in DbVisualizer, i.e., the rest of the application is
locked.
Transaction
Control
DbVisualizer use the physical root connection for the actual database
connection when saving table data. Once save is requested, DbVisualizer
will
implicitly set the auto commit state to off and reset it to what it was
prior to requesting
save when saving is completed. If the
Use
Single
Shared
Physical
Database
Connection is enabled in connection
properties,
DbVisualizer will check whether there are any uncommitted updates in
the database when save is requested. If there are uncommitted changes
you must first commit or rollback these changes before save is started.
Saving table data edits are batched in a single transaction. There is
no DbVisualizer restriction on the number of edits that may be saved in
a single save
operation, but the database server may put either explicit or implicit
restrictions. The connection property
Physical
Connection->Transaction->Commit
Batch
Size specifies how
many edits should be performed in the database table until commit is
automatically initiated. If you, for example, are saving 150 edited
rows and an
error occurs while saving the 121:st row, then the first 100 rows will
have been committed and the rest are left unchanged. The visual
indication in the grid after a incomplete save operation is that rows
that weren't saved keep their original editing state indicator. Rows
that were saved properly are indicated with a green checked cylinder
icon.
Figure: Saved rows state
The cylinder icon with the green check mark indicates that the row was
saved in the database table. Normally the grid is reloaded after a
successful save operation and there is no cylinder icon displayed. It
only appears if the save operation was partly successful. Rows that
weren't saved are still represented with the original editing state
icon and you may request save one more time.
Rows that have been
properly saved (indicated with the cylinder icon) cannot be edited
until all rows are saved properly or the grid is reloaded.
Permissions
All of the insert, update and delete requests performed by the grid
editor may require confirmation before being executed by the
database server. Specify in
Tool
Properties->Permissions which operations should require
confirmation. The default behavior is that delete operations
must be confirmed while insert and update need no confirmation.
Errors
If a database error occurs while saving changes, details about the
errors are displayed in a
window along with the actual SQL that was executed.
Form Editor/Viewer
The
Form Viewer is available
in the right-click menu (
Browse Row in
Form) for all grids in DbVisualizer. It is used to
browse information and to present binary data in viewers.
The
Form Editor adds editing
capability to the form viewer. 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 "rotate" the data in one row and presents it as a
vertical form with the column name as a label. All edits made in the
form
editor are reflected in the grid with the edited state icon being
updated along with new values. Saving edits in the database is always
done with the
Save Edit(s)
control in the original grid editor.
Open the form editor via the
Edit Row
in Form right-click menu choice, via the button in the toolbar
or by double-clicking the row number header.
Figure: A row in the grid
Here is the same row as selected in the previous screen shot displayed
in the row form window.
Figure: Form viewer
The
Key field optionally
contains an icon for primary key columns and the
Name field corresponds to the column
name in the grid.
None of Key or the Name fields can be edited. You can edit the values
in the form in
the same way as you edit values in the grid editor.
The form viewer presents images as thumbnails. The size of these is
controlled in
Tool
Properties->Form Viewer->Image Thumbnail Size. To see the
original size of an image, open the cell in the cell viewer either
by selecting
Edit in Cell Window
in the right-click menu, the toolbar button or by double-clicking on
the
image.
Cell Editor/Viewer
The
Cell Viewer is available
in the right-click menu for all grids in DbVisualizer. It presents the
data for a single cell (column in a row) in a window. If the data is
of a recognized type, it is presented by a corresponding viewer:
- Image viewer
- XML viewer
- Serialized Java object viewer
- Hex viewer
- Text viewer
The cell viewer allows saving data to a file and to print it.
The
Cell Editor adds editing
capability to the cell viewer. You may import data from a file or
manually change the text in a text editor.
Opening an image in the cell editor will display the following window.
Figure: Cell editor
The tabs at the top shows the available viewers for the current data.
When you load a file into the cell editor, the tabs may change to
reflect the newly loaded data. To nullify the cell value, press the
Set Value to NULL toolbar
button.
Read more about binary and formatted text data in the following
chapters.
Binary/BLOB
Due to the nature of binary/BLOB and CLOB data, cells of these types
can only be fully
modified and viewed in the cell editor. (There is partial support in
the form editor to view image data and to load from file). Editing
binary data can be done by importing from a file or via the text editor.
Binary data in DbVisualizer
is
the generic term for several common binary database types:
- LONGVARBINARY
- BINARY
- VARBINARY
- BLOB
Image Viewers
The image viewer supports displaying the full size images for the
following formats:
PDF Viewer
The PDF viewer shows Portable Document Format documents in a viewer.
Figure: PDF viewer
XML Viewer
The XML Viewer shows the content of an XML document in a tree with
color highlighting. You can switch to an editable text view by pressing
the
Edit value in text editor
toolbar button.
Figure: XML viewer
Serialized
Java Objects Viewer
The serialized Java object viewer renders a java object in a tree
style.
All aspects about the object may be browsed.
Figure: Binary
data viewer for serialized Java objects
Hex Viewer
The generic Hex/ASCII viewer shows the hexadecimal representation of
every byte
in the data and its text representation. This is the default viewer for
unknown data.
Figure:
Hex/ASCII
viewer
Large text data/CLOB
Large text data and CLOB data types are typically edited in the multi
line text editor. For formatted data (that includes new lines), the
default editor is useful. If editing a large chunk of non-formatted
data,
enable the
Use Wrapped Editor
setting and DbVisualizer will then automatically wrap the text for easy
editing.
Figure: The text viewer and editor
Import
from File
Importing data from a file can be done in the form and cell editors.
Imported binary data of a recognized type is displayed by the
corresponding binary viewer. Import is supported for both binary and
text data.
Export to File
Export can be made in the grid, form and cell editors for binary and
text data.
Copyright © 2010 DbVis Software AB. All rights reserved.
Master documentation index