Viewing Table Data

A table's data can be viewed in various ways in the Data tab in its Object View tab.

Check Viewing BSON Document Data for information about the document data viewer in DbVisualizer.

Opening the Data tab

To open the Data tab for a table:

1. Locate the table in the Databases tab,
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.

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

Sorting

You can sort the data grid based on the values in one or more columns:

1. 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.
2. Click the same column header again to sort in descending order, indicated by a down-arrow in the column header.
3. 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.

Formatting

Note that when a cell is selected, the format pattern used to display the cell value is visible at bottom of the grid. By clicking the blue link, the Data Formats section of Tool Properties will be opened.

Where Filter

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:

1. Select Open Filter Editor to launch the Filter & Sort Configuration dialog,
2. Select a column name, an operation and the value for the condition using the controls in the Filter area,
3. Add the condition to the filter by clicking the AND or OR button, and create additional conditions in the same way if needed,
4. 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.

Column Filter

The Column Filter acts on the data that is already loaded in the grid, as opposed to a Where Filter which is used to limit the number of rows fetched from the database. With a Column Filter, you can easily list only those rows having a column value matching the Column Filter. By customizing the filter it is possible to add more complex filters such as only listing rows where the column value does not contain a certain value or substring.

A Column Filter is added by clicking the right part of the column header. A column having a Column Filter is indicated by the presence of a filter icon in the column header.

It is possible to to add Column Filters to multiple columns. For cases where you don't care which column has a specific value we recommend the Quick Filter

Quick Filter

The Quick Filter acts on the data that is already loaded in the grid, as opposed to 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 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 and Max Chars settings

Max Rows setting

DbVisualizer by default limits the number of rows shown in the Data tab to 20,000 rows to conserve memory. The following is displayed when this limit is reached.

If the max rows limit prevents you from seeing the data of interest, you should first consider:

1. Using a Where Filter to only retrieve the rows of interest,
2. Exporting the table to a file.

If you really need to look at more than 20,000 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.

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, under the General / Table Data category.

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, up to the Max Rows setting in the Data tab, click the Reload button in the Data tab toolbar.

Max Chars setting

Text values presented in the Data tab grid may be very long, potentially consuming lots of memory and affecting performance. By default, DbVisualizer visually truncates text values at 1,√000 characters, and when it happens, there is an indicator below the grid. The Max Chars setting can be modified in the Tool Properties / Data Formats category or by clicking on any of the Max Chars links in the screenshot below.

Along with the highlighted field, a warning pops up close to the field. You can disable this behavior in the Tool Properties dialog under the General / Grid category.

Pagination

For some databases, DbVisualizer supports navigating the data set page by page. When this is possible, a paging section is added to the toolbar allowing to navigate between the pages. The page size is determined by the Max Rows setting.

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, under the General / Table Data category.

Auto Resize Columns and Wrap Column Lines

The column header right-click menu contains a number of options for automatic resizing of column widths. The same menu allows you to choose to wrap long text lines in columns. Line wrap is not available for CLOB or Binary data types.

If a column contains line endings and wrapping is not turned on for that column, line endings will be represented by a symbol within the cell.

To show the full text of a wrapped text value, double-click on it to open in the Value viewer.

Show Only Some Columns

The Grid Column Chooser dialog controls which columns you want to appear in a grid. Open the dialog by the right-click menu on the column header or the button above the vertical scrollbar in the grid. See the screenshot below.

Grid Column Chooser with the default layout. The icon in the top right corner of the grid indicates the standard layout.
Grid Column Chooser with a custom layout. The icon in the top right corner of the grid indicates that the layout is changed.

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 one or several rows and use the Up and Down buttons to move the rows up (left in the grid) or down (right in the grid). You can also use Drag&Drop to move the rows.

If you want to revert your changes, you can click on the Default Layout button to reset the grid, i.e., making all columns visible and putting them in their default locations.

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 described below. Examples are based on the following simple grid that contains 9 cells on 3 rows with 3 columns, where the first two rows are selected:

1 A c3
2 B c3

col1 col2 col3
1 A c3
2 B c3

col1 col2 col3 
---- ---- ---- 
1    A    c3 
2    B    c3

1, 2
A, B
c3, c3

(col1 IN (1, 2))
and (col2 IN ('A', 'B'))
and (col3 IN ('c3'))

(col1 = 1 and col2 = 'A' and col3 = 'c3')
or (col1 = 2 and col2 = 'B' and col3 = 'c3')

(col1 IN (1, 2))
and (col2 IN ('A', 'B'))
and (col3 = 'c3')

Script to SQL Commander

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.

SELECT * FROM COUNTRY;

select * FROM SAKILA.COUNTRY
where COUNTRY = 'Brazil'

select * 
from SAKILA.COUNTRY
where COUNTRY_ID = 15 // If this is the filter, see above

insert into SAKILA.COUNTRY
(COUNTRY_ID, COUNTRY)
values (15, 'Brazil')

insert into SAKILA.COUNTRY
(COUNTRY_ID, COUNTRY)
values (15, 'Brazil')

update SAKILA.COUNTRY
set COUNTRY_ID = 15, 
    COUNTRY = 'Brazil'
where COUNTRY = 'Brazil'

delete from SAKILA.COUNTRY
where COUNTRY = 'Brazil'

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 SAKILA.COUNTRY and has a column named COUNTRY with the value 'Brazil' on the selected row:

SELECT * FROM COUNTRY WHERE COUNTRY = ${COUNTRY (where)||Brazil||String||where ds=50 dt=VARCHAR}$;

And here is the same example with the Include Variables in SQL setting disabled:

SELECT * FROM COUNTRY WHERE COUNTRY = 'Brazil';

Creating Monitors

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.

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.

Aggregates viewer

The Aggregates viewer presents aggregation data organized per data type for 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:

The aggregate viewer runs in the background whenever it is visible on screen, and the selection changes in the source grid. The buttons in the toolbar:

- When selected, numbers in text data types will be calculated as numbers
- When selected, the compact aggregation status entry will be displayed below the source grid.

Clicking a label with an underline highlights the corresponding cell in the source grid.

Aggregation status bar

The aggregation status bar can show up to three metrics for the current selection. It is updated even when the aggregation viewer is not displayed. The default is to show the summary of numbers in the selected cells.

Click the status bar entry to choose up to three metrics and to show their current values:

The Show all Aggregates item will open the aggregate viewer, and there the aggregation status bar can be turned off.