A table's data can be viewed in in various ways in the Data tab in its Object View tab.
Opening the Data tab
To open the Data tab for a table:
- Locate the table in the Databases tab tree,
- Double-click the table node to open its Object View tab,
- Open the Data sub 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.
In the same Tool Properties category, you can also disable Show Grid Row Header, i.e. the row number shown to the left of the data rows, for read-only grids such as the Data tab in the DbVisualizer Free edition and result sets from joined tables.
The column headers corresponds to the column names by default, but you can specify in the Tool Properties dialog, in the Grid category under the General tab, that you like to use the column alias instead. This is mostly useful for grids representing SQL Commander result sets, but may also be useful in the Data tab grid for some databases.
The Data tab contains a number of features for locating and focusing on just the data of interest as described in the following sections.
You can sort the data grid based on the values in one or more columns:
- Click on a column header to sort the grid in ascending order on the values in that column, indicated by an up-arrow in the column header.
- Click the same column header again to sort in descending order, indicated by a down-arrow in the column header.
- Click a third time to show the data in the order it was received from the database. This removes the sort indicator.
To sort on more than one column, Ctrl-click (keep the Ctrl key pressed when clicking) on additional columns. The grid is then sorted on the values in the first column you clicked on (indicated with a 1 next to the arrow), and then all rows with the same value in the first column are sorted on the values in the second sort column (indicated with a 2 next to the arrow), and so on.
You can use the filter capability in the Data tab to limit the number of rows shown in the grid, using the same syntax as for an SQL WHERE clause. The Filter menu button in the grid toolbar contains all operations related to using a filter.
The top entries in the menu are previously used filters for the table, if any. The checkbox is selected for the filter that is currently in use. The filters are saved between DbVisualizer sessions, and you can toggle between them by selecting them from the menu. The maximum number of filters to save is specified in the Tool Properties dialog, in the Table Data category under the General tab.
You use the Use No Filter choice to disable all filters for the table, and the Clear Filter List to permanently remove all filters for the table.
To create a new filter:
- Select Open Filter Editor to launch the Filter & Sort Configuration dialog,
- Select a column name, an operation and the value for the condition using the controls in the Filter area,
- Add the condition to the filter by clicking the AND or OR button, and create additional conditions in the same way if needed,
- Click the Use Filter button to apply the filter, save it and close the dialog, or close the dialog without applying and saving the filter by clicking the Close button.
You can use Ctrl-Enter while editing the value field to reload the grid with that single condition applied, or in the editor to reload the grid based on all filter conditions created so far.
The Sort area is similar to the Filter area. You can select column names and sort order from the two lists, and click the Add button to add the sort criteria for the single column to the complete criteria.
If you often need to tweak the filter conditions and want a more compact user interface, you can use the inline filter view. Use the Show/Hide Filter Area choice in the Filter menu to toggle the visibility of the inline filter.
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
The Quick Filter acts on the data that is already loaded in the grid, as opposed of a Where Filter which is used to limit the number of rows fetched from the database. With a Quick Filter, you can easily list only those rows in the grid that match the entered search string.
Use the Quick Filter pull-down menu (click on the down arrow next to the magnifying glass) to choose if the filter should match cells in all columns or just one selected column, case or case insensitive matching, and where in the cell the value must match.
For the Use wild cards option the following characters have special meaning:
? - The question mark indicates there is zero or one of the preceding element. For example, colou?r matches both "color" and "colour".
* - The asterisk indicates there are zero or more of the preceding element. For example, ab*c matches "ac", "abc", "abbc", "abbbc", and so on.
+ - The plus sign indicates that there is one or more of the preceding element. For example, ab+c matches "abc", "abbc", "abbbc", and so on, but not "ac".
Max Rows/Max Chars
DbVisualizer limits the number of rows shown in the Data tab to 1000 rows, by default. This is done to conserve memory. If this limit prevents you from seeing the data of interest, you should first consider: If you really need to look at more than 1000 rows, you can change the value in the Max Rows field in the grid status bar. Use a value of 0 or -1 to get all rows, or a specific number (e.g. 5000) to set a new limit. Character data columns may contain very large values that use up lots of memory. If you are only interested in seeing a few characters, you can set the Max Chars field in the grid status bar to the number of characters you want to see. You can define how to deal with columns that have more characters than the specified maximum in the Tool Properties dialog, in the Grid category under the General tab. You have two choices: Truncate Values or Truncate Values Visually. This affects any subsequent edits and SQL operations that use the value since it's truncated. This setting is only useful to save memory when viewing very large text columns. When the grid data is limited due to either the Max Rows or Max Chars value, you get an indication about this in the rows/columns field in the grid status bar and in the corresponding limit field. Along with the highlighted field, a warning pops up close to the field. You can disable this behavior in the Tool Properties dialog, in the Grid category under the General tab.
If you really need to look at more than 1000 rows, you can change the value in the Max Rows field in the grid status bar. Use a value of 0 or -1 to get all rows, or a specific number (e.g. 5000) to set a new limit.
Character data columns may contain very large values that use up lots of memory. If you are only interested in seeing a few characters, you can set the Max Chars field in the grid status bar to the number of characters you want to see.
You can define how to deal with columns that have more characters than the specified maximum in the Tool Properties dialog, in the Grid category under the General tab. You have two choices: Truncate Values or Truncate Values Visually.
This affects any subsequent edits and SQL operations that use the value since it's truncated. This setting is only useful to save memory when viewing very large text columns.
When the grid data is limited due to either the Max Rows or Max Chars value, you get an indication about this in the rows/columns field in the grid status bar and in the corresponding limit field.
Along with the highlighted field, a warning pops up close to the field. You can disable this behavior in the Tool Properties dialog, in the Grid category under the General tab.
Max Rows at First Display
By default, opening the Data tab for a table loads all rows, unless there is a Max Rows limit. If you have very large tables and don't want to risk memory issues if you accidentally open the Data tab and have no Max Rows limit, you can specify a Max Rows at First Display limit. You do this in the Tool Properties dialog, in the Table Data category under the General tab.
The default is -1, which means no limit. If you set it to a positive number, only the specified number of rows are loaded when the Data tab is first opened for a table. To load more rows, click the Reload button in the Data tab toolbar.
Column Header Tooltips
The column header tooltip shows data type information about the column. To see the tooltip, let the mouse hover over the column header. The tooltip pops up in about a second.
Highlight Primary Key Columns
By default, a Primary Key column is shown with an icon in the column header. You can disable this in the Tool Properties dialog, in the Table Data category under the General tab.
Show Only Some Columns
The Grid Column Chooser dialog controls which columns you want to appear in a grid. Open the dialog by clicking the button above the vertical scrollbar in the grid.
The Grid Column Chooser dialog shows all columns that are available in the grid. The checkmark in front of a column name indicates that the column is visible in the grid, while an unchecked box indicates that it is excluded from the grid. Click the checkmark to change the visibility of a column. You can change the visibility for all columns at once using the two visibility buttons in the dialog.
The order of the columns can also be adjusted in this dialog. Just select a row and use the Up and Down buttons to move it up (left in grid) or down (right in grid).
If you want to revert your changes, you can click on the Default Layout button to reset the grid, i.e., making all column visible and put them in their default locations.
Modifications of column visibility, size and order are saved between invocations of DbVisualizer for all grids in the various Object View tabs except for the Data tab.
If you modify the column visibility in the Data tab, the changes persists throughout the session. For instance, if you remove the Name column in the Data tab for the table EMPLOYEE, the Name column remains excluded when you reload the table or come back to the Data tab for that table later in the same session. You must manually make it visible again to bring it back. The changes are, however, reset when you restart the application.
Auto Resize Columns
The column header right-click menu contains a number of options for automatic resizing of column widths.
Right-Click Menu Operations
The right-click menu for the grid contains a lot of operations for working with the data without changing it. In addition to the common select, copy, and print operations, some operations that may require a bit of an explanation are:
|Copy Selection||Copy all selected cells onto the system clipboard|
|Copy Selection with Column Header||Copy all selected cells including column header onto the system clipboard.|
|Copy Selection as Formatted Text||Copy all selected cells including column header in fixed width columns onto the system clipboard.|
|Copy Selection as Comma List||Copy all selected cells, with cells from the same column formatted as comma separated values|
|Copy Selection as IN Clause||Copy all selected cells, with cells from the same column as formatted as an IN clause, e.g. |
|Save Selected Cell||Save the value of the selected cell to a file, selected with a file chooser dialog|
|Compare||Compare the data in this grid to the data in other open grids.|
|Compare Selected Cells||Compare the data in the two cells as text|
|Browse Row in Window||Display all data for the selected row in a separate window.|
Note: for a read/write grid, this entry is named Edit Row in Window.
|Browse Cell in Window||Display the cell value in a separate window. This is especially useful for BLOB/CLOB data.|
Note: for a read/write grid, this entry is named Edit Cell in Window.
|Show in Navigator||Open the Navigator tab with the current selections and sorting.|
|Describe Data||Show detailed information about the columns in the grid.|
|Aggregation Data for Selection||Displays aggregation data for the current selection. Read more in Aggregation Data for Selection below.|
|Generate Filter & Sort||The operations in this submenu helps you create common Where Filters.|
|Create Row Count Data Monitor||Creates a monitor for tracking the row count in the table over time.|
|Create Row Count DIff Data Monitor||Creates a monitor for tracking the number of added or removed rows in the table over time.|
There are also a set of operations for generating SQL statements based on the current selection. Choosing any of these creates the appropriate SQL and then switches the view to a new SQL Commander tab. You must use these operations to edit table data in the DbVisualizer Free edition. With the DbVisualizer Pro edition, you can instead use inline and form based editing.
|Script: SELECT ALL|
|Script: SELECT ALL WHERE|
|Script: SELECT ALL WITH FILTER|
|Script: INSERT INTO TABLE|
|Script: INSERT COPY INTO TABLE|
|Script: UPDATE WHERE|
|Script: DELETE WHERE|
You can generate SQL with either static values as they appear in the grid, or with DbVisualizer variables. A variable is essentially a placeholder for a value in an SQL statement. When the statement is executed, DbVisualizer locates all variables and presents them in a dialog where you can enter or modify values for the variables. DbVisualizer replaces the variable placeholders with the new values before executing the statement.
Variables are used in the generated SQL statements by default. You can disable the Include Variables in SQL setting in the Tool Properties dialog, in the Table Data category under the General tab, to use literal values are instead.
Here is an example of the SQL generated for Script: SELECT ALL WHERE with the Include Variables in SQL setting enabled, assuming the table is named HR.COUNTRIES and has a column named COUNTRY_NAME with the value 'Brazil' on the selected row:
And here is the same example with the Include Variables in SQL setting disabled:
A monitor in DbVisualizer is an SQL query executed at a specified frequency so you can track changes in data over time. The result can be viewed either as a grid or a graph. The right-click menu for the grid in the Data tab contains operations for creating two common types of monitors for the table: a Row Count Data monitor or a Row Count Diff Data monitor. The first tracks the number of rows in the table over time and the second tracks the number of added or removed rows over time. Please read more about monitors in Monitoring Data Changes.
Aggregation Data for Selection
Only in DbVisualizer Pro
This feature is only available in the DbVisualizer Pro edition.
The Aggregation Data for Selection feature presents aggregation data organized per data type on the current selection in a grid. It provides information about cells holding numbers, text, date/time information and more. The following is an example of what it shows:
With Auto Update checked, the data is updated automatically when you change the selection in the grid. For very large selections, you may prefer to disable this feature and instead click Update when you want to refresh the data. Click a link (blue underlined text) in the aggregation table to locate and highlight the actual value in the source data grid. The Handle Number Values in Text Types as Numbers setting simply treats all valid numbers in text data types as numbers and include them in the Number Count summary.