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 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
A block of cells is copied		The block is pasted into the selected region
A block of cells is copied		The block is pasted into a non equal number of target cells

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 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:

You may choose to edit the value in the Cell Editor, which we recommend, as the control characters will then be preserved. Alternatively, you can edit the value in the grid anyway but you then risk loosing the control characters.

Using the 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 also allow you to save the 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.

Using the 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 button in the grid editor toolbar, just as for data edited directly in the grid.

Open the form editor via the Edit Row in Form right-click menu choice, via the corresponding button in the toolbar or by double-clicking the row number header.

The same row looks like this in the row form window:

The Key field 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 by the Image Thumbnail Size setting in the Tool Properties dialog, in the Form Viewer category under the General tab. To see the original size of an image, open the cell in the cell viewer either by selecting Edit in Cell Window in the grid right-click menu, the toolbar button or by double-clicking on the image.

If you want numbers to be right-aligned in the Form Viewer, enable Right Aligned Numbers in the Tool Properties dialog, in the Form Viewer category under the General tab.

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.

Editing Binary/BLOB and CLOB Data

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

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 results in performance penalties and the memory consumption increases dramatically.

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 Cell Editor. 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

The Image Viewer supports displaying full size images for the following formats:

• GIF
• JPG
• PNG
• TIFF
• BMP
• PDF