Editing Table Data

With the DbVisualizer Pro edition, you can edit table data in the Data tab grid; 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.

Opening the Data tab

To open the Data tab for a table:

1. Locate the table in the Databases tab tree,
2. Double-click the table node to open its Object View tab,
3. Open the Data sub tab.

Each column width is automatically resized to match the column width, including the column header, by default. You can disable this behavior in the the Tool Properties dialog, in the Grid category under the General tab.

If Auto Resize Column Widths is enabled, the Max Column Width setting can be used to limit the column width so that an extremely wide column does not take up all space.

Editing Data in the Grid

To edit a column value:

1. Select the column cell,
2. Type the new value, or double click to edit the current value,
3. Click the Save toolbar button to update the database.

You can also use the Set Selected Cells drop down menu to set a number of column values to things like null or the current date or time.

To add a new row:

1. Select the row above where you want to insert the new row,
2. Click the Add Row toolbar button,
3. Enter values for the columns,
4. Click the Save toolbar button to update the database.

To duplicate a row:

1. Select the row you want to duplicate,
2. Click the Duplicate Row toolbar button,
3. Edit at least the key column(s) value(s),
4. Click the Save toolbar button to update the database.

To delete one or more rows:

1. Select the rows to delete,
2. Click the Delete Rows toolbar button,
3. Click the Save toolbar button to update the database.

If you change your mind, you easily can undo edits:

1. Select the cell(s) you want to revert,
2. Click the Undo toolbar button.

Reverting all cells in a row that are marked as Insert or Duplicate removes 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.

Copy/Paste

You can copy selected cell values with the Copy Selection right-click menu choice or the corresponding key binding (Ctrl-C or Command-C by default). The data on the clipboard may then be pasted either into DbVisualizer or any external application. The column and newline delimiter used for copy and paste operations in the grid editor are defined by the Copy Grid Cells in CSV Format settings in the Grid category in the Tool Properties dialog, under the General tab. The default setting are sufficient for most uses.

The grid editor supports pasting data from the major spreadsheet applications, such as Excel and OpenOffice. The grid editor supports pasting single data as well as block of data. Copy/paste of binary data is transparent between grids or in the same grid. Binary files may also be copied in an external application and pasted in a cell in DbVisualizer (target cell must be a binary type).

Copy from spreadsheet		Paste into DbVisualizer grid
A block of cells is copied		The block is pasted into the selected region
A block of cells is copied		The block cannot be pasted into a different number of target cells
A single cell is copied		Paste into selected target cell
A single cell is copied		Paste and fill the single column target selection
Multiple cells in a single row is copied		Paste and fill the target selection

Updates and Deletes Must Match Only One Table Row

When you update or delete rows, DbVisualizer ensures that only one row in the table will be affected. This is to prevent changes in one row to silently affect data in other rows. DbVisualizer uses the following strategies to determine the uniqueness of the edited row:

1. Primary Key,
2. Unique Index,
3. 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 uses 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 checks if there is a unique index. If there are several unique indexes, DbVisualizer picks 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.

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 checks that there is a way to identify unique rows in the table. If it cannot be accomplished, the following window is displayed.

The key column chooser can also 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, an error dialog is displayed and the editing state is kept for that row in the grid editor.

Editing 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)

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.

New Line and Carriage Return

If a cell in the grid or record editor contains new line, carriage return or tab characters, these are visually represented in the grid, but an information dialog will be displayed whenever you try to edit such a value:

Grid viewers

Data in grids can be presented in three different viewers:

• Value viewer
  A viewer for a single selected cell in the grid. Based on the data type, the viewer may be a text, image, XML, JSON, serialized Java or a hex viewer.
• Record viewer
  The record viewer shows all column values in the selected grid row in a transposed form view with all columns as rows.
• Aggregates viewer
  Shows various aggregation metrics for the current selection in the grid.

The default position of these viewers is in a tabbed layout side-by-side with the grid. In Tool Properties under the General / Grid / Grid Viewers category, the layout can be changed to instead show these in separate windows rather than next to the grid.

Value viewer

The Value viewer shows the selected grid cell value in a content-type-specific viewer. These are the available viewers:

• Text
• XML
• JSON
• PDF
• Image (GIF, JPEG, PNG, TIFF, BMP, SVG)
• Serialized Java Object
• HEX
• Date, Time, and Timestamp (with choosers when editing)

The value viewer is opened with one of:

• Click the grid toolbar button:
• Choose Value Viewer in the grid right-click menu
• Double-click on a CLOB or binary cell
• Double-click on a text cell having newline or tab character symbols
• Double-click on a cell that is truncated due to the Max Chars setting in Tool Properties under the General / Data Formats category

The following is an example of what the Value viewer to the right shows:

In this example, a binary cell is selected in the grid. The value viewer then detects the type of content and shows the matching viewer, in this case a text editor.

The left-most eye icon shows when clicked a list of all viewers:

Only viewers that are supported by the selected cell are enabled. The Show Metadata entry shows a pane at the bottom of the value viewer with type information for the cell. The Auto-detect Viewer entry will, when selected, automatically show, for example, XML and JSON in a graphical viewer rather than in a text editor.

The value viewer supports dropping files on it.

Check the General / Grid / Grid Viewers category in Tool Properties for settings related to the value viewer.

Editing in the Value viewer

If editing is supported, edits made in the grid are directly propagated to the value viewer and vice versa.

Record viewer

The Record viewer shows the selected grid row in a transposed ("rotated") view with all the original columns as rows. This is a convenient viewer for grids having a lot of columns.

The record viewer is opened with one of:

• Click the grid toolbar button:
• Choose Record Viewer in the grid right-click menu
• Double-click the row number to the left of the grid data

The following is an example of what the Record viewer to the right shows:

The Key field shows an icon for primary key columns, and the Name field corresponds to the column name in the grid. The Value field shows the value for each column. For CLOB and binary data types, the value field shows an icon and the size of the data. To see the data for these values, double-click on the cell to open the value viewer in a separate window or click the in the toolbar. The format of the values can be adjusted in Tool Properties under the General / Data Formats category.

The navigation buttons in the toolbar allow you to navigate between the rows in the grid and show the corresponding row in the record viewer.

For detailed information about each column, hover a Name field to show database data type, nullability, and more.

Check the General / Grid / Grid Viewers category in Tool Properties for settings related to the record viewer.

Editing in the Record viewer

If editing is supported, edits made in the grid are directly propagated to the record viewer and vice versa. Edits are indicated with the same colors as in the grid.

Preview Changes

You may preview the SQL statements that will be executed when choosing to Save the edits via the Edit Table Data → SQL Preview right-click menu choice.

View and edit Binary/BLOB and CLOB Data

Due to the nature of binary/BLOB and CLOB data, values of these types can only be fully modified and viewed in the value viewer (there is partial support in the Record viewer to view image data and to load from a file).

In the grid, Binary/BLOB and CLOB data is by default presented by an icon and the size of the value. You can select another presentation format in the Tools Properties dialog, in the General/Data Formats category under the General tab. Selecting By Value or Image Preview results in performance penalties and the memory consumption increases dramatically. The Image Preview for BLOB presents a downscaled image directly in the grid if the BLOB is in one of the supported image formats. You can set the image preview size.

In the same Tool Properties category, you can also specify how to handle Copy/Paste and Drag and Drop when pasting binary data in a target component that doesn't support binary data.

Editing binary data can be done by importing from a file or via the text editor in the value viewer. You can also copy the file in the operating system's file browser and paste it into a BLOB/CLOB cell.

Binary data in DbVisualizer is the generic term for several common binary database types:

• LONGVARBINARY
• BINARY
• VARBINARY
• BLOB