[ Master documentation index ]
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, 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, StartOffice 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 contains new line, carriage return or tab
characters, these are not visually represented in the grid. These
characters instead appear as \n (newline),
\r (carriage return) and \t (tab) when you edit the cell
value.
Figure: Carriage Return, Line Feed
and Tab characters
The figure show identical values in the Name column. The second is in
edit mode and the special characters are visible, while in the first
non
editing row they are not visible.
A better solution for editing multi-lined data is with the cell editor,
as it handles these characters more naturally:
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 columns
will be updated when the changes are saved in the database.
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 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 field, which should 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 function. Nothing will happen if
choosing for example "Insert Current
Time" into a DATE data type.
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 the Save toolbar button. If there
are rows that has 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 finished. 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 restriction of 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 for example saving 150 edited rows and an
error occur 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 non complete 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 indicate 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 appear 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.
Note: Rows that has 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 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.
Errors
If a database error occur 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 for all grids in DbVisualizer. It is used to
browse information and to present binary data in viewers.
The Form Editor add editing
capability in 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 in a
form with the column name as 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 in the 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 optionally show with
an icon what columns are primary key columns and the Name is the column name in the grid.
None of Key or the Name can be edited. Edit the values in the form in
the same way as these are edited in the grid editor.
The form viewer presents images as thumbnails. The size of these are
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 present the
data for a single cell (column in a row) in a window. If the data is
recognized it will be presented in a corresponding viewer:
- Image viewer
- XML viewer
- Serialized Java object viewer
- Hex viewer
- Text viewer
The cell viewer allows saving data to file and print.
The Cell Editor adds editing
capability in 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:
Figure: Image 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
tool bar button.
Figure: XML viewer
Serialized
Java Objects Viewer
The serialized Java object viewer render 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 hex 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 with a recognized binary viewer will be displayed
accordingly. Import is supported for binary and text data.
Export to File
Export can be made in the grid, form and cell editors for binary and
text data.
Copyright © 2007 Onseven Software AB. All rights reserved.
