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 editor or form editor contains new line, carriage return or tab characters, these are visually represented in the grid, but 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 Editor/Viewer is available in the right-click menu (Edit Cell in Window or View Cell in Window if the data is read-only) and on the toolbar 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 editor that allows you to view, edit and/or format the content, and also to save or load the data to/from a file.

The Text Editor can be used for viewing and editing textual data, including JSON and XML. It does not offer any syntax coloring or validation; it is a plain text editor.

The Hex Viewer shows the content in hexadecimal format.

The JSON Formatter can transform the JSON structure to a human readable multi-line format (sometimes called "pretty printed") or to a compact single line format where insignificant whitespace is trimmed. The formatting will fail if the text does not comply with JSON syntax.

The XML Viewer is shown if the cell value is recognized as well-formed XML, or if you manually select to open the XML Viewer (which, for obvious reasons, may fail). The viewer presents data in a structured way but does not allow editing; you need to switch to the Text Editor to edit.

The Image Viewer displays full size images for binary data that conforms to a supported image format:

• GIF (Graphics Interchange Format )
• JPG/JPEG (Joint Photographic Experts Group)
• PNG (Portable Networks Graphics)
• TIFF (Tagged Image File Format)
• BMP (Bitmap Image File)
• PDF (Portable Document Format)

Opening the Cell Viewer for binary data will automatically render the content if a supported image type or if a PDF document.

Using the Form Editor/Viewer

The Row Viewer is available in the right-click menu (Browse/Edit Row in Window) for all grids in DbVisualizer. It is used to either browse or edit information and to present binary data in viewers.

The Row 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 and transposed overview of all the data.

The form editor transpose or "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 Window 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 navigation buttons are used to navigate the source grid and show the data for the first, previous, next or last row in the form.

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 General / 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 viewer/editor, 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.

View and edit 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 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 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