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.
To open the Data tab for a table:
To edit a column value:
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:
To duplicate a row:
To delete one or more rows:
If you change your mind, you easily can undo edits:
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.
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 cannot be pasted into a different number of target cells
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:
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.
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.
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)
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.
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.
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)
Opening the Cell Viewer for binary data will automatically render the content if a supported image type or if a PDF document.
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 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.
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.
The listed SQL statements may not be 100% identical to what is sent to the database, as the save process uses variable binding to pass values to the database.
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: