Database Objects Explorer

• Introduction
• Create a Database Connection
  • Database Connection object
    • Alias
    • Default database and schema
    • Remove and copy database connection objects
    • Database Connection detailed information
    • Search
  • Organizing Database Connections in Folders
  • Connections overview
• Database Objects Tree
  • Standard Actions
  • Object Actions
    • Common Object Actions
      • Create Table
      • Create Index
      • Import Table Data
      • Export Table
      • Script Object to SQL Editor
      • Script Object to New SQL Editor
  • Objects Tree Filtering
  • Show Table Row Count
  • Object Tree Icons
• Database Profiles
  • Database Specific Support
  • Generic profile
    • Catalog/Database object
    • Schema object
    • Table object
    • Procedure object
• Object Views
  • Grid
  • Form
  • Source
  • Table Row Count
  • Table Data
    • Right-click menu
    • Where Filter
    • Quick Filter
    • Monitor row count
    • Editing
  • DDL Viewer
  • References
  • Navigator
  • Procedure Editor

Introduction

The Database Objects Tree is used to explore databases and browse details about objects. Which object types may be explored and which object actions exist are database dependent.

Figure: Database Objects tab

The Databases tab to the left is the place to setup new database connections and establish connections. Once connected, expand the database connection object and explore the objects available. The Object View area to the right displays detailed information about the currently selected object in the tree.

The Filter setup pane below the tree is used to control which objects are displayed in the tree. It comes in handy when you have many schemas or tables in your database and want to limit the number of visible objects.

For some object types, there are actions (small dialogs for performing a task) for common operations, such as creating, altering, and dropping database object. Which actions are available depends on the database you are connected to and the database profile used for the connection. More about this in sections below.

Create a Database Connection

There are a few objects that always appear in the tree independent of the edition of DbVisualizer and the database profile in use. The most important object is the Database Connection, which is used to setup and establish a database connection. The other two objects are Folder and Connections Overview. The following sections describe these objects in more detail.

Database Connection object

The Database Connection object is the root object for a connection. Before exploring or accessing a database, you need to establish the connection. Create a new database connection using the Database->Add Database Connection main menu choice and the following will appear.

Figure: Add database connection

We recommend that you always use the connection wizard when you create a new database connection, as it hides the complexity of loading drivers and the syntax of database URLs (detailed information on how to establish a connection is provided in the Load JDBC Driver and Get Connected document).

Alias

The name of the database connection object as it appears in the tree is by default "Database Connection". The Connection Alias can be used to provide a name that is more descriptive. Enter the new name in the Alias field in the Connection sub tab.

Default database and schema

The (Default) indicator after the name of a database or schema in the tree indicates that it is the default database or schema. The default is determined when you connect to the database. 

Figure: The (default) indicator for database and schema objects

Remove and copy database connection objects

To remove a database connection, select the Database->Remove Database Connection operation in the main menu. You can copy a database connection with Database->Duplicate Database Connection.

Database Connection detailed information

The following section describes the tabs in the Objects View for a database connection briefly.

Search

The Search tab is used to search among the objects in the tree by object name. Note that if you have tree filters or any other property that limits the content of the tree enabled, the search is performed only for those objects that match the filters. The types of objects that are searchable depends on the database you are connected to. For instance, columns are included in the tree for some databases but not for others.

Figure: The Search tab

Search by specifying the name of the object, or name pattern, and press the Search button.  You can use asterisk (*) as a wildcard in a pattern, or you can use a regular expression pattern if you enable it by checking the Regular Expression checkbox. You can also specify where in the tree to start the search, and whether to do a case sensitive search.

You can interrupt a search operation with the Stop button in the grid toolbar. Use the Show Object Path toolbar toggle button to include or exclude a column for the complete path for each found object in the grid. This path is the same as if navigating to each object manually in the objects tree. Other grid toolbar buttons let you export and print the search result grid.

The search may take some time to perform the first time.

Organizing Database Connections in Folders

If you work with many database connections, you can use folder objects to organize and group them in the tree. Folder objects can have child folder objects in an unlimited hierarchy.  Use the Database->Create Folder and Database->Remove Folder menu choices to create and remove folder objects. You can use the Database->Move Up/Down main menu choices to move the folders (and database connections) in the tree, or you can just drag and drop to the nodes to a new location.

Figure: The database objects tree and the folder object type

Connections overview

The Connections object is the root object in the tree and acts as a holder for all database connections and folders. When selected, it displays an overview of all database connections in the Object Details view. Here you can see the basic settings and states for your database connections. For more information, see the Load JDBC Driver and Get Connected chapter.

Figure: Connections object

Database Objects Tree

Standard Actions

The Database Objects toolbar buttons are used to do tree related operations. These are individually enabled or disabled based on the currently selected object.

Figure: Objects tree toolbar

Description of the buttons from the left:


The right-click menu for an object and the Database main menu lists object specific actions. The following actions are always available for all objects:

Figure: Standard right click menu actions for all objects

Object Actions

An object in the objects tree may have object specific actions attached to it. These actions are accessible via any of:

• Right-click menu in the objects tree
• Via the Database->Selected Object main menu
• Via the Actions menu button in the object view

Here is an example of the actions menu launched via the Actions menu button:

Figure: Object actions menu

Common Object Actions

There are a few actions that appear for some object types in all database profiles. These are most often valid for plain table object types and offer related functionality. Read the following sections for more information.

Create Table

The Create Table action launches the Create Table feature. You use it to create a table, optionally with a primary key, foreign keys and other constraints. Read more about this feature in Create and Alter Table.

Create Index

The Create Index action launches the Create Index assistant dialog, where you can select columns to include in a new index for a table.

Import Table Data

Import Table Data launches a dialog where you can specify a CSV file to be imported into a table. Various configurations for how the source file is organized and data mapping are offered. Read more in Export and Import.

Export Table

Export Table launches a dialog for exporting the DDL and/or data for the table. Read more in Export and Import.

Script Object to SQL Editor

Use this action to create pre-defined SQL statements based on the source table and its columns. The created statement is copied to the current SQL editor in the SQL Commander. Here are a couple of examples:

Script Object to SQL Editor -> Select

SELECT
   COUNTRY_ID,
   COUNTRY_NAME,
   REGION_ID
FROM
   HR.COUNTRIES

Script Object to SQL Editor -> Insert

INSERT
INTO
   HR.COUNTRIES
(
   COUNTRY_ID,
   COUNTRY_NAME,
   REGION_ID
 )
 VALUES
 (
    '',
    '',
    0
 )

For databases with DbVisualizer database specific profiles, the Script Object to SQL Editor action menu also contains an entry for generating the DDL for Table and View objects. 

Script Object to New SQL Editor

This is the same as Script Object to SQL Editor, except that the SQL is copied to a new SQL editor instead of to the current editor.

Objects Tree Filtering

The Filtering setup is activated via the Database->Show/Hide Tree Filter menu choice and the filter pane appear below the objects tree. Filtering is useful to limit the number of objects that will appear in the tree.

Tree filters are managed per database connection object. What can be filtered is defined per database profile. The generic database profile supports filtering of database (catalog), schema, table and procedure names.

Figure: Examples of tree filter settings

An active filter for a database connection is represented by the funnel icon just before the database connection name. The active state for a filter is defined using the Activate Filters checkbox in the Object Filter pane. A filter can only be activated if there are any filters defined. Up to 15 filters can be defined per object type.

Show Table Row Count

The Database->Show/Hide Table Row Count menu choice decides whether the number of rows for table objects will be listed after the name of the table in the tree.

Enabling this property results in a performance degradation.

Object Tree Icons

Every known object type is associated with an icon that is displayed in the objects tree, object view tab and the actions window. A few operations may add an overlay icon in the objects tree to indicate certain conditions. These are the overlay icons that may appear on top of the object icon:

	New symbol. This indicates that the actual object is newly created since the objects tree was last loaded
	Warning symbol indicating that the condition(s) for the actual object is in a state that may require attention
	Error symbol indicating that the object is in an erroneous or incomplete state

Database Profiles

A Database Profile is the foundation for database specific support in DbVisualizer. A database profile is, somewhat simplified, a definition of the kind of information that is presented in the database objects tree and in the various object views for a specific database engine. In addition, the profile defines the actions for the object types defined in the profile. DbVisualizer loads the matching database profile when you connect to a database. If no matching profile is found, or if you are running DbVisualizer Free, DbVisualizer uses a Generic profile with just the general database information and actions included.

Database Specific Support

DbVisualizer Personal currently offer database specific support (database profiles) for the following databases (click links for details):

• DB2 LUW
• DB2 z/OS
  
• HP Neoview
• Informix
• JavaDB/Derby
• Mimer
• MySQL
  
• Oracle
  
• PostgreSQL
  
• SQL Server
  
• Sybase ASE
  

The specialized database profiles define different object types, so the database objects tree may look different depending on which database you are connected to. The structure and organization of a database profile is also something that may impact the layout of the tree, even though the provided ones are similar in their structure. There are two root nodes in the majority of the profiles:

• User objects
• DBA objects

User objects are, for example, tables, views, triggers, and functions, while DBA objects most often are objects that require administration privileges in the database in order to access them. DbVisualizer puts all DBA objects under the DBA Views tree node. If you connect to a database using an account with insufficient privileges to access a DBA object, you may see error messages if you try to select nodes under the DBA Views node. The following is an example of the DBA sub tree for Oracle.

Figure: The DBA Views tree object

Generic profile

DbVisualizer supports a wide range of databases. The nature of the databases and what they support differ from vendor to vendor, so the appearance and structure of the tree below the Database Connection objects for different databases differ as well. The generic database profile (the only profile available in DbVisualizer Free) displays objects based on what JDBC offers in terms of database information (aka metadata information). DbVisualizer asks the JDBC driver for all schemas, databases, tables and procedures, and then builds the tree based on what the driver returns.

The advantage of using JDBC to get database metadata is that it is a standard way to access the information, independent of the database engine type; the JDBC driver layer hides the proprietary details about where and how the information is really stored. The drawback with using JDBC is that JDBC doesn't offer access to all metadata a database may hold. While the information presented by the generic profile, with its reliance on JDBC, is sufficient for many tasks, a database specific profile offers far more details as well as more features. If you use DbVisualizer Free with one of the databases supported by database specific profiles, you may want to upgrade to the DbVisualizer Personal edition.

The generic database profile when used for an Oracle connection look as follows:

Figure: The generic database profile when applied to an Oracle database connection

The appearance of the generic database profile may include schema objects and/or catalog objects depending on whether the database supports these objects. The Procedures object always appear in the tree, regardless of if the database connection supports procedures or not.

The following sections describe the objects provided by the generic profile.

Catalog/Database object

Catalog is the term used in JDBC and some database engines for a logical grouping of database objects. Other database engines, e.g., Sybase, PostgreSQL, SQL Server and MySQL use the term Database for, more or less, the same purpose. Both terms are used in interchangeably in DbVisualizer.

The Object View for a Catalog object in the generic profile is a pane with two tabs, Tables and References. The Tables tab lists all the tables that are located in the catalog while References shows the exact same list of tables but instead as a referential integrity graph.

Figure: The view for Catalog objects

The child objects shown for a catalog object depend on the capabilities of the JDBC driver. Typically, a child object represents a type of table that the driver use to categorize the tables in a catalog, e.g., regular tables or system tables. For instance, the example in the figure above shows a MySQL database connection with catalog objects as its child object. The catalog child objects are TABLE and LOCAL TEMPORARY, because these are the table types that the MySQL JDBC driver supports (these table types are the same as those listed in the Table Types tab when selecting a database connection object). For other databases, you may see child objects representing other table types.

Schema object

The generic profile Schema object tree and view are organized in the same way as for the Catalog objects. There is in fact no difference except that the schema objects are in another level in the tree and is represented by a different icon.

The following screenshot shows the information for the selected schema with the References tab selected.

Figure: The view for Schema objects

Table object

The Table object is probably the most frequently accessed object in the tree, since its Object View shows not only a lot of information about the table but also the data the table holds. This is also the place where you can edit the table data (only available with the DbVisualizer Personal edition).

Figure: The view for Table objects

The Object View for a table object contains the following tabs:

Procedure object

The Procedure object shows the name of the procedure or function in the tree, and the Object View lists the parameters that are used when calling it.

Figure: The procedure object

The Object View shows a list of column names for the selected procedure.

Object Views

The Object View tab shows detailed information about the selected tree object. The Object View may contain several sub tabs, depending on the current database profile and the type of the object selected in the tree. There may also be several representations of the same information, providing different views of the information. The following sections describe the different views, or visual presentation forms, provided by DbVisualizer.

Grid

The Grid view is the most common one as it displays the data in a standard grid style.

Figure: The Grid view

Form

The Form view extends the Grid view by adding a form below the grid. Click on a row in the grid and the information is displayed in the form.

Figure: The Form view

If there is only one row of data, only the form is displayed.

Source

The Source view is typically used to show the source for functions, procedures, triggers, etc. It is based on a read only editor with SQL syntax coloring. The sub toolbar buttons from the left:

• Reload the data from the database
• Stop loading the data from the database
• Export the data to file
• Print the data
• Copy the data to SQL Commander
• Format the SQL

Figure: The Source view

Table Row Count

The row count view is really simple: it only shows the number of rows in the selected object.

Figure: The Row Count view

Table Data

You use the Data tab to browse the data in the table and to do various data related operations. This view is based on the generic grid, but it adds a few more visual components to limit the max number of rows, the width of text columns and the collection of data tab specific operations in the right-click menu. In addition, you can also use a filter (a SQL WHERE clause) to limit the data to the rows that match the filter. The data tab is the place to do edits in DbVisualizer Personal.

Figure: The Data tab for Table objects

Right-click menu

The Data tab grid right-click menu contains some operations in addition to those in the standard grid right-click menu. The additional operations are primarily for creating SQL statements based on the current selection. Choosing any of these creates the appropriate SQL and then switch the view to the SQL Commander tab. You must use these operations to edit table data in the DbVisualizer Free edition. With the DbVisualizer Personal edition, you can instead use inline and form based editing. (Information about the standard right click menu operations are available in the Getting Started and General Overview document).

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 can be used in any SQL statement and DbVisualizer relies on them heavily. (Read more about variables in the SQL Commander document).

Whether to use variables in the SQL statements generated by the right-click menu SQL operations depends on the Table Data->Include Variables in SQL setting in Tool Properties, under the General tab. By default, variables are being used in the statement. If you disable the property, literal values are instead used in the generated statement.

Here is an example with the Include Variables in SQL setting enabled and then disabled. The SQL is generated when the Script: SELECT ALL WHERE operation is selected based on the selection in the previous figure.

Include Variables in SQL is enabled:

select *
from HR.COUNTRIES
where COUNTRY_NAME = ${COUNTRY_NAME (where)||Brazil||String||where nullable ds=40 dt=VARCHAR }$

Include Variables in SQL is disabled:

select *
from HR.COUNTRIES
where COUNTRY_NAME = 'Brazil'

The following lists the generated SQL for each of the operations based on the selection of COUNTRY_NAME = Brazil, with variables disabled.

Where Filter

The filter capability in the Data tab lets you limit the number of rows 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.

Figure: Filter menu

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. 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 Configure Filter to launch the Filter Configuration dialog.

Figure: The Data tab Filter Configuration dialog

The Filter Configuration dialog contains one Filter area and a Sort area.

The Filter area is composed of two parts. The upper one is used to define a condition for a single column. You can use the two lists to select the column name and an operator, and enter the value of the column in the text field. You can use Ctrl-Enter while editing the value to force a reload of the grid based on that single filter. The lower part displays the complete filter and the buttons are used to control whether the newly entered filter will be AND'ed or OR'ed with the complete filter. The buttons change appearance based on whether there is any filter or not. While in the complete filter you can use Ctrl-Enter to force a reload based on the complete filter.

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.

Click the Use Filter button to apply the filter and save it, and close the dialog by clicking the Close button.

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 Inline Filter choice in the Filter menu to toggle the visibility of the inline filter.

Figure: Data tab with the Inline filter enabled

The inline filter is displayed above the grid. You can edit the condition in the text field and use Ctrl-Enter or click the Use Filter button to apply the modified condition. Instead of manually typing column names in the field use the Ctrl-Space key binding to show a list of available columns.

Quick Filter

The quick filter acts on the data that is already 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.

The following figure shows data that matches the search string "d". Matching cells are highlighted.

Figure: Using the Quick Filter

Entering successive characters will narrow the result even further, as in the following figure.

Figure: Refining the filtering

The Quick Filter pull-down menu (click on the down arrow next to the magnifying glass) lets you 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.

Monitor row count

Read more about the Monitor Row Count and Monitor Row Count Difference in Monitor and Charts.

Editing

Read about data editing in Edit Table Data

DDL Viewer

The DDL Viewer tabs appear only for objects in databases that have specialized database profiles.

Figure: The DDL viewer for a table

References

The References tab for a Table object shows how the table references other tables (Imported Keys) and how other tables reference the selected table (Exported Keys), based on primary and foreign key declarations. Use the sub tabs at the bottom of the display to show either view. The following shows the references from the table.

Figure: The references graph showing imported keys for a table

Navigator

The Navigator tab (only available in the DbVizualizer Personal edition) provides an interactive way to navigate in data by following primary key and foreign key references.

Figure: The Navigator tab showing two navigation cases

The tab contains a graphic view showing navigation cases (paths through the data) at the top and a data grid showing the data for the navigation case selected in the graph. You navigate in the data by selecting the row in the grid that holds the key value you want to follow, e.g., a specific department in the example shown in the figure, and then select a primary or foreign key relationship from the Related Table list above the grid. This creates a new navigation case in the graph and updates the grid with the corresponding data.

How to use the navigator is described in more detail in the Data Navigation section.

Procedure Editor

You can use the procedure editor to browse, edit, compile and execute procedures, functions, packages, package bodies, triggers and other database objects that represent custom code that can be invoked in a database.

You can edit the source code in the editor and then click Save to save/compile the code. If errors are found, selecting an error message in the error list highlights the row containing the incorrect statement in the editor (in the cases when a row number is available, which is not true for all databases). To test the code, click Execute and a script for calling the procedure with the parameter values you provide is generated and executed in the SQL Commander.

More information can be found in the Procedure Editor document.

Figure: The procedure editor for functions, procedures, packages etc.

---